Delete burial dates which only contain the year or month and year
I have found that when importing individuals or families from FamilySearch, very frequently there is a burial date which only contains the year or the month and year instead of a full date. This is really useless information and I would like to be able to delete all burial dates that are not full dates. I was thinking maybe it would be possible to delete those that only contain 4 characters (i.e. "1900" and those that contain only 8 characters (i.e. "Jan 1900"). If there is not a burial location or description, then I would like to delete the entire fact, but if there is a burial location or text in the description, I obviously want to keep that information, so I don't want to delete the entire fact, just the useless dates. I hope this makes sense.
I'm going to offer you three options. You probably won't like Option #1 or Option #2 because they only find the Burial facts that need to be fixed, and expect you to do the fix manually in the RM user interface.
Here is option #1. Just run it to get a list of Burial facts that need to be fixed, and work the list manually.
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
ORDER BY B_DateType DESC, B_MM, B_DD
Here is option #2. Just run it to get a list of Burial facts that need to be fixed, and work the list manually. The difference is that I have done a GROUP BY. With a GROUP BY, there is only one item on the screen, or only a few. Work what is on the screen and do a refresh. New unworked items will appear until you have done them all. There is a counter of how many are left. I find this easier to work with than option #1, but that's just me.
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD,
COUNT(*)
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
GROUP BY B_DateType, B_MM, B_DD
ORDER BY B_DateType DESC, B_MM, B_DD
Option #3 is automatic. You just run it, and you are done. But I don't like it because it makes no attempt to set the sort date for the burial fact to be a sort date that keeps the burial fact in the correct order. To be honest, I suspect it doesn't matter because it will leave the existing sort date for the Burial fact unchanged which is probably ok. However, I'm discovering that my Burial facts that are year only are accompanied by a Death fact that is year only, and that they are in the correct order only by accident. If I were doing data entry on year only Deaths and Burials today, I would leave the Burial Date blank and set sort dates to put the Death and Burial facts into the correct order. But that is a much more complicated script. You will notice that my scripts have all picked up but the Death Date and a Burial Date on the theory that it would make a future script to clean up the year only burial dates while setting sort dates correctly much easier. But I have not yet tried to write that logic. In the meantime, the following should work as option #3, but I have not yet tested it. I simply used the option #1 query as a subquery, except I had to double nest it to be able to pull out just the EventID column. The original Option #1 query had to have more than one column. This double nesting was the easiest "quick and dirty" way I could think of to reduce the number of columns to 1 as required.
UPDATE EventTable
SET Date = '.' -- blank burial date
WHERE EventID IN
(
SELECT A.EventID
FROM
(
SELECT E_Burial.OwnerID, E_Burial.EventID,
E_Burial.Date AS Bu_Date,
E_Death.Date AS De_Date,
SUBSTR(E_Burial.Date,2,1) AS B_DateType,
SUBSTR(E_Burial.Date,8,2) AS B_MM,
SUBSTR(E_Burial.Date,10,2) AS B_DD
FROM
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Burial')
) AS E_Burial
LEFT JOIN
(
SELECT *
FROM EventTable
WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE 'Death')
) AS E_Death ON E_Death.OwnerID = E_Burial.OwnerID
WHERE B_MM LIKE '00' OR B_DD LIKE '00'
ORDER BY B_DateType DESC, B_MM, B_DD
) AS A
)
Note: There was a bug in my original posting. I have now edited the posting to fix the bug. Namely, the definition of B_DD is SUBSTR(E_Burial.Date,10,2) AS B_DD rather than SUBSTR(E_Burial.Date,8,2) AS B_DD. The bug does not prevent any of the three scripts from running, and probably would not even prevent it from producing the correct results. But please use the corrected version rather than the original version in any case.
Correction to my correction: the fixed code is correct, but my comment about the new code not mattering was not correct. When I ran the fixed code in my database, it identified additional burials that needed my attention. Namely, it identified burials with a year and month and without a day. I'm really surprised. Those additional burials that need my attention apparently came into my database decades ago via GEDCOM import. I never entered burial dates that way myself.
Jerry, as usual, you are so thorough with your scripts and testing. Thank you for your solution. I will try it out soon and let you know how it goes. Jaime
It worked PERFECTLY! Thank you so very much 🙂
Jerry - I just discovered a problem .... the burial dates with just a year or just a month/year are eliminated, however many of them also had the same info in the sort date, which is causing RM to sort those burials before deaths 🙁
Is there a way to clear out burial sort dates that only contain a year or month/year????? (or clear out all burial sort dates if RM would repopulate it when there is an actual burial date??????)
It would be total trivial to clear all burial sort dates. RM would repopulate automatically as you filled in actual burial dates.
It would be slightly harder but not totally hard to clear out only burial sort dates of the form year or month year. I can probably get to it over the weekend.
I am good with clearing all burial sort dates, as long as the burials that already have an actual day/month/year would repopulate the sort date accordingly, It usually doesn't matter because RM puts them in order of Death then Burial (right????) if there are no dates, but some people have posthumous events, like probate (and I even have a few posthumous land grants/patents) which usually/should be after burial. I would say all these posthumous events would have a specific date, but some may have just a month and year. Am I getting loopy over this problem and not thinking it through adequately?
You always have such sound advice and we seem to view things pretty much the same way, so .... think it through a bit and I'll be happy with whatever route you think is best! Or both methods and I could see which works best or works at all with posthumous events. OK .... now I KNOW I'm getting loopy 😉
I guess this is matter of preference - IF the date when people simply use YEAR for burial -- its too incomplete (IMHO) I'd rather leave the DATE blank if not known complete date. On sort date -- I usually add a sort date when date is unknown or if null. I prefer the burial to (Closely) follow the death but before most other things (null sort date on burial usually will cause it to drop bottom of list). Nothing wrong per say -- but it goes to preference .
This script should do what you wish - turn off year only sort dates or year/month only sort dates while leaving full year/month/date sort dates untouched. Let me know if you have any trouble.
Technically, I think it's a good use case of using a CTE.
Uploaded files:I had no problem with your script, Jerry. It does what I want.
THANK YOU!!!!