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:

FactTypeID Name OrderMin OrderMax SQL
503 Stillborn 9 10 INSERT OR REPLACE INTO xFactOrderTable VALUES (503,’Stillborn’,9,10);
6 Adoption 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (6,’Adoption’,10,200);
10 Blessing 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (10,’Blessing’,10,200);
18 Census 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (18,’Census’,10,200);
29 Residence 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (29,’Residence’,10,200);
504 Illness 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (504,’Illness’,10,200);
505 Living 10 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (505,’Living’,10,200);
1 Birth 10 10 INSERT OR REPLACE INTO xFactOrderTable VALUES (1,’Birth’,10,10);
3 Christen 20 20 INSERT OR REPLACE INTO xFactOrderTable VALUES (3,’Christen’,20,20);
7 Baptism 30 30 INSERT OR REPLACE INTO xFactOrderTable VALUES (7,’Baptism’,30,30);
12 Confirmation 40 40 INSERT OR REPLACE INTO xFactOrderTable VALUES (12,’Confirmation’,40,40);
13 First communion 50 50 INSERT OR REPLACE INTO xFactOrderTable VALUES (13,’First communion’,50,50);
304 Engagement 95 100 INSERT OR REPLACE INTO xFactOrderTable VALUES (304,’Engagement’,95,100);
305 Marriage Bann 96 100 INSERT OR REPLACE INTO xFactOrderTable VALUES (305,’Marriage Bann’,96,100);
306 Marriage Contract 97 100 INSERT OR REPLACE INTO xFactOrderTable VALUES (306,’Marriage Contract’,97,100);
307 Marriage License 98 100 INSERT OR REPLACE INTO xFactOrderTable VALUES (307,’Marriage License’,98,100);
308 Marriage Settlement 99 100 INSERT OR REPLACE INTO xFactOrderTable VALUES (308,’Marriage Settlement’,99,100);
510 Separation 105 120 INSERT OR REPLACE INTO xFactOrderTable VALUES (510,’Separation’,105,120);
14 Ordination 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (14,’Ordination’,100,200);
21 Graduation 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (21,’Graduation’,100,200);
22 Retirement 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (22,’Retirement’,100,200);
24 Education 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (24,’Education’,100,200);
300 Marriage 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (300,’Marriage’,100,200);
310 Residence (family) 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (310,’Residence (family)’,100,200);
311 Census (family) 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (311,’Census (family)’,100,200);
500 Degree 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (500,’Degree’,100,200);
501 Military 100 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (501,’Military’,100,200);
301 Annulment 110 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (301,’Annulment’,110,200);
303 Divorce filed 115 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (303,’Divorce filed’,115,200);
302 Divorce 120 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (302,’Divorce’,120,200);
2 Death 200 200 INSERT OR REPLACE INTO xFactOrderTable VALUES (2,’Death’,200,200);
5 Cremation 210 210 INSERT OR REPLACE INTO xFactOrderTable VALUES (5,’Cremation’,210,210);
4 Burial 220 220 INSERT OR REPLACE INTO xFactOrderTable VALUES (4,’Burial’,220,220);
20 Will 230 230 INSERT OR REPLACE INTO xFactOrderTable VALUES (20,’Will’,230,230);
19 Probate 240 240 INSERT 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:

Problem Date Sharer Name Sharer Name:1
Marriage before Birth: BLAKESTON , W. J.-1047 PARSLOW , Ann J.-1048
Marriage before Birth: PARSLOW , Ann J.-1048 BLAKESTON , W. J.-1047
Marriage before Birth: [WOOLDRIDGE] , Anne-1085 WOOLDRIDGE , Elipat-1084
Burial before Marriage: HOLDEN , James-1103 1846 CASKER , Elizabeth-1104
Death before Marriage: HOLDEN , James-1103 1846-04-07 – 1846-04-10 CASKER , Elizabeth-1104
Death before Birth: *ORDER , Birth Death-1189 1900

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.