Unlike the names of persons, RootsMagic 6 does not yet have any mechanism for automatically revolving through Place names in reports, i.e., using the full place name only if it has not been previously used for a person while taking advantage of the first or lowest level place name or the abbreviated place name for subsequent instances in the person’s narrative. It is up to the user to modify default sentences for Facts and Roles and to create custom sentences for events in order to take advantage of the :first and :short options for the Place field and thus reduce needless repetition of higher level place values such as county, state/province/shire, country. And exacerbating repetition of country is an effect of RootsMagic’s own County Checker and Gazetteer which want to populate the Place field with country.

To mitigate such repetition with a token effort, this script automatically edits the default sentence templates to use [Place:first] instead of [Place] and then goes on to generate custom sentence templates or modify existing ones for all first instances of a place in each person’s chronology to use [Place] instead of [Place:first]. That leaves all subsequent instances of the place for that person to be the default sentence having [Place:first] or to be so modified, which it also does.

For a person who was born, was schooled, worshipped, worked, married, resided, died and was buried in the same Place, his narrative will have but one instance of the full Place name (city, county, state, country) for his birth; all other instances will be just the city. More or less! A “less” is where the person appears as a child below his parent’s narratives as these phrases are not subject to any accessible template.

Screenshot from MS Word comparing the revised narrative to the original.

In the before/after example above, the Birth event was detected as the first use of Glasgow so a custom sentence for the event was generated that was the same as the original default sentence with the full Place. All the subsequent events in Glasgow, including the shared or witnessed census events, used the new default sentences having [Place:first] so “, Lanarkshire, Scotland, United Kingdom” appears deleted (red strikethrough). The one Quebec City event is a first and only so it received a custom sentence with full Place name. Then North Bay appears three times, the first with a full Place custom sentence, the rest using the first place value per the revised default sentence template.

This utility is not the be all and end all for narratives but it should be useful as a quick streamliner and a basis on which further customisation of sentence templates can deliver further improvement. It does not address all the combinations of options that can be added to [Place] because there could be many. SQLite does not have a regular expression search and replace function which would be necessary to deal with them efficiently and comprehensively.

Places-FirstNameExploit.sql

-- Places-FirstNameExploit.sql
/*
2013-03-29 Tom Holden ve3meo
rev 2013-03-30 added [Place:plain] update to [Place:plain:first] for defaults only.
    needs regexp search & replace to deal with all combinations of Place modifiers
 
Exploits the Place:first option for Place names in narratives by
setting default sentences to Place:first and customising to Place 
only for the first event for a person in any place. 
 
This makes the narrative less wordy and repetitious of the higher levels 
in a Place name. However, it has no effect on the phrases used in the 
Children list at the bottom of a person's narrative which continue 
to use the full place name.
*/
-- Set default Fact sentences to use the first Place name.
UPDATE FactTypeTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]');
UPDATE FactTypeTable
SET Sentence = REPLACE(Sentence, '[Place:plain]', '[Place:plain:first]');
 
-- set default role sentences to use the first Place name
UPDATE RoleTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]');
UPDATE RoleTable
SET Sentence = REPLACE(Sentence, '[Place:plain]', '[Place:plain:first]');
 
-- create table of first event in a place for a person including shared events
DROP TABLE
IF EXISTS xFirstPlaceEvents;
 
CREATE TEMP TABLE
IF NOT EXISTS xFirstPlaceEvents AS
    SELECT *
    FROM (
        -- INDIV events
        SELECT EventID
            ,0 AS isSharer
            ,PlaceID
            ,OwnerID
            ,SortDate
        FROM EventTable
        WHERE OwnerType = 0
            AND PlaceID > 0
 
        UNION
 
        -- Husband events
        SELECT EventID
            ,0 AS isSharer
            ,PlaceID
            ,FatherID AS OwnerID
            ,SortDate
        FROM EventTable
        INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
            AND OwnerType = 1
            AND PlaceID > 0
 
        UNION
 
        -- wife events
        SELECT EventID
            ,0 AS isSharer
            ,PlaceID
            ,MotherID AS OwnerID
            ,SortDate
        FROM EventTable
        INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
            AND OwnerType = 1
            AND PlaceID > 0
 
        UNION
 
        -- shared events
        SELECT WitnessID
            ,1 AS isSharer
            ,EventTable.PlaceID AS PlaceID
            ,WitnessTable.PersonID AS OwnerID
            ,EventTable.SortDate AS SortDate
        FROM WitnessTable NATURAL
        INNER JOIN EventTable
        WHERE EventTable.PlaceID > 0
        ORDER BY OwnerID
            ,SortDate DESC -- so next GROUP BY will pick up the smallest SortDate or first event in the group
        )
    GROUP BY OwnerID
        ,PlaceID
    ORDER BY EventID; -- so the IN() expression in the following queries will see an ordered list
 
-- set all first events for a person to use the default sentence customised with the full Place
-- except those already with custom sentences 
UPDATE EventTable
SET Sentence = (
        SELECT REPLACE(FactTypeTable.Sentence, '[Place:first]', '[Place]')
        FROM EventTable Events
        INNER JOIN FactTypeTable ON Events.EventType = FactTypeID
        WHERE EventTable.EventID = Events.EventID
        )
WHERE EventID IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 0
        )
    AND EventTable.Sentence LIKE '' --change this to OR with a match to the default
    OR EventTable.Sentence LIKE (
        SELECT REPLACE(FactTypeTable.Sentence, '[Place:first]', '[Place]')
        FROM EventTable Events
        INNER JOIN FactTypeTable ON Events.EventType = FactTypeID
        WHERE EventTable.EventID = Events.EventID
        );
 
-- set all other custom sentences for first events to use the full name
UPDATE EventTable
SET Sentence = REPLACE(Sentence, '[Place:first]', '[Place]')
WHERE EventID IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 0
        );
 
-- set all other custom sentences for non-first events to use the first name
UPDATE EventTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]')
WHERE EventID NOT IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 0
        );
 
--DO The Same steps for shared events
-- set all first witness for a person to use the default sentence customised with the full Place
-- except those already with custom sentences 
UPDATE WitnessTable
SET Sentence = (
        SELECT REPLACE(RoleTable.Sentence, '[Place:first]', '[Place]')
        FROM WitnessTable Witness
        INNER JOIN RoleTable ON Witness.ROLE = RoleTable.RoleID
        WHERE WitnessTable.WitnessID = Witness.WitnessID
        )
WHERE WitnessID IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 1
        )
    AND WitnessTable.Sentence LIKE '' --change this to OR with a match to the default
    OR WitnessTable.Sentence LIKE (
        SELECT REPLACE(RoleTable.Sentence, '[Place:first]', '[Place]')
        FROM WitnessTable Witness
        INNER JOIN RoleTable ON Witness.ROLE = RoleTable.RoleID
        WHERE WitnessTable.WitnessID = Witness.WitnessID
        );
 
-- set all other custom sentences for first witness events to use the full name
UPDATE WitnessTable
SET Sentence = REPLACE(Sentence, '[Place:first]', '[Place]')
WHERE WitnessID IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 1
        );
 
-- set all other custom sentences for non-first witness events to use the first name
UPDATE WitnessTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]')
WHERE WitnessID NOT IN (
        -- EventIDs of first events having a PlaceID for all persons
        SELECT EventID
        FROM xFirstPlaceEvents
        WHERE isSharer = 1
        );