Reports+-+Concordances+for+Indexes

toc =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 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.

Requires a SQLite manager with a RMNOCASE extension. code format="sql" -- 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

code =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.

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.