Marking for Indexing Needed for Individual Summary and Custom Reports

RootsMagic 6 does not generate in reports an Index of Names or an Index of Places for the Individual Summary report, nor for custom reports. Yet one can readily generate a batch of these reports and it would be handy to have them indexed. Even when included in a Reports > Publisher ‘Book’, they are not covered by the auto-generated Indexes process. When the report is saved to RTF, one could manually mark names and places for indexing using Microsoft Word but the effort is laborious and has to be repeated the next time the report is generated.

Automarking from a Concordance Table in Microsoft Word

The good news is that MS Word has an Automark feature to expedite indexing. One simply creates a separate MS Word document containing a two column table. The left column contains the terms to be marked for indexing and the right column contains the value under which it is to be indexed, i.e., the Index entry. This is called a Concordance Table. With the report open in MS Word, you use References > Insert Index > Automark to select the Concordance file and mark for indexing all the matching terms in the document. Then, with cursor located where the Index is to be located, Insert Index again to generate the Index.

Thanks to Charlie Hoffpauir for opening my eyes to this capability in MS Word in a discussion on the RootsMagic-Users-L on publishing a report containing everyone in a database. More on Indexing and Automarking with a concordance table at How-To Geek, including a VB Script for cleaning out the index marks if you have to redo your concordance table for that document.

Problem Creating Concordance Table using RM Custom Reports

Using a RootsMagic custom report, it is possible to create a concordance table but there are issues. The Automark search is case-sensitive; an exact match is required. I like to have surnames in upper case in reports and that is achieved for standard RootsMagic reports by checkmarking the box “Display surnames in upper case” in Tools > File Options > General. However, surnames are not affected by that setting for custom reports (another piece of unfinished business…)! Moreover, to make sub-entries for a surname common to multiple persons, let alone upper case conversion, would require operating on the custom report in a spreadsheet.

Hence, SQLite to the rescue!

A Concordance Table SQLite Query and resulting Index

The SQLite query produced this concordance table. Copied from SQLiteSpy straight to a new MS Word document without modification.

Sample of an Index generated in MS Word by Automarking from a Concordance table generated by a SQLite query.

The Index shows no entry for Betsy ALEXANDER because that search term was not found in any of the reports; Individual Summaries were generated for a subset of the database while the Concordance table has every name, including Alternate Names in it.

A similar query could generate a concordance table of Place Names, albeit a necessarily more complicated one to handle name reversals and Place Details.

Names-ConcordanceCaps.sql Requires a SQLite manager with a RMNOCASE extension.

-- Names-ConcordanceCaps.sql
-- 2014-11-25 Tom Holden ve3meo
/*
Creates a temporary SQLite View laid out as a MS Word
Concordance Table of people's names to aid in the generation
of indexes for a collection of Individual Summary reports.
It is of the form:
SearchName              | IndexName
Annie Eliza ALEXANDER | ALEXANDER: Annie Eliza
B. F. ALEXANDER          | ALEXANDER: B. F.
Betsy ALEXANDER          | ALEXANDER: Betsy
 
The left column contains the case-sensitive search string;
the right column has the value to be outputted in the Index.
In this example, the colon will cause one ALEXANDER surname
to be printed with an indented line for each os the individuals.
 
The search surnames have been uppercased because a display
setting in RootsMagic File Options for the database causes
the standard reports to output upper case surnames; that is
what Word will search. If your reports have lower case surnames,
remove the UPPER() function from the SearchName expression.
 
Likewise, if you do not want the Index to have all-cap surnames,
remove the UPPER() function from the IndexName expression.
*/
DROP VIEW IF EXISTS NameConcordanceCaps
;
CREATE TEMP VIEW NameConcordanceCaps
AS
SELECT DISTINCT
  REPLACE
  (
   TRIM
   (
    Prefix || ' '
    || Given || ' '    || UPPER(Surname) || ' '    || Suffix
    )
    , '  ', ' '
   )
  AS SearchName
  ,
  REPLACE
  (
   TRIM
   (UPPER(CASE Surname WHEN '' THEN 'UNKNOWN' ELSE Surname END) || ': '    || Prefix || ' '    || Given || ' '    || Suffix
    )
    , '  ', ' '
   )
  AS IndexName
FROM NameTable
ORDER BY IndexName
;

A Concordance Table and Index that groups by First Name

This is a more advanced table that relies on the report having the person’s Record Number following their name so that it can include Birth Year and Death Year in the Index. It responds to a wish expressed by RootsMagic Forums member Paul1307 in the topic Sorting in Index that names be sorted by surname, first name, year(s), excluding middle names from the sort.

Snippet of the index for an 80 page batch of Individual Summary reports, created from the Concordance Table generated from a SQLite query.

This query creates a temporary View in SQLite that does the heavy lifting of parsing Given names into First Name and Middle Names and creates the Era string from the person’s Birth and Death years; it is named “NameGivensParsed”. The second View “NameConcordanceCapsFirstYrsMids” is the Concordance Table. Click on that View to see the resulting table, select all the results, copy and paste into a new blank MS Word document. Save the document to where you can readily find it for use with RM reports from this database.
Names-ConcordanceCapsFirstYrsMids.sql