Dates – Same Day Sort Order #date #sortdate #update

Problems

Some RootsMagic users bemoan the fact that events having the same date are displayed and reported in an unnatural or illogical order, having more to do with the order they were entered. Burial before Death or Christen before Birth are often mentioned. RootsMagic does provide the user with the option to modify the SortDate date to establish the desired order.

RM’s proprietary SortDate is lost in an export to another program; any import into RM from anything other than a RM database or an RM generated GEDCOM with the Extra Details (RM specific) box checked will be assigned SortDates matching the event date resulting in the probable loss of the desired order. So manual control over the SortDate is of no comfort to those who move their database between different programs to take advantage of their respective strengths. An enhancement to provide better results for the order of same day events has long been on the RootsMagic Wish List.

Workaround

Meanwhile, here are two SQLite queries that can re-order all same day events of limited types in seconds with results as shown in the screenshots below. They both exploit the SortDate feature that values of the form date-n, where n is an integer from 1 to 4999, are sorted in ascending order.

Before and after the query was applied.

SortDateSameDayOrder.sql

SortDateSameDayOrder.sql, currently addresses the following in the order listed: Birth, Christen, Baptism, Death, Cremation and Burial. Others could be added. Should work with any SQLite manager on both RM4 and RM5 databases. Currently deprecated because it changes other facts on the same day; left here because its comparative simplicity may help to understand its successor below.

/* SortDateSameDayOrder.sql
   2011-12-19 ve3meo
   Alters SortDates of Birth, Christen, Baptism, Death, Cremation and Burial
   to a natural order when any pair or more occur on the same date.
   Could be extended to order other facts also. SortDates are effectively assigned
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all
   Event dates are handled, e.g. "Bef 1960".
   2011-12-20 order of Cremation and Burial corrected
*/
 
UPDATE EventTable
SET SortDate = SortDate-6692012023*(EventType
  IN (1,3,7,2,4,5))  -- list of FactTypes we want to sort, in no particular order except this corresponds to the desired order
  +1048576*(SUBSTR('1426503',EventType,1)-1) -- the substr maps the FactType to its order
WHERE EventID
IN (SELECT EventID FROM EventTable
    INNER JOIN
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches FROM EventTable
     WHERE OwnerType = 0 AND EventType IN (1,3,7,2,5,4)
     AND
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE
        WHEN DATE LIKE '.%'
        THEN 1
        ELSE Substr(DATE,3,5) END
        +10000
        )*562949953421312
        + Substr(DATE,8,2)*35184372088832
        + Substr(DATE,10,2)*549755813888
        + 17178820620
      GROUP BY SortDate, OwnerID, OwnerType
     )
     USING (OwnerID, SortDate)
     WHERE Matches
    )
;

SortDateSameDayOrderCustom.sql

SortDateSameDayOrderCustom.sql
Goes to the next level, supporting the ranking of any number of facts, be they standard or custom. It also corrects an oversight in SortDateSameDayOrder.sql and does not touch unranked facts on the same date. Requires the use of a fake RMNOCASE collation or possible revision with COLLATE NOCASE phrases where FactTypeTable.Name is used. Because SortDate supports up to date-4999, this query could be modified to have a multiplier of the rank, thus leaving room for other events on the same date to be manually added with a SortDate lying between those assigned by the query, without having to revise the existing SortDates.

/* SortDateSameDayOrderCustom.sql
   2011-12-20 ve3meo
   Alters SortDates of any set of Fact types
   to a natural order when any pair or more occur on the same date.
   Could be extended to order other facts also. SortDates are effectively assigned
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the rank of the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all
   Event dates are handled, e.g. "Bef 1960".
*/
DROP TABLE IF EXISTS TmpFactOrder
;
CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder
(Rank INTEGER PRIMARY KEY, FactName TEXT)
;
 
/* list of Fact Names, standard and custom, to be sorted, in rank order.
   Revise the list to suit your needs */
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Christen');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Baptism');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Funeral');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Cremation');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Obituary');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Memorial');
/* revise SortDates */
UPDATE EventTable
SET SortDate = SortDate
  -6692012023  -- this offset goes to Date-1 if the event is a ranked event
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )>0
    )
  +1048576  -- this offset adds steps of 1 to Date-1 multiplied by (rank-1)
  *( (
      SELECT Rank
      FROM TmpFactOrder,
           FactTypeTable
      WHERE FactName LIKE Name
      AND FactTypeID = EventType
      )-1
    ) -- maps the FactType to its order
WHERE EventID
IN (SELECT EventID FROM EventTable
    INNER JOIN
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches
     FROM EventTable
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
     AND
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE
        WHEN DATE LIKE '.%'
        THEN 1
        ELSE Substr(DATE,3,5) END
        +10000
        )*562949953421312
        + Substr(DATE,8,2)*35184372088832
        + Substr(DATE,10,2)*549755813888
        + 17178820620
      GROUP BY SortDate, OwnerID, EventTable.OwnerType
     )
     USING (OwnerID, SortDate)
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID
     WHERE Matches
     AND EventTable.OwnerType = 0
     AND Name IN (SELECT FactName FROM TmpFactOrder)
    )
;

Discussions & comments from Wikispaces site