Using SQL to Manage Source Templates in RootsMagic
Have you ever considered what programming language your genealogy software uses? When I switched from The Master Genealogist to RootsMagic, one of the deciding factors was that RootsMagic utilizes SQL. Since I had experience using SQL to create reports in PowerSchool, this influenced my software choice.
Although I made the switch in 2016, it wasn’t until recently that I began using scripts to make significant changes to my database. Thanks to Mr. Otter’s well-written instructions, I successfully ran his Switch Source Template script. This allowed me to convert my _EE_FamilySearch_COUNTY_RECORDS_online_images_FILM-Specific-website template into a new format that properly supports angle brackets (<>) around image numbers.
With that change completed, I wondered whether I could safely delete the original source template. However, RootsMagic does not provide a built-in report to show which sources are linked to a particular template, leaving me unsure whether the old template was still in use.
To find a solution, I explored the SQLite Tools for RootsMagic website, where I discovered SQL code designed to identify which sources are associated with specific source templates. Unfortunately, this code was written for an earlier version of RootsMagic. However, the comments on the page provided a key insight: both the SourceTable and the SourceTemplateTable contain a field named TemplateID.
Determined to find an answer, I turned to the LEARN tab on the SQLite Tools site and found another excellent tutorial by Mr. Otter on running SQL queries within a RootsMagic database. Following the tutorial, I accessed my database tables and located my original source template in the SourceTemplateTable. Its TemplateID was 10286.
Next, I checked the SourceTable to see if any sources were still using that TemplateID. By default, the table displayed all sources, but I applied a custom filter to the TemplateID field to isolate results.
Since no sources appeared in the filtered list, I confirmed that the script successfully updated all affected sources. This means the original template is no longer in use and can be safely deleted.
Thanks to Mr. Otter and to everyone who has contributed to the SQLite Tools for RootsMagic for helping me query my RootsMagic file.