Exploit your RootsMagic family tree database with SQLite Tools
Design, definitions, structure, operations…
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…
RootsMagic 4+ Built-In Fact Types 2023-05-22 updated to RM9 The built-in fact types for RM4 and later can be found in the FactTypeTable with FactTypeID less than 1000. User-defined/custom types are assigned values of 1000 and higher by the program when created. Between RM4 and RM9, only the 902-Associations fact type has been added. There…
A singular system table, sqlite_master, resides in a SQLite database. This table in the RootsMagic 6 database essentially defines the RootsMagic 6 database tables, indices, and fields, as below. The difference in structure with RootsMagic 5 is the addition of one new table, URLTable (no index) for WebTags. type name tbl_name rootpage sql table…