Exploit your RootsMagic family tree database with SQLite Tools
Operations on persons and families and relationships, esp on PersonTable, FamilyTable and ChildTable.
As of Feb 3, 2014 and version 3.8.3, SQLite introduced “Recursive common table expressions (that) provide the ability to do hierarchical or recursive queries of trees and graphs, a capability that is not otherwise available in the SQL language”. (http://www.sqlite.org/lang_with.html) This means it is now possible to generate ancestry or descendancy lists within SQLite, without…
RootsMagic 7 and prior has the peculiar shortcoming that ancestral lineage reports, charts and other important functions follow the lineage set by the parent family displayed in the last view of each person in one of the Main screens. If the last time you viewed your mother showed her stepfather as her father, then her…
Intro This query lists all the ancestral lines for a given RIN in your database out to the 13th generation. It is just a table of RINs but could be extended to provide names et al. More significantly, it can be the basis of other queries that need to follow ancestral lines. One of these,…
After running the query Children-SetOrderByBirthSortDate.sql, there may remain families that need to be manually arranged because one or more of the children has no birth-type fact or has a dominant birth-type fact with no date. This query produces a list of the parents who have such children. Look up and select one of the parents…
There have been recurring wishes expressed in the RootsMagic Forums for an enhancement to globally sort each family of children in the order they were born. For example, have a look at this thread, Sorting all children in the database by birth order. Expressed there are good reasons to be cautious about such an operation…
Superseded 2015-01-25. See Living Flag – Set Globally. I’m posting my version of SQL for Set Living. It proves to be remarkably similar to Tom’s. SQL #1, we turn on the Living Flag for everybody in the database. Essentially, we make everybody private and then followup later by setting individuals as not private when they…
Superseded 2015-01-25. See Living Flag – Set Globally. Discussion in RootsMagic-Users pointed out some limitations with the Set Living tool in RootsMagic and the risk of unsetting persons that have been previously set correctly. This page discusses how SQLite might help and is intended to start the development of some useful queries. The Living flag…
Finally! A script that sets the Living flag globally without having to be run repeatedly until all ancestors are accounted for. It does so with the recursive query feature introduced in SQLite3 in 2014 (see the page Recursive Queries – Ancestors and Descendants for more background). This script supersedes prior efforts by Tom and Jerry,…
This little query responds to the desire expressed by RootsMagic Forums member Ksquared to relocate Titles of Nobility from Prefix to Suffix. This is where they ended up in the direct import of his TMG project to RootsMagic. It moves the prefix for each name to the suffix field, provided it is empty. If the…
Why? RootsMagic 6.3.3.2+ imports the TMG_ID into the standard RootsMagic Reference No. fact type (REFN). While this fact type can be displayed after the name of the person in main views and reports, it cannot be displayed in the sidebar Index and in some other places. Some users would prefer that it did; the only…