Dates+-+Same+Day+Sort+Order

toc 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.

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.

=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. **// code format="sql" /* 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    ) code

=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. code format="sql" /* 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)    )

code