Exploit your RootsMagic family tree database with SQLite Tools
Description Here are the most advanced and useful SQLite queries for reviewing citations in your RootsMagic 4-8 database. They provide some of the same info that you would get in the RootsMagic Source List report and then some you don’t… You have the advantage of sorting and filtering results in ways you cannot within RM…
2023-02-19 Version for #RM8 added. Creates a Named Group in the open database with the name “SQL: Duplicate Events” if one does not already exist Removes all members from that named group Populates that named group with persons having “duplicate” events Duplication is declared if a pair of events: (have dates for which the span…
Problems Some RootsMagic users bemoan the fact that events having the same date are displayed and reported in an unnatural or illogical order, having more to do with the order they were entered. Burial before Death or Christen before Birth are often mentioned. RootsMagic does provide the user with the option to modify the SortDate…
Creates a Named Group in the open database with the name “SQL: Text Dates” if one does not already exist Deletes all members from that named group Populates that named group with persons having an event date that is backgrounded in yellow on the Edit Person screen, i.e., an invalid date format interpreted by RootsMagic…
Rebuilding the indexes of large databases using RootsMagic’s File > Database tools > Rebuild Indexes function is ponderously slow, still at version 6.0.0.2. A 162000 person database took over 100 minutes. This page provides procedures that take but a few minutes. In RootsMagic version 5.0.2, the Rebuild Indexes tool was introduced under Menu > File…
Work in progress. This query lists tasks related to Individuals, Families (Father’s name only outputted) and General research. Check it out and see if it’s useful. You can run it on the same database as RM4 is using (at your own risk) to assist finding tasks in RM4 or people to work on; alternatively, make…
Lists individuals whose Death Year is missing from RootsMagic Explorer (and other views and reports where just the YEAR is outputted) or mismatches the value that has been stored in the Date field of the Death fact. Adapted from the query Birth Year Mismatch. New: see Rebuild Indexes and Update Birth and Death Years for…
Lists individuals whose Birth Year is missing from the sidebar (and other views and reports where just the YEAR is outputted) or mismatches the value that has been stored in the Date field of the Birth fact. This is less of an issue as of RM 5.0.2.0, which incorporates an update of the BirthYear and…
Storage formats Within the RootsMagic 4 database, several date-related fields exist. These fields can be grouped into into four different storage types: FLOAT, with the integer part representing number of days since 1899 Dec 31 and fractional part representing time of day EventTable – EditDate LinkTable – extDate (presumably) NameTable – EditDate (presumably) PersonTable –…
RM8 replaced Edit Date with UTCModDate Previous versions had an EditDate column in a few tables. In #RM8, these have been replaced by UTCModDate which is present in most tables and appears to serve the same functions, one of which is to populate the “Date Edited” field in the People List view. The “Date Edited”…