This is a query I wrote just for myself and I didn’t feel as if it were of sufficiently general interest to post here. Also, it is not as polished as I like queries to be that I share publicly. But a request came up on the RootsMagic forums, so here it is. The query adds a Parent fact to everyone in the database who is not a dummy person, who has parents, and who has a birth fact with a valid date. The dummy person definition is for my own database, where my dummy people have an asterisk somewhere in their name. The birth fact with a valid date is required because the Parent fact needs a valid date, and in my database at least it needs to be placed immediately after the Birth fact. The name of the fact is singular “Parent” but it supports both parents. As written, the parent information is placed two places: 1) in the Description field for the Parent fact, and 2) the Parent fact is shared with both parents.
My sentence template for the Parent fact itself is setup as follows:
I use point form sentences with the sentence for each fact starting on a new line. The new line is a part of the template. Most RM users would need to set up the sentence template differently to meet their own needs. The [Birth_of_Child] variable looks funny, but that’s the role I share with the parents and it is what gets the parents listed in the sentence. Using the [Desc] variable would produce the same sentence, but using the [Birth_of_Child] variable causes the parents to be listed in the index for this page and the [Desc] variable does not. I mostly use Descendant Narrative reports. For the descendants, the parents appear in the report anyway, a generation earlier. The [Parent] variable makes the parents explicit. For the spouses of the descendant, their parents already appear by default in what I call the spouse sentence. So the [Parent] variable for the spouses represents a duplication. The spouse sentence is not under control of source templates (a major failing of RM), so I solve the problem by running a Notepad++ script on the RTF file to remove the parents from the spouse sentence before printing.
My sentence template for the [Birth_of_Child] role is as follows.
<b>Birth of Child:</b> <[Date:plain]><, [Person:Full]><, parent age: [ThisPerson:age:plain]>.
This has the salutatory effect of including the birth of each child on the timeline of each parent in a narrative report. Upon further review, I think the sentence would look better using a gender switch to say mother’s age or father’s age as appropriate. Gender switches work on the [Person] variable. I can’t remember if they work on the [ThisPerson] variable or not.
And here follows the script, warts and all
-- This script writ large creates a Parent -- fact for every person in the RM database -- who has parents. It loads the parents' -- names into the Description field for the -- Parent fact. It also shares the Parent -- fact with the parents themselves where it -- shows up as a Birth of Child event. -- This script was not developed with the intent -- of being generally distributed. Hence, it has -- not been refined and tested the way I would do -- for general distribution of a script. Please -- use it and adapt it on an "as is" basis or for -- ideas alone. -- I can't remember for sure, but sometimes scripts -- that insert this much data into into an RM data -- base can create indexing errors. If so, run the -- RM database tool in order immediately after running -- this script. -- Jerry Bryan 7/31/2018 -- Create a view of the EventTable -- containing only Parents events. -- This view will be used to prevent -- creating a Parents event for -- any individuals who already have -- a Parents event. The main data needed -- is the PersonID of the individuals -- who already have a Parents event. -- The PersonID manifests itself in -- the EventTable as OwnerId. DROP VIEW IF EXISTS ParentEventView; CREATE TEMP VIEW ParentEventView AS SELECT E.* FROM EventTable AS E JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType WHERE FT.Name LIKE('Parents'); -- Create a view of the EventTable -- containing only Birth events. -- These events provides an initial list -- of candidates of people to receive -- a Parents event. Also, many of the -- fields for newly created Parents -- events will be replicated from the -- corresponding Birth event. DROP VIEW IF EXISTS BirthEventView; CREATE TEMP VIEW BirthEventView AS SELECT Birth.* FROM EventTable AS Birth JOIN FactTypeTable AS FT ON FT.FactTypeID = Birth.EventType WHERE FT.Name LIKE('Birth'); -- Create a view of Birth events for people -- that don't have a Parent event. This is the -- first filtering on the list of candidates -- of people to receive a Parents event. DROP VIEW IF EXISTS BirthWithoutParentsEventView; CREATE TEMP VIEW BirthWithoutParentsEventView AS SELECT Birth.* FROM BirthEventView AS Birth LEFT JOIN ParentEventView AS Parent ON Parent.Ownerid = Birth.OwneriD WHERE Parent.OwnerID IS NULL; -- Create a view of the NameTable -- containing only primary names. -- This view will be a source of -- the names needed for the newly -- created Parents events and will -- prevent any names other than the -- primary ames from being loaded -- into -- Parents events. This -- view also prevents Parents events -- from being created for any dummy -- people, designated with an asterisk -- in the name. DROP VIEW IF EXISTS NameView; CREATE TEMP VIEW NameView AS SELECT N.* FROM NameTable AS N WHERE N.IsPrimary = 1 AND N.Surname NOT LIKE('%*%') AND N.Given NOT LIKE('%*%'); -- This view performs most of the -- main processing for this project. -- It determines which people actually -- do have parents. Actually having -- parents is based on being in the -- ChildTable and has nothing to do -- with whether a Parents event exists -- or not. This list of people with parents -- is then matched against people who are not -- dummy people, who do have birth -- events, and who don't already have -- Parents events. This view also -- develops the data that will need to -- be stored in the newly created -- Parents events. DROP VIEW IF EXISTS ChildParentsView; CREATE TEMP VIEW ChildParentsView AS SELECT Child.ChildID, Child.Given || ' ' || Child.Surname AS ChildName, Father.FatherID, Father.Given || ' ' || Father.Surname AS FatherName, Mother.MotherID, Mother.Given || ' ' || Mother.Surname AS MotherName, CASE WHEN Father.FatherID = 0 THEN Mother.Given || ' ' || Mother.Surname WHEN Mother.MotherID = 0 THEN Father.Given || ' ' || Father.Surname ELSE Father.Given || ' ' || Father.Surname || ' and ' || Mother.Given || ' ' || Mother.Surname END CombinedNames, B.Date, B.SortDate, B.EditDate FROM ( SELECT C.RecID, C.ChildID, N.Given, N.Surname FROM ChildTable AS C JOIN NameView AS N ON N.Ownerid = C.ChildID ) AS Child JOIN ( SELECT C.RecID, FM.FatherID, N.Given, N.Surname FROM ChildTable AS C JOIN FamilyTable AS FM ON C.FamilyID = FM.FamilyID LEFT JOIN NameView AS N ON N.OwnerID = FM.FatherID ) AS Father ON Father.RecID = Child.RecID JOIN ( SELECT C.RecID, FM.MotherID, N.Given, N.Surname FROM ChildTable AS C JOIN FamilyTable AS FM ON C.FamilyID = FM.FamilyID LEFT JOIN NameView AS N ON N.OwnerID = FM.MotherID ) AS Mother ON Mother.RecID = Father.RecID JOIN BirthWithoutParentsEventView AS B ON B.OwnerID = Child.ChildID; -- The data from the ChildParentsView needs to be used -- twice, once to load Parents events into the EventTable -- and again to load roles into the WitnessTable. The -- Parents events have to be loaded into the EventsTable -- first, and one Insert statment in SQLite cannot load -- data into two tables. However, loading Parents Events -- into the EventTable will cause the ChildParentsView -- not to produce the correct results the second time it -- is used. Therefore, the results from applying the -- ChildParentsView will be saved into a temporary table -- called ChildParentsTable. As a table instead of a view, -- the data in ChildParentsTable can be used two different -- times and remain the same data both times it is used. DROP TABLE IF EXISTS ChildParentsTable; CREATE TEMP TABLE ChildParentsTable (ChildID,ChildName,FatherID,FatherName,MotherID,Mothername,CombinedNames,Date,SortDate,EditDate); INSERT INTO ChildParentsTable (ChildID,ChildName,FatherID,FatherName,MotherID,Mothername,CombinedNames,Date,SortDate,EditDate) SELECT CP.* FROM ChildParentsView AS CP; -- Load new Parents facts into the EventTable. INSERT OR ROLLBACK INTO EventTable SELECT NULL AS EventID ,(SELECT FT.FactTypeID FROM FactTypeTable AS FT WHERE FT.Name LIKE('Parents') ) AS EventType ,0 AS OwnerType ,ChildID AS OwnerID ,0 AS FamilyID ,0 AS PlaceID ,0 AS SiteID ,Date AS Date ,SortDate AS SortDate ,0 AS IsPrimary ,0 AS IsPrivate ,0 AS Proof ,0 AS STATUS ,EditDate AS EditDate ,CAST('' AS TEXT) AS Sentence ,CAST(CombinedNames AS TEXT) AS Details ,CAST('' AS TEXT) AS Note FROM ChildParentsTable; -- =========================================================================================================== -- The following is adapted from Tom Holden to rank same date sort dates. -- -- There are a number of changes by Jerry Bryan that are specific to his -- use case. -- -- * The list of fact types which are supported is greatly increased. -- * Sort dates including ranked sort dates (date-n) are ranked and even re-ranked -- whether or not they match the date from from the fact itself, provided only -- that the sort dates in question match each other. -- . This allows same "ABT" dates to be ranked. -- . This allows same "year only" dates to be ranked if the sort date is -- 1 July of the year. -- . This allows same "year and month only" dates to be ranked if the -- . sort date is the 15th of the month. -- -- =========================================================================================================== /* SortDateSameDayOrderCustom.sql 2011-12-20 ve3meo Alters SortDates of any set of Fact types to a natural order when any pair or more occur on the same date. Could be extended to order other facts also. SortDates are effectively assigned (by arithmetical offsets) an absolute suffix -1, -2, ... related to the rank of the FactType. Affects only those events whose SortDates correspond to the Fact Date, as computed by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all Event dates are handled, e.g. "Bef 1960". */ DROP TABLE IF EXISTS TmpFactOrder ; CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder (Rank INTEGER PRIMARY KEY, FactName TEXT) ; /* list of Fact Names, standard and custom, to be sorted, in rank order. Revise the list to suit your needs */ INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Parents'); -- added by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Birth Certificate'); -- added by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Christen'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Baptism'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Obituary'); -- moved up by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Death Certificate'); -- added by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Funeral'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Cremation'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial'); INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial Inscription'); -- Added by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Burial GPS'); -- Added by JB INSERT INTO TmpFactOrder (Rank, FactName) VALUES (NULL, 'Memorial'); /* revise SortDates */ UPDATE EventTable SET SortDate = SortDate -6692012023 -- this offset goes to Date-1 if the event is a ranked event *( ( SELECT Rank FROM TmpFactOrder, FactTypeTable WHERE FactName LIKE Name AND FactTypeID = EventType )>0 ) +1048576 -- this offset adds steps of 1 to Date-1 multiplied by (rank-1) *( ( SELECT Rank FROM TmpFactOrder, FactTypeTable WHERE FactName LIKE Name AND FactTypeID = EventType )-1 ) -- maps the FactType to its order WHERE EventID IN (SELECT EventID FROM EventTable INNER JOIN (SELECT -- matching dates SortDate, OwnerID, COUNT()-1 AS Matches FROM EventTable INNER JOIN FactTypeTable ON EventType = FactTypeID WHERE EventTable.OwnerType = 0 AND Name IN (SELECT FactName FROM TmpFactOrder) /* AND -- commented out by JB to handle sort dates not matching fact date when sort dates are equal to each other. SortDate = -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it) (CASE WHEN DATE LIKE '.%' THEN 1 ELSE Substr(DATE,3,5) END +10000 )*562949953421312 + Substr(DATE,8,2)*35184372088832 + Substr(DATE,10,2)*549755813888 + 17178820620 */ GROUP BY SortDate, OwnerID, EventTable.OwnerType ) USING (OwnerID, SortDate) INNER JOIN FactTypeTable ON EventType = FactTypeID WHERE Matches AND EventTable.OwnerType = 0 AND Name IN (SELECT FactName FROM TmpFactOrder) ) ; -- We now add Parent roles to each Parents event. -- It is most convenient to add the Parent role -- for the father in one INSERT and to add the -- Parent role for the mother as a separate insert. -- So we first create a view that joins the -- Parent events with the ChildTable and FamilyTable -- to get a list of Parent events and their -- respective fathers and mothers. DROP VIEW IF EXISTS ParentRoleView; CREATE TEMP VIEW ParentRoleView AS SELECT PEV.*, FM.FatherID, FM.MotherID FROM ParentEventView AS PEV JOIN ChildTable AS CT ON CT.ChildID = PEV.OwnerID JOIN FamilyTable AS FM ON CT.FamilyID = FM.FamilyID; -- Load Parents roles for fathers into the WitnessTable INSERT OR ROLLBACK INTO WitnessTable SELECT NULL AS WitnessID ,PE.EventID AS EventID ,Parents.FatherID AS PersonID ,0 AS WitnessOrder ,(SELECT R.RoleID FROM RoleTable AS R WHERE R.RoleName LIKE ('Parent') ) AS Role ,CAST('' AS TEXT) AS Sentence ,CAST('' AS TEXT) AS Note ,CAST('' AS TEXT) AS Given ,CAST('' AS TEXT) AS Surname ,CAST('' AS TEXT) AS Prefix ,CAST('' AS TEXT) AS Suffix FROM ChildParentsTable AS Parents JOIN ParentEventView AS PE ON PE.OwnerID = Parents.ChildID AND PE.Details = Parents.CombinedNames; -- Load Parents roles for mothers into the WitnessTable INSERT OR ROLLBACK INTO WitnessTable SELECT NULL AS WitnessID ,PE.EventID AS EventID ,Parents.MotherID AS PersonID ,0 AS WitnessOrder ,(SELECT R.RoleID FROM RoleTable AS R WHERE R.RoleName LIKE ('Parent') ) AS Role ,CAST('' AS TEXT) AS Sentence ,CAST('' AS TEXT) AS Note ,CAST('' AS TEXT) AS Given ,CAST('' AS TEXT) AS Surname ,CAST('' AS TEXT) AS Prefix ,CAST('' AS TEXT) AS Suffix FROM ChildParentsTable AS Parents JOIN ParentEventView AS PE ON PE.OwnerID = Parents.ChildID AND PE.Details = Parents.CombinedNames; ;