This page has not been updated since 2012 but many scripts have been added since. The tag cloud on the home page and the search control are two good ways to find pages of interest.
RootsMagic Queries
Many of these were developed around RootsMagic 4 and should also work on RootsMagic 5 and 6 with limitations. Likewise, many were developed around RM5 and should also work on RM6, with fewer limitations. The limitations are due to the changes in database definitions. RM5 added the ResearchItemTable and changed the storage of some Media Properties from the tag to the media item. RM6 added the URLtable in support of WebTags. Therefore a few queries may be erroneous in dealing with Media, and/or ignore the ResearchLinkTable or the URLtable but most would be unaffected because their purpose does not include these tables.
Some SQLite managers can import a query file and some can also export a query file. Others may require you to open the file with a text editor and copy/paste the query into the SQLite manager’s query editor.
Some smaller standalone queries are combined in one file; other larger files may be multiple queries that run in seqence. Depending on the SQLite manager, you can select which statements you want to run by highlighting them or by placing the cursor anywhere in the one statement to be run. Each SQL statement must end with a semi-colon to demark it from the others.
Optionally, a single query can be expanded to begin with the CREATE VIEW command. When successfully executed, the query is embedded in the database as a virtual table which remains with the database file until it is ‘dropped’ (deleted). Some SQLite managers allow you to save a query as a view from the user interface, putting a GUI in front of an internal CREATE VIEW.
The following table lists the queries from most recent to oldest. Queries are categorized in a menu-like structure on the page Query Menu.
| Description | Page | Query Name | Date Added |
|---|---|---|---|
| Creates a Citation WebTag for each citation having an URL beginning the Citation Comments field with “http://”. Not restricted to Ancestry.com downloads. | WebTags – from Ancestry.com and FTM | WebTags-MakeFromAncestryComments | 2012-12-10 |
| Creates Person WebTags from Citation WebTags to consolidate all WebTags pertaining to a person under the WebTags button on the Edit Person Screen. | WebTags – Consolidate | WebTags-Consolidate | 2012-12-10 |
| Deletes perfectly duplicate WebTags as will occur if WebTags-Consolidate is repeated or other possible cause. | WebTags – Consolidate | WebTags-DeleteDuplicates | 2012-12-10 |
| Sets the Media Type value for each media file to one of the four types: Image, File, Sound, Video, according to the file extension. | Media Type Reset | MediaTypeReset | 2012-12-04 |
| Inserts an Alternate Name of type “Married” for female “Wife” spouses in a “Family” where no Alternate Surname matches the male “Husband’s” and a (family) Marriage fact exists. | Names – Add Married | Name-Add_Married | 2012-11-26 |
| Sets Birth and Death years as seen in the sidebar index and various other reports and displays to match the corresponding facts. NOT for use on RM4; requires RM5+ Rebuild Indexes after completion. | Rebuild Indexes and Update Birth and Death Years | UpdateBirthDeathYears | 2012-11-14 |
| For encoding to SortDates, dates with optional RM modifiers and decoding same. | Dates – SortDate Algorithm | SortDateDecodeDev | 2012-11-03 |
| Procedures involving Excel, text editor and SQLite queries to bring Ancestry hyperlinks, citations and images into RootsMagic that are otherwise lost on a straight GEDCOM import. | Ancestry.com and RootsMagic 5 | MergeAncestryURLsToFTM Shrinking Verbose Master Sources from Ancestry.com | 2012-10-31 |
| Cleans out unused records from most tables, including phantom citations. Follow by RM Rebuild Indexes and Compact. | Delete Phantoms | DeletePhantoms | 2012-10-27 |
| Delete all people color-coded RED (or other color by editing the query). Follow with Delete Phantoms. Delete all people belonging to a Named Group (edit the query to the group ID). Follow with Delete Phantoms. | Delete Many | DeleteByColorCode DeleteByNamedGroup | 2012-10-27 |
| Lists citations for Primary Name as imported from Ancestry.com, FTM, et al but hidden in RM. Converts such invisible citations to visible Person citations. | Citations Invisible Revealed | Citations, Invisible – List Citations, Invisible – Convert to Personals | 2012-09-10 |
| Lists all addresses in AddressTable and reports how they are used. | Addresses – How Used | AddressesHowUsed | 2012-08-17 |
| An outline of procedures that have been used when People View throws error message “SQLite Error 1 – at most 64 tables in a join”. | People View Error – at most 64 tables in a Join | People View Error | 2012-03-19 |
| Lists the Include settings for all fact types. Sets all fact types to be included in GEDCOM and drag’n’drop. Stores a snapshot of all settings for fact types in an extra table in the database. Lists the include settings stored in the snapshot. Restores all settings for fact types from the snapshot. Deletes the extra table storing the snapshot from the database. | Fact Inclusion Controls | 2012-03-12 | |
| An outline of procedures that have been used with some success to recover a corrupted RootsMagic database. | Corrupt Database Recovery | Corrupt Database Recovery | 2012-03-10 |
| Finds SSN facts that have not been set to Private. | WebTrees Website – Pre-Processing Tools using SQLite in Visual Basic et al | RMSanity.exe | 2012-02-06 |
| Finds broken media links using an extension in Visual Basic. Finds media items in the Media Gallery that are not tagged to anything in the database. Finds media items that have been attached more than once to the Media Gallery | WebTrees Website – Pre-Processing Tools using SQLite in Visual Basic et al | RMSanity.exe | 2012-02-06 |
| Finds Place Details that have no owner Place. | WebTrees Website – Pre-Processing Tools using SQLite in Visual Basic et al | RMSanity.exe | 2012-02-06 |
| This series of queries and related Windows commands revises a RM4 database and makes copies of the multiply-linked files so that the update to RM5 will preserve all the metadata. | Media – Preserve Captions et al from RM4 to RM5 | RM4-MultiLinkedMediaDuplicationPreRM5 | 2012-01-31 |
| One query lists all Place Detail rows from PlaceTable for which there is no master Place. Another deletes such rows. | Place Details without a Place | Place Details without a Place | 2012-01-30 |
| The opposite direction is much easier. Needed for export to GEDCOM for third party apps that do not support Place Detail. | Convert Place + Place Detail to Place | Convert Place + Place Detail to Place | 2012-01-22 |
| Compare EXIF, IPTC, XMP metadata stored in image files to the Caption, Description and other metadata stored in a RootsMagic 5 database for image files linked to its Media Gallery and copy between the files and the database. | Media Metadata, Read, Write, Compare with Picasa | RM5comparePicasa.bat ImportPicasaDescriptions.sql RM5copyMetadataToImageFileExifToolArgList.sql RM5copyMetadataToImageFileExifToolArgList.bat RM5copyMetadataToRM.sql RM5copyMetadataToRM.bat | 2012-01-17 |
| Lists those image files that have not been opened by RM, hence nothing in the thumbnail field. With comments leading to other references. | Missing Media | 2012-01-15 | |
| Causes Narrative Descendant reports to output childless, spouseless children in their own generation, along with their married or parental siblings. | Reports, Narrative, Jerry Bryan Trick | DummyFamily-Add DummyFamily-Delete | 2012-01-15 |
| Every direct line ancestral RIN list for one or all persons in the database with the Ahnentafel number of the last person in each line, to 64 generations, exceeding RM’s 32 gen limit on the number. Also shows all lines, not just those of parents selected for display in RM’s main Views. | Ahnentafel 64 generations | Ahnentafel-64 | 2012-01-13 |
| A SQLite equivalent to RootsMagic’s own Search & Replace function on “Multimedia filenames” in the “Field to search” selection but with no programming constraints | Update Media Paths | Update Media Paths | 2012-01-11 |
| Removes CR/LF characters around custom fact sentences and person/family/alt name/fact notes and adds CR/LF pairs to ends of notes except the last, for first cut batch paragraphing. | Paragraphing | Paragraph-Strip | Paragraph-Add | 2011-12-22 |
| Re-orders all same day events of limited types to a natural order, e.g., Birth before Death | Dates – Same Day Sort Order | SortDateSameDayOrder | SortDateSameDayOrderCustom | 2011-12-19 |
| List and convert events having key properties in common to shared events. Especially useful on imports from Legacy Family Tree in which events were copied to multiple persons. | Sharable Events – Find and Convert to Shared | SharableFacts2 | SharableEvents-Convert | 2011-12-12 |
| Makes an empty Master from current database, preserving custom fact types and source templates, Places, Master Sources and associated Repositories and Addresses, and Place/Source Media | Depopulate but keep Customs, Places, Sources | CustomDatabaseShell | 2011-12-10 |
| A fact may show that it is shared with someone whose name is missing. This query lists such facts with the names of the principals and the RINs of the missing witnesses. | Shared Events With Missing Witnesses | TraceHeadlessWitnesses | 2011-12-10 |
| There are four Internet sites that RM5’s County Check is set to call for maps and other information. | County Check | – | 2011-12-10 |
| Clones a RM4/5 database without the RMNOCASE collation so that we’re free to do what we want to the data. Even works with RM but it might trip up on non-English alphabets. | Convert Database to NOCASE | RM#_CREATE_as_DB3_NOCASE | RM#_Copy_Data_to_DB3_NOCASE | 2011-12-09 |
| Report on the role, event and owner or principal of the shared event for which a witness or sharee is no longer a person in the database. | Shared Events With Missing Witnesses | TraceHeadlessWitnesses | 2011-12-09 |
| Marks or Unmarks members of a group according to a list; equivalent to memorising the checkboxes in RootsMagic Explorer for re-use. | Named Group – Mark or Unmark List refresh | Group Unmark List Refresh | Group Mark List Refresh | 2011-11-27 |
| Top page for a collection of queries for refreshing Named Groups | Named Group Refresh | Refresh – various | 2011-11-26 |
| Builds or refreshes a group of persons whose lifetime probably spanned a user-defined Census Year and who had some event in the user-defined jurisdiction but not a Census fact for that year. | Census Needed – Named Group | CensusNeededGroup | CensusNeededGroup2 | 2011-11-26 |
| Provides a manual refresh for a specific Named Group, i.e., the ancestors of a specified person. | Ancestors Named Group | AncestorsGroup | 2011-11-23 |
| Lists all the ancestral lines for a given RIN | Ancestors Query | Ancestors | 2011-11-23 |
| Produces a list of names that match or sound like specified names, similar to RM4’s NameFind. | Name Find query | NameFind | 2011-11-21 |
| Combo of SQLite queries and RM4 edits of Place List beats having to edit every fact/event to split a Place into Place and Place detail; otherwise, wait for the RootsMagician! | Places to Place Details Conversion | Places to Place Details Conversion | 2011-11-16 |
| Complete listing of all users of media in the Gallery, more complete and navigable than what is provided in RM 4.1.2.1 | Media Users List Query | Media Users List | 2011-11-09 |
| About as close as we can get to a RM4 Source List Report presented in tabular form with which the results can be sorted and filtered with relative ease. | Source List Query | Source List | 2011-11-05 |
| This query strips extraneous Carriage Returns from the end of the Footnote sentence template for the Source Template “Vital Records (state-level, online derivatives)” which cause unwanted white space in reports. | Source Templates | Fix Extra Line Feeds in Footnote | 2011-10-02 |
| Converts Sources using uneditable, built-in templates to using editable copies. | Source Templates | SrcTmpltsConvert | 2011-10-02 |
| Reverts Sources modified by SrcTmpltsConvert to using uneditable, built-in templates. | Source Templates | SrcTmpltsRevert | 2011-10-02 |
| A Query for a To Do List in a Grid Format – using the REFN to assign status for paragraphing and census. Versions for both SQLite directly and MS Access via SQLODBC. | A Query for a To Do List in a Grid Format | MyToDoList | 2011-07-07 |
| Another version of a Set Living query. | Another version of a Set Living query | SetLivingFlag | 2011-07-04 |
| A discussion of queries that can modify the Living flag. | Set Living Flag | SetLivingFlag | 2011-07-02 |
| Variant of CopyFact2Group that copies a REFN fact to a group, substituting the target persons’ PersonID’s or RIN for that of the source person. | Copy RIN to REFN | CopyRINtoREFN | 2011-06-29 |
| Two queries that list the test results in a format suitable for easy review and copying and pasting into other applications. RM4.1.1.4 provides no DNA report. | DNA Test results list | DNA_mtDNA_locationslist | DNA_Y-STR_markerslist | 2011-06-18 |
| Lists Source Names along with Source Detail field names; illustrates principles applicable to Master Source fields and other XML-like columns. | Source Detail View (Parsing XML) | Source Detail View (Parsing XML) | 2011-06-06 |
| Copies a fact/event for a person to a Named Group of persons, along with the Sources but not the Media for the Fact. | Copy Fact to Group | CopyFact2Group | 2011-04-05 |
| Lists files under the RM Multimedia default folder and flags those used by the RM database; helps to ensure that files are used. | Scrapbook Files Status | ScrapBookFilesStatus | 2011-03-13 |
| Backs up the database file along with all the media files referenced by it to one file. Uses the command versions of SQLite3 (free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a .rmgb file that RootsMagic can restore, media and database. | Backup Media with Database – RAR | RMfullbackup.bat | 2011-02-02 |
| Backs up the database file along with all the media files referenced by it to one file. Uses the command versions of SQLite3 (free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a .rmgb file that RootsMagic can restore, media and database. | Backup Media with Database – 7Zip | RMfullbackup.bat | 2011-02-02 |
| The RMSplit C# program below allows you to create a group in RootsMagic5 which contains a selected person, their ancestors, and a selected number of “leaves” (collateral lines us a given depth). | SplitTree | Rmsplit | 2011-01-03 |
| Series of queries to list duplicate media file names in the Media Gallery and list duplicate links to items in the Gallery. Repairs a specific case of duplicate file names as an example; repairs all cases of duplicate links. | Media Repair Queries | MediaRepair | 2010-12-14 |
| Discussion and examples of how you can search (filter) using any SQLite manager and replace found values with revised ones using SQLiteSpy. | Search & Replace | Search&Replace | 2010-11-09 |
| This query helps you plot events from your RootsMagic database on Google Maps, Google Earth, and Bing Maps, provided there are geo-coded Places and Place Details (sites) in your database. | MapEvents-KML query | MapEvents-KML | 2010-09-03 |
| Have you ever wished to be able to look at all the facts in your family tree database that happened within a day’s horseride of a certain location? This adaptation of the LifeLines query helps you view your events for any geographic area in addition to looking at the lifeline of any person in your database. | Geo-Lifelines Query | Geo-Lifelines | 2010-08-31 |
| Lists persons with multiple spouses in descending number – may flag a data problem | Multiple Spouses query | Multi-spouses | 2010-08-23 |
| Lists all source names cited for a person in descending order of the count of duplicate citations – a help in finding and resolving duplicate citations after merging. | All Citations & Dupes Count – Query | AllCitations+Dupes | 2010-07-08 |
| Lists Individuals whose Death Year from NameTable does not match that of the date for their Death fact. | Death Year Mismatch | DeathYearMismatch | 2010-06-04 |
| Explains why an integrity check is needed and how, using PRAGMA quick_check. | Check RootsMagic Database Integrity | Check RootsMagic Database Integrity | 2010-05-27 |
| Lists count of rows in each table — If not 22 tables listed, database has corruption | RMGC Tables Row Count | RMGC_TablesRowCount | 2010-05-26 |
| Lists all events for all persons whether in a database tree or not, including shared facts, date, fact detail, site and place, MRIN, other parties, and duplication indicator. Sorted by RIN and Sort Date. With the right SQLite manager, can filter results for one person. | LifeLines – Query | LifeLines | 2010-03-15 |
| Lists people who share a fact with a Principal, as well as relevant fact and Principal information. | People Who Share a Fact with a Principal List – Query | People Who Share a Fact with a Principal | 2010-03-10 |
| Lists people who share a fact with a Principal, but who aren’t in a tree in the database file, as well as relevant fact and Principal information. | People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File List – Query | People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File | 2010-02-26 |
| Lists duplicate name pairs with a weighted score indicating degree of match. On a large database, produces results in 4.5 minutes similar to what RootsMagic 4’s Duplicate Search Merge tool does in 45 min. | Duplicate Name Search – query | DuplicateNameSearch | 2010-02-23 |
| Copies all unmerged pairs from the results of RM4’s Duplicate Search Merge Tool to RM4’s ExclusionTable so that they will not reappear in subsequent runs of Duplicate Search Merge. | Duplicate Search Merge Database | MarkNotProblem | 2010-02-21 |
| Lists Sources associated with Source Templates, as well as an extension to Citation details. Free-Form templates are excluded since they’re not included in SourceTemplateTable. | Source Template List – Query | SourceTemplateListWithCitationDetails2.sql | 2010-02-13 |
| As ‘Database Properties’ but with more detail and pointing out possible problem areas | RMGC_Properties – Query | RMGC_Properties | 2010-01-31 |
| Deletes citations of non-existent Sources from database that manifest themselves in the Edit Persons screen as a citation (counted and checkmarked) but return nothing when opened. | Delete Phantom Citations – Query | Delete Phantom Citations | 2010-01-30 |
| Lists all citations in the database from which citations of non-existent sources (‘phantoms’) and citations for non-existent events or persons (‘headless’) can be found, along with other useful information such as all citations per source. | All Citations – Query | AllCitations | 2010-01-30 |
| Reports in a list most of the values found in RM4’s File > Properties report; results can be exported to a file. | Database Properties List – Query | Database Properties | 2010-01-29 |
| Decodes most of the possible formats found in RM4 Date fields of the form Da+nnnnnnnn.x+nnnnnnnn.x | Date Decoder | DateDecoder | 2010-01-21 |
| Lists uncompleted To Do tasks for Individuals, Families and General; can be readily modified to filter or sort by surname, priority, repository, etc. | RM4 To Do List | To-Do (was To-Do4Persons) | 2010-01-18 |
| Lists Individuals whose Birth Year from NameTable does not match that of the date for their Birth fact. | Birth Year Mis-Match | BirthYearMisMatch | 2010-01-17 |
| Returns all Places with Place Details and the Persons or Families (couples) and their Facts, including Shared events, using the Place Detail. | Facts Having Place Details – Query | FactsHavingPlaceDetails3 | 2010-01-16 |
| An optional extension to AllFacts4Persons or standalone. Having a Child as an event for the Father and the Mother. | Births of children as facts | PersonsBegatChildren | 2010-01-14 |
| List all the Individual, Family and Shared Facts/Events for all persons in a database. A complex query using UNION ALL, COUNT() and GROUP BY, constants and NULL to assemble multiple SELECTs in one big result. | Pulling Together All the Events for An Individual | AllFacts4Persons | 2010-01-06 |
| List Persons with Blank Names in the Address List — (a fault that may occur in a GEDCOM import). | Four Little Queries | blankname_in_addresslist | 2009-12-30 |
| List of unused Places | Four Little Queries | UnusedPlaces | 2009-12-30 |
| Lists Places having Place Details | Four Little Queries | PlacesDetails | 2009-12-30 |
| List Persons with specified Surnames. — Example of creating a SQL View or Virtual Table and the explicit use of COLLATE NOCASE to override the RMNOCASE collation defined for certain fields and embedded in the RootsMagic application. | Four Little Queries | selected_surnames | 2009-12-30 |