- Creates a Named Group in the open database with the name “SQL: Text Dates” if one does not already exist
- Deletes all members from that named group
- Populates that named group with persons having an event date that is backgrounded in yellow on the Edit Person screen, i.e., an invalid date format interpreted by RootsMagic as Text
To refresh the named group’s membership list to reflect corrections you have made to the format of event dates, rerun the query to regenerate the RootsMagic group table and then, in RootsMagic, select a different group and then re-select the “SQL: Text Dates” group. You should see that names have disappeared from the group, provided you have corrected all the text dates for the persons you worked on.
Voila! A Dynamic Group! Just a few clicks more than if RootsMagic had it built in.
Group-PersonsWithTextDates-RM10 2024-10-08 for #RM10 & probably suitable for RM8 and RM9
Group-PersonsWithTextDates.sql for all versions RM4-RM7
2013-09-01 superseded PersonsWithTextDates.sql which was flawed and incomplete.
rev 2018-07-07 bug fixed when no other existing group
Can you please provide a ver 10? Thanks
Done. That’s a pretty simple one because the LabelTable became the TagTable along with some changes in field names.
Thanks ! Could I suggest an enhancement. If a date is null for an event, could it be excluded?
Sorry, I forgot to respond to this. One approach would be to replace every instance of the constraint
WHERE Date LIKE ‘T%’ AND…
with this revised cosntraint
WHERE Date LIKE ‘T_%’ AND…
That requires there to be at least one character in the date field of a not valid Date value. However, that first and only character could be a space character and would look like a null date to you.
A more rigorous constraint using regular expression pattern matching such as this would cover that exception
WHERE Date REGEXP ‘^T[^\s]+’ AND…
The pattern ^T requires the first character to be “T”.
Then [^\s]+ requires that the first subsequent character and any quantity subsequent not be whitespace characters. I thought that it would require that all subsequent ones be not whitespace but I may misunderstand the rules or SQLiteSpy has misinterpreted them.
That result troubled me and I found on further investigation that this revised constraint is required to cover what I believe to be all the empty, quasi-empty Text dates you want to ignore:
WHERE Date LIKE ‘T%’ AND Date NOT REGEXP ‘^T[\s]*$’ AND…
The LIKE constraint includes all TEXT type dates.
The NOT REGEXP excludes the empty or quasi-empty TEXT type dates. Its pattern matches strings with T at the beginning followed by 0 or more whitespace characters through to the end of the string.