DOD vs Burial Date
RM 9 Windows 11 - I am not picky about burial dates. However I would like to blank out the burial date if it is over 30 days of the death date. Does anyone have a script that would compare the dates so I can blank the burial date if it meets the criteria. Also do I need to do anything with the sort date?
Thanks to anyone that can help.
From the way you have framed your question, it sounds like you want a list of people whose burial date is more than 30 days after their death date so that you can edit the person 's profile in RM to delete the burial date. If that is accurate, perhaps it would be satisfactory to create a Custom Report listing the RIN, Name, Death Date, Burial Date, filtered on a group having both Death and Burial Events. Using RM9, save the report to Excel format. Open it with Excel to process the Event Dates into a supported Excel Date format and calculate the difference in days. Then filter in Excel to show only those whose difference is >30 days.
After suggesting the above, I just tried it and it worked with a couple of caveats:
- plain dates were automatically interpreted as dates by Excel and a simple formula calculates the difference in days.
- RM date modifiers such as "after" or "before" are not interpreted by Excel as dates and the difference formula returns an error.
- A partial date is handled as that number or maybe assumes the current year, resulting in a gross miscalculation, e.g.
31 Jul 1993 3 Aug 11326 1918 28 Oct 1918 4958 - Maybe worst of all, is that Excel does not interpret dates older than 1 Jan 1900 as dates so the difference formula gives a #VALUE! error. You would have to research how to handle older dates in Excel.
I felt a need to distract myself from all the prep before moving day next week and poked into this further. I thought of parsing the dates from the custom report and shifting the year by 1000 so that all could be interpreted by Excel's 1900 date system. But that just seemed too onerous for my Excel skills and so I went back to SQLite and came up with a query that produces this useful list of people whose burial event is recorded as over 30 days after their death event.
| RIN | DeathDate | BurialDate | Died | Buried |
|---|---|---|---|---|
| 163 | D.+16820205..+00000000.. | D.+16990913..+00000000.. | 1682-02-05 | 1699-09-13 |
| 360 | D.+20130529..+00000000.. | D.+20170717..+00000000.. | 2013-05-29 | 2017-07-17 |
| 2059 | DO+17780628..+17780817.. | D.+17780817..+00000000.. | 1778-06-28 | 1778-08-17 |
| 2237 | D.+18780619..+00000000.. | D.+18790621..+00000000.. | 1878-06-19 | 1879-06-21 |
| 2986 | D.+19980427..+00000000.. | D.+20130502..+00000000.. | 1998-04-27 | 2013-05-02 |
| 4029 | D.+19421231..+00000000.. | D.+19430512..+00000000.. | 1942-12-31 | 1943-05-12 |
The DeathDate and BurialDate columns show the format that the event date is stored as in the RM database. The query returned 91 cases in this database where the delay between death and burial dates is over 30 days.
Compare that with the 69 I got from the Custom Report=>Excel process:
| RIN | Died | Buried | Delay |
| 360 | 29 May 2013 | 17 Jul 2017 | 1510 |
| 2986 | 27 Apr 1998 | 2 May 2013 | 5484 |
| 4029 | 31 Dec 1942 | 12 May 1943 | 132 |
Note the latter misses the pre-1900 events because of Excel's date limitation to post-1899 dates.
The sql query could also bring out the record number (EventID) of the Burial event to a temporary table or view which could be used in another query to blank the Burial date.
I'm sure those with higher level language skills than mine could do this more elegantly...
Uploaded files:Adding that you use this type of query as a subquery and create a group which is sometimes What I do