Adding Dates to Common Events

#1 · 2022-10-08, 9:24 am

Hi I originally posted this in RootsMagic Community but it was suggested I post here.

Using RM7

Is there an way to automatically add a date to a common event e.g. a census, so that I don’t need to keep entering it in to the fact, when the fact is added to a person. I have created custom facts for each of the UK censuses currently the custom fact name is, for example, 1881 Census UK with the date in the abbreviation, for example, 1881 04 03 Census. The custom facts can be easily changed.

I have a limited knowledge of sql but help with the query would be appreciated.

Thanks

#2 · 2022-10-08, 9:49 am

Backup your database in case you have to fall back. This query should work for your specific case of the 1881 Census. A more general query could apply to any fact type whose name or abbrev began with the format yyyy mm dd.

UPDATE EventTable SET Date = 'D.+18810403+00000000..'
WHERE EventTypeID =
(
SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE '1881 04 03 Census'
)
;

Kenneth and Nampara have reacted to this post.

#3 · 2022-10-08, 12:23 pm

The request was for an "automatic" way. That is not automatic. The query is going to need to be ran on some sort of regular basis to update any facts entered since the last run. Each time it is ran, one is going to need to make sure they backup, run the query, and they are going to have to do it for every census that they use. A lot less wasted time just typing in a date.

Nampara has reacted to this post.

#4 · 2022-10-08, 2:34 pm

Quote from Kenneth on 2022-10-08, 12:23 pm
That is not automatic.

True, and not claimed to be. And its efficiency for the purpose is dependent on the use case. Obviously low compared to manually entering the date if run for each newly added Census event. Obviously high if there are many such existing events needing the date. I'm sure @nampara will be able to draw conclusions for her use case.

Nampara has reacted to this post.

#5 · 2022-10-08, 6:58 pm

Thank you to everyone who has contributed comments to my query. I will consider all comments before making a decision as to using the sql query or just entering the date manually.

#6 · 2022-10-08, 11:06 pm

Quote from thejerrybryan on 2022-10-08, 11:06 pm
The subject has been well discussed but let me add a little bit anyway.
Any SQLite script will need to set both the fact date and the fact sort date. The default fact sort date when the fact date is blank is a large date. Changing only the fact date from SQLite will not also change the fact sort date unless the script does it. As a result, your custom census facts will be in the wrong order unless the SQLite script sets both the fact date and the fact sort date.
I make heavy use of special fact types for census years, which is the issue at hand. So I have many years of experience with this issue. I realize that it may sound silly for me to have to enter a date of 1850 for my 1850 Census fact, etc. But I don't like to leave the Edit Person screen without being able to see that the facts are correct. And I usually even run a quick one generation descendant narrative report for the current person after exiting the Edit Person screen to be sure that the facts are all correct. Running an after the fact SQLite script to enter the census dates loses all this ability to verify that the data is correct.
Finally, sort dates are really, really important sometimes. For example, for a person who is born in a census year, I often have to adjust the birth sort date and the census sort date for that year to be sure the the birth fact is before the census fact. I can't think of a good SQLite way to automate a solution to this issue.
I certainly support anybody's decision on how they wish to handle dates for special census facts for each census year. But for me, entering the date for custom census facts manually in the Edit Person screen is a much lesser evil than entering both the date and sort date for the custom census facts using SQLite.
By the way, in Tom's example, I seem to need
SET Date = 'D.+18810403..+00000000..' rather than
SET Date = 'D.+18810403+00000000..'. Also, be aware that fact sort dates are stored in a very different format than are fact dates.

The subject has been well discussed but let me add a little bit anyway.

Any SQLite script will need to set both the fact date and the fact sort date. The default fact sort date when the fact date is blank is a large date. Changing only the fact date from SQLite will not also change the fact sort date unless the script does it. As a result, your custom census facts will be in the wrong order unless the SQLite script sets both the fact date and the fact sort date.

I make heavy use of special fact types for census years, which is the issue at hand. So I have many years of experience with this issue. I realize that it may sound silly for me to have to enter a date of 1850 for my 1850 Census fact, etc. But I don't like to leave the Edit Person screen without being able to see that the facts are correct. And I usually even run a quick one generation descendant narrative report for the current person after exiting the Edit Person screen to be sure that the facts are all correct. Running an after the fact SQLite script to enter the census dates loses all this ability to verify that the data is correct.

Finally, sort dates are really, really important sometimes. For example, for a person who is born in a census year, I often have to adjust the birth sort date and the census sort date for that year to be sure the the birth fact is before the census fact. I can't think of a good SQLite way to automate a solution to this issue.

I certainly support anybody's decision on how they wish to handle dates for special census facts for each census year. But for me, entering the date for custom census facts manually in the Edit Person screen is a much lesser evil than entering both the date and sort date for the custom census facts using SQLite.

By the way, in Tom's example, I seem to need
SET Date = 'D.+18810403..+00000000..' rather than
SET Date = 'D.+18810403+00000000..'. Also, be aware that fact sort dates are stored in a very different format than are fact dates.

Tom Holden and Nampara have reacted to this post.

#7 · 2022-10-09, 5:27 pm

I must apologise for having posted an untested query having drafted it on a Chromebook which cannot run it and relying on my (increasingly?) faulty memory. It is Thanksgiving weekend here so my computer time is spottier than usual. Had I done it on the PC, my errors and oversight would have jumped out. There was also a misnamed field that would have prevented it from running. Here is the corrected and tested query:

UPDATE EventTable
SET
Date = 'D.+18810403..+00000000..'
,SortDate = 6688550800533225484
WHERE EventType =
(
SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE '1881 04 03 Census'
)
;

@thejerrybryan, thanks for pointing out 2 of the three problems with the draft.

Nampara has reacted to this post.

#8 · 2022-10-10, 3:31 am

Thank you again for your help with this.