Exploit your RootsMagic family tree database with SQLite Tools
Design, definitions, structure, operations…
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…
Description Lists the properties of a RootsMagic 4 or 5 database except for dates, plus reports extra detail characterizing the database and identifying possible problems in it or in transfers to another other RootsMagic database via GEDCOM or drag’n’drop. Most SQLite managers can export the results to a .csv spreadsheet for tracking and comparison. Could…
Problem Currently under RootsMagic 4, the only way to obtain the Database Properties for a file is to go to File > Properties and use Alt+Prnt Screen (or Ctrl+Prnt Scrn) to copy the screen to the clipboard and use CTRL+V to paste the contents to a program capable of rendering the result. Solution pt 1…
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”…
Initial SortDate Algorithm The SortDate column in the EventTable contains very large numbers that have defied until now any easy method of decoding. After many experiments with date entry and inspection of the stored values, I now have deduced an algorithm that works fine for encoding single, pure dates and decoding corresponding SortDates. Date modifiers…
(See Backup Media with Database – 7Zip for a similar procedure using 7-zip.) Here is a one-click backup procedure that stores the database file and all the media files it references in one solid, compressed backup file, preserving the path to each. Many have lamented the fact that RootsMagic 4 neither stores images in its…
RootsMagic 4+ Built-In Roles The built-in roles for RM4 and later can be found in RoleTable with RoleID up through 58. User-defined/custom types are assigned values of 59 and higher by the program when created. Built-in roles and their unedited values within the table are as follows: RoleID RoleName EventType RoleType Sentence 1 Witness 1…