Logo Home    Page list

Facts - Sort Order Problems #events #date #sortdate

Table of Contents

Introduction
Concept
Fact Order Table
Sort Order Problems Query

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:
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.
www.000webhost.com