#RM10 compatible
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
thejerrybryan
11 September 2017 16:37:37
Aha (I think)!
I commented out the following code, and it seems to have solved my problem. I was trying to make changes within the commented out code, and I just needed to get rid of the commented out code entirely.
/* 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 */
I would be interested in opinions concerning the following observation. It seems to me that commenting out the code in question might be a good idea for the general use case, not just for my specific use case. Which is to say that even with this code removed from the script, the script still does not modify sort dates that are different. For example, if the RM user has established a birth sort date of 1 Jul 1850-1 and a death sort date of 1 Jul 1850-2, then the script will not modify the sort dates even with the code commented out as indicated. The sort dates are not identical and are therefore are prevented from matching by INNER JOIN’s. They don’t need to be prevented from matching by the code that has been commented out. At least that’s the way it seems to me, and that’s what I’m going to go with for now.