Identifying Events with Individual Sentence Template Customization

I recently went through an exercise of removing all sentence customization for specific events in my RM database. The purpose of this change was to allow my Point Form sentence templates from the Fact Type Table to be used in all cases.

It is not possible in the RM user interface to search for specific events that have their own customized sentence templates. To that end, I developed the following query. This is pretty specific to my own research, but it did occur to me that there might be other reasons that someone might wish to find all events with individualized sentence customization.

I removed each sentence customization in RM by hand and I used this query only to find the customizations that needed to be removed. I could have done the removals with a query, but as I was removing the sentence customizations I was also updating the Fact note and in some cases the Fact description. So it was essentially impossible to do a complete automation of the the project.

Jerry

-- Finds events with individually customized sentence templates.
-- Sentence templates that are customized in the Fact Type table are not
-- identifed, only those that are customized for a particular event for a
-- particular person in the Edit Person sceen.
--
-- This query is for standard facts only, not for shared facts.
--
-- For a query such as this, most typically I would create
-- three queries and form their UNION. The reason for the
-- three queries is to find the PersonID for individual facts,
-- the FatherID for family facts, and the MotherID for family
-- facts. But for this query, I changed the logic a bit and
-- created a single query with three result columns. Some of
-- the column values will therefore be null, but each row
-- of the query includes at least one person of interest.
--
-- There is a subtle but important point in this query. The
-- query contains a sub-query within a couple of CASE statements.
-- These sub-queries must test the OwnerID from the EventTable
-- that's outside the sub-query rather than the OwnerID from the
-- EventTable that's inside the sub-query. I'm not quite sure why
-- this is so, but the query does not work correctly otherwise.
 
SELECT CASE E.OwnerType
       WHEN 0 THEN E.OwnerID      -- individual fact, get the PersonID
       END PersonID,
 
       CASE E.OwnerType   -- family fact, get the FatherID
       WHEN 1 THEN (SELECT F.FatherID
                    FROM FamilyTable AS F
                           JOIN
                         EventTable ON E.OwnerID = F.FamilyID)
       END FatherID,
 
       CASE E.OwnerType
       WHEN 1 THEN (SELECT F.MotherID   -- family fact, get the MotherID
                    FROM FamilyTable AS F
                           JOIN
                         EventTable ON E.OwnerID = F.FamilyID)
       END MotherID,
 
       FT.Name AS Fact,
       E.Sentence AS Sentence
FROM EventTable AS E
        JOIN
      FactTypeTable AS FT ON E.EventType = FT.FactTypeID
WHERE LENGTH(E.Sentence) > 0                                   -- test to see if sentence template exists
ORDER BY FatherID, MotherID, PersonID, E.DATE;