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 /* 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 );