Group – Persons with Text Dates #date #namedgroup

  • 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

4 Replies to “Group – Persons with Text Dates #date #namedgroup

      • 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.