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.