Facts – Sort Order Problems #events #date #sortdate

Introduction

There has been a longstanding wish that RootsMagic would sort events that have no date or the same date (which might be as coarse as just the year) in an order that was natural or logical, e.g., Birth before Death being the simplest and most obvious example. The problem is that RootsMagic will sort these same date events in the order in which they were entered, i.e., in table row number order. The effect in a narrative report can be quite jarring.

To make matters worse, RM does not even provide a tool to find instances of such disturbing sequences. Its Tools > Problem Search > Problem list feature does have promising criteria:

  • Proper order of events
  • Birth before parent’s marriage
  • Birth before parent’s birth
  • Birth after father’s death
  • Birth after mother’s death

Unfortunately, same or empty dates are ignored!

A prior SQLite workaround, Dates – Same Day Sort Order, addresses many of these problems by modifying the SortDate value in the EventTable with offsets that correspond to “hyphen” dates in the Sort date field of the Edit Person screen, e.g., dd mmm yyyy – 10. This even works for blank dates – however, the offset Sort date remains blank. The script is limited to offsetting dates without certain modifiers because of the SortDate decoder used at the time. That, together with the small number of fact types it addresses, still leaves certain problems with event ordering that can only be resolved with manual editing.

How can one easily and quickly find these outstanding problems, given that the RM Problem Search does not, leaving one to rely on proof reading voluminous reports to detect disturbing sequences of sentences? This page offers a concept and a script that attempts to address this deficiency.

Concept

My initial approach was to build on the offset concept used in Dates – Same Day Sort Order, i.e., an ordering factor similar to its offsets: the lower the order number the earlier the event in the natural order, the higher the order number, the later. Then I realised that some events necessarily occur not before nor later than some other event which led to a minimum and maximum order number. So I thought a lookup table for the order range for each fact type would be needed and built it with the help of Excel:

FactTypeIDNameOrderMinOrderMaxSQL
503Stillborn910INSERT OR REPLACE INTO xFactOrderTable VALUES (503,’Stillborn’,9,10);
6Adoption10200INSERT OR REPLACE INTO xFactOrderTable VALUES (6,’Adoption’,10,200);
10Blessing10200INSERT OR REPLACE INTO xFactOrderTable VALUES (10,’Blessing’,10,200);
18Census10200INSERT OR REPLACE INTO xFactOrderTable VALUES (18,’Census’,10,200);
29Residence10200INSERT OR REPLACE INTO xFactOrderTable VALUES (29,’Residence’,10,200);
504Illness10200INSERT OR REPLACE INTO xFactOrderTable VALUES (504,’Illness’,10,200);
505Living10200INSERT OR REPLACE INTO xFactOrderTable VALUES (505,’Living’,10,200);
1Birth1010INSERT OR REPLACE INTO xFactOrderTable VALUES (1,’Birth’,10,10);
3Christen2020INSERT OR REPLACE INTO xFactOrderTable VALUES (3,’Christen’,20,20);
7Baptism3030INSERT OR REPLACE INTO xFactOrderTable VALUES (7,’Baptism’,30,30);
12Confirmation4040INSERT OR REPLACE INTO xFactOrderTable VALUES (12,’Confirmation’,40,40);
13First communion5050INSERT OR REPLACE INTO xFactOrderTable VALUES (13,’First communion’,50,50);
304Engagement95100INSERT OR REPLACE INTO xFactOrderTable VALUES (304,’Engagement’,95,100);
305Marriage Bann96100INSERT OR REPLACE INTO xFactOrderTable VALUES (305,’Marriage Bann’,96,100);
306Marriage Contract97100INSERT OR REPLACE INTO xFactOrderTable VALUES (306,’Marriage Contract’,97,100);
307Marriage License98100INSERT OR REPLACE INTO xFactOrderTable VALUES (307,’Marriage License’,98,100);
308Marriage Settlement99100INSERT OR REPLACE INTO xFactOrderTable VALUES (308,’Marriage Settlement’,99,100);
510Separation105120INSERT OR REPLACE INTO xFactOrderTable VALUES (510,’Separation’,105,120);
14Ordination100200INSERT OR REPLACE INTO xFactOrderTable VALUES (14,’Ordination’,100,200);
21Graduation100200INSERT OR REPLACE INTO xFactOrderTable VALUES (21,’Graduation’,100,200);
22Retirement100200INSERT OR REPLACE INTO xFactOrderTable VALUES (22,’Retirement’,100,200);
24Education100200INSERT OR REPLACE INTO xFactOrderTable VALUES (24,’Education’,100,200);
300Marriage100200INSERT OR REPLACE INTO xFactOrderTable VALUES (300,’Marriage’,100,200);
310Residence (family)100200INSERT OR REPLACE INTO xFactOrderTable VALUES (310,’Residence (family)’,100,200);
311Census (family)100200INSERT OR REPLACE INTO xFactOrderTable VALUES (311,’Census (family)’,100,200);
500Degree100200INSERT OR REPLACE INTO xFactOrderTable VALUES (500,’Degree’,100,200);
501Military100200INSERT OR REPLACE INTO xFactOrderTable VALUES (501,’Military’,100,200);
301Annulment110200INSERT OR REPLACE INTO xFactOrderTable VALUES (301,’Annulment’,110,200);
303Divorce filed115200INSERT OR REPLACE INTO xFactOrderTable VALUES (303,’Divorce filed’,115,200);
302Divorce120200INSERT OR REPLACE INTO xFactOrderTable VALUES (302,’Divorce’,120,200);
2Death200200INSERT OR REPLACE INTO xFactOrderTable VALUES (2,’Death’,200,200);
5Cremation210210INSERT OR REPLACE INTO xFactOrderTable VALUES (5,’Cremation’,210,210);
4Burial220220INSERT OR REPLACE INTO xFactOrderTable VALUES (4,’Burial’,220,220);
20Will230230INSERT OR REPLACE INTO xFactOrderTable VALUES (20,’Will’,230,230);
19Probate240240INSERT OR REPLACE INTO xFactOrderTable VALUES (19,’Probate’,240,240);

My mind was spinning by the time I got this far so I went on to build a partial ordering table and a prototype script to test out the concept. I realized rather quickly as I tried to assign the order values that this concept is flawed because some events could be conditional – I found it confusing if I thought an order value was somehow indicative of probable age in addition to natural order. The implication is that the analysis should be probabilistic, not deterministic, something like RM’s Duplicate Search Merge or my Duplicate Name Search – query.

Fact Order Table

The above series of INSERTs were copied and pasted into a script that builds a temporary table
xFactOrderTable within the target database. You open the database with SQLiteSpy and then open and execute this script.
FactOrderTable.sql

If you want to play with the order values, you can download this spreadsheet that includes all of RM’s standard fact types and replace the series of INSERTs in
FactOrderTable.sql and re-run it:
FactOrder.xlsx

Sort Order Problems Query

This initial script uses only the OrderMin values in a deterministic algorithm and gives results like this:

ProblemDateSharer NameSharer Name:1
Marriage before Birth: BLAKESTON , W. J.-1047PARSLOW , Ann J.-1048
Marriage before Birth: PARSLOW , Ann J.-1048BLAKESTON , W. J.-1047
Marriage before Birth: [WOOLDRIDGE] , Anne-1085WOOLDRIDGE , Elipat-1084
Burial before Marriage: HOLDEN , James-11031846CASKER , Elizabeth-1104
Death before Marriage: HOLDEN , James-11031846-04-07 – 1846-04-10CASKER , Elizabeth-1104
Death before Birth: *ORDER , Birth Death-11891900

The Problem list of RootsMagic 6 returned 15 problems with all the criteria shown in the Introduction included, 11 for solely ‘Proper order of events’. For the same database, this script returned 157 potential problems. Note that the script does detect events on blank dates that are out of natural order in their position in the EventTable. It also reports as problems some sequences that may not cause a problem in reports because of the separation of ‘family’ events from ‘individual’ events, e.g. most of those listed above; should RM be revised to make narrative reports purely chronological, these could well become problems, if nothing is done to provide natural ordering of same date events. Sorting the results on Problem can put the focus on common types of problems; initially, the problems are sorted by RIN.
SortOrderProblems.sql Remember to run FactOrderTable.sql before running this script. The temporary tables they add to the database are dropped when SQLiteSpy closes the database.

You could run this script before and after running Dates – Same Day Sort Order to see how many (and what types of) problems it resolves.

This script is an experiment, a work-in-progress that may go no further; it has not been optimised for speed and will likely run very slowly on even moderate size databases.

In developing this query, it became clear that additional (not implemented) special measures are needed to detect the marriage of a person prior to their divorce from a previous spouse.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.