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, respectively:
Set Living Flag
Another version of a Set Living query
It is simple enough to set the Living flag to false when a person has a Death fact. The previous scripts went further than that, e.g., deeming a person to be dead if their Birth was more than 105 years ago, as is the case with RootsMagic. Where they came to a halt or grinding reiteration was the need to deem dead all ancestors of all such persons deemed dead.
This new script expands on the deemed dead criteria and utilises a variant of the recursive query referenced above to cover all their ancestors. Here is an outline of the expanded criteria for setting the Living flag to False (for dead):
1. person with individual or family events before current year less 105, e.g., 1910 in 2015;
2. person whose spouse has individual events before this cusp year;
3. children of persons with birth before a certain year or death before a later year should be deemed dead (if person born >105 yrs ago deemed dead, then
a) child of parent born > (105 + 25) yrs ago could be deemed to have been born around 105 years ago and now dead
b) child of parent who died > 105 years ago can be deemed to have been born around the same time and now dead;
4. spouses of persons deemed dead are probably also dead; (arguably a bit risky but, without it, many ancient spouses can be left Living)
5. ancestors of all the above persons;
6. person with death fact;
For everyone else, the script sets the Living flag to true.
From initial testing, the results look pretty reliable. I think there could be isolated instances of a person deemed living when they should be dead. For example, any person with more than 3 generations of descendants, none of which have any dated events or a Death fact – a pretty unlikely scenario. That said, it is desirable for many other reasons that an estimated era for Birth and Death should be entered and that would also resolve this issue.
A complementary script sets color coding to Red for Living flag true and to Green for Living flag false.
Both scripts should run on any SQLite3 manager compiled with SQLite 3.8.3 or later.
LivingFlag-GlobalSet.sql
ColorCode-byLivingFlag.sql
2015-01-25 1st release