Sources – Newspapers.com – Cleaner Footnotes and Simpler Bibliography

Issues

This project arose from a request Fix & Merge Hundreds of Newspapers.com Sources in the Forum. The poster is a heavy consumer of the Newspapers.com sources through RM’s TreeShare with Ancestry.com and had issues with:

  1. A long Source List in the application and repetitiously long report Bibliographies due to a different Master Source for each page of a newspaper.
  2. Repetitious listing of “Newspapers.com” in Source Names and in the Title in Bibliographies and in Footnotes. Her approach was to manually delete it in every Master Source but still had hundreds to do.
  3. Leading punctuation in the Footnotes and Bibliographies because the Author value is empty in sources from this Ancestry collection.
  4. “N.p.” and “n.d.” notations in Footnotes and Bibliographies when a value for Publisher, Publish Place or Publish Date is empty.

Solution

Because the sources were imported via TreeShare, they are Ancestry Record type, i.e., they are created using the built-in Ancestry Record Source Template. Built-in Source Templates are uneditable through the RM user interface but are defined in the same table that holds user-defined templates. Thus, the built-in templates can be modified by using SQLite to edit entries in the SourceTemplateTable. We can address Issues #3 and #4 by modifying the Footnote and Bibliography sentence templates in the Ancestry Record template. That will be of benefit also to citations having empty values from some other Ancestry Collections (see Ancestry TreeShare – Impact).

Issues #1 and #2 are more challenging because the values of the source and citation variables that appear in the Footnote and Bibliography sentences are stored in a XML data structure. To solve #1, we want to “lump” all citations of a given newspaper Title under one Master Source. That requires that the data that differentiates the Master Sources for a common newspaper must be deleted or transferred from the Master Source to the Citation Details. For example, the Page # must be extracted from the Source Name in the SourceTable and moved to the Detail ([Page] variable in XML) for each Citation of that Source in the CitationTable. There are more steps than that alone for each of that one newspaper’s multiple Master Sources and Citations.

Once all the data manipulations are complete, there will be multiple identical Master Sources for a given newspaper Title. RM’s AutoMerge Sources function can finish the job.

Before/After Screenshots

The database undergoing modification was from RM7, hence the screenshots are of RM7. However, the solution also works with RM8 and RM9.

Before

Example of one source in the original database. Note that the Source Name and the Title variable (italics) are identical and contain the unwanted “Newspapers.com”, the title of the newspaper, the publish date and the page number. The [Page] variable at the Citation level contains description of the item of interest and the publish date (repeated from the [Title] variable). All three sentences have unwanted leading punctuation and white space.
A Master Source for each page cited from a given newspaper. This example of ‘extreme splitting’ of sources is perfectly acceptable for some users while, for others, the long Source List and report Bibliographies are objectionable and ‘lumping’ to one Master Source per newspaper is preferred.

Transition

These Before/After shots of the Edit Source window show the operations needed to prepare Sources and Citations for lumping Sources by Newspaper Title and the resulting sentence previews from an improved Ancestry Record source template.

After

Now just one Arizona Republic in the Source List instead of many individual Page #’s. In some cases such as the Arizona Daily Star at the top of the list, RM’s Source AutoMerge leaves two Master Sources that look identical and it is necessary to Manual Merge the two to end with just one. Despite fields looking identical in the Source Editor, AutoMerge compares the full XML strings of each source and there’s no match if the order of otherwise identical fields is different.

Download Scripts

Procedure

  1. Backup your database in case you need to revert to it.
  2. Open your database with a SQLite manager having RMNOCASE – faking it in SQLiteSpy or RMNOCASE – faking it in SQLite Expert, command-line shell et al and supporting the REGEXP_REPLACE() function.
  3. Load and execute Sources-NewspapersCom-LumpClean.sql.
  4. If the Ancestry Record source template does not have ” – cleaned” appended to it, load and execute SourceTemplate-AncestryRecord-cleaned.sql.
  5. On returning to RM, run Rebuild Indexes in Database Tools.
  6. In RM, open the Source List and run AutoMerge.
  7. If you have two or so remaining sources for the same newspaper using the Ancestry Record template and you wish to have only one, use RM’s Manual Merge for Sources.
  8. Repeat after you have added more Newspapers.com sources via TreeShare.

Notes

  1. Should you have reason to revert the Ancestry Record source template to the format supplied by the application, load and execute in your SQLite manager SourceTemplate-AncestryRecord-Reset.sql, edited to find a RM database file of the same major version number to fetch the built-in format.
  2. Should you upgrade or drag’n’drop to another database, the “Ancestry Record – cleaned” template will revert to the built-in format. Run step #4 on the target database to restore it.
  3. The user reported that TreeShare does not report any change as a consequence of this procedure; it would seem to rely solely on the link to the Ancestry Record stored in the RM7 LinkAncestryTable (AncestryTable in RM8, RM9).
  4. The procedures should work also on RM8 and RM9.
  5. The main script is not what I would call ‘elegant’. It grew like Topsy as I explored the database and evolved the process through a sequence of building blocks. Someone cleverer than I with SQLite might well produce a better, faster version.

Convert RM8 Database to RM7 #rm8 #rm7

Issue

As of writing, still no Book Publisher feature in RM8 and along with other persistent issues, some users wish to return to the RM7.5 database from which they had ‘upgraded’. However, the only ways back using the RM user interface are lossy:

  • #GEDCOM which, along with other attendant losses (see GEDCOM & DnD transfer losses) does not import #namedgroup and custom #reports.
  • RM8’s File > Export Data > DropBox translates the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android) but it loses shared events, and likely more.

A more complete conversion is wanted.

2023-05-04: Update re #RM9: despite the inclusion of the Book Publisher feature missing from RM8, there are RM9 users who seek a way to return to RM7 for other reasons. A tweak to a RM9 database to change its version number to RM8 opens up the solution below to carry through to RM7. See Database Revert RM9 to RM8 to RM7.

Solution

Both RM7 and RM8 use similar SQLite databases so, obviously, a transfer of data through SQLite operations is the most direct means possible. However, “similar” is not “identical” and there are challenges with transferring Tasks, TreeShare, FamilySearch, Custom Reports and Report Settings and other File Settings, History, Bookmarks, et al. The script above provides the most thorough and complete conversion achieved to date and is built on and supersedes:

For a full understanding of what has gone into the current procedure, they remain useful references.

Procedure

  1. In RM8, open and prepare your RM8 database file for export by running the set of File>Tools>Database Tools. Note the file’s full path and name for entry into the SQLite script. Close the database.
  2. In RM7, create a new empty database or open the database you wish to overwrite (I’ll leave it to you to backup or make a copy!). If an existing file, run the RM7 set of File>Database Tools. Note the file’s full path and name so you can find it with your SQLite manager. Close the database.
  3. Open the target RM7 database with your SQLite manager (one with a fake RMNOCASE collation – see #rmnocase).
  4. Open your SQLite manager’s SQL Editor on this database file and load into it the script listed above under Solution.
  5. Edit this script to change the path and name in the ATTACH DATABASE line near the beginning to that of your source RM8 .rmtree database file. Edit 2 more lines near the end flagged by **** to explicit media paths.
  6. Execute the script.
  7. On reopening the target RM7 database with RM7, run File>Database Tools.
  8. If Media links are broken despite the edits you made to the script, use the Media Gallery’s Fix Broken Media Links tool and/or, as appropriate, Search & Replace (Ctrl+H) on Media filenames to fix them.

Notes

  1. Copies over the ConfigTable records from RM8 which can include Custom Reports and Book Publisher settings that originated in the RM7 database which had been upgraded to RM8, thereby making the round trip from RM7 to RM8 back to RM7 pretty much intact, except for possible losses in the area of Research Logs and Folders due to structural differences.
  2. This procedure is orders-of-magnitude faster than GEDCOM export-import, approximately 2 minutes from a 330MB RM8 database file, a few seconds for a small one. 
  3. A surprising outcome from this script development is that I’ve used the script to demonstrate  that RM 8.1.8 TreeShare falsely reports mismatches between Ancestry Sources and RM’s copies after applying ‘Merge all duplicate citations ‘. See: TreeShare mysteries from RM8.1.8 ‘Merge all duplicate citations’ (likewise for RM8.2).
  4. At some point, a RootsMagic 8 update or its successor will change the database in a way that breaks the script or causes an incompatibility.
  5. RootsMagic 7 is no longer being developed so features such as TreeShare and FamilySearch integration will break when those services change their APIs (the Application Program Interface with which RM7 interacts).
  6. Color-coding suffers in translation because RM8 has 28 colours, 13 of which are outside the range for RM7 and those that are within do not all map to the corresponding color.
  7. I was ‘lazy’ with data typing despite there having been changes between the two versions. SQLite itself is also ‘loose’ with enforcement. For example, many fields in RM7 that were type BLOB became type TEXT in RM8. My prior experience with RM in the past was that it did not care when the content was textual so I’ve made no attempt to CAST these TEXT fields back to BLOB on import. Yet, it’s possible it may give rise to some obscure error.
  8. I get a memory access error in citations using Find Everywhere on one file that originated in RM7 and imported back from RM8, yet there is no such error in the original nor in the RM8 upgrade. That’s an obscure error not present in other files I’ve converted.
  9. Mac users probably cannot run RM7 any more so this procedure is of little interest except for its potential to solve some RM8 problems by bouncing the data down to RM7 and back to RM8.
  10. Please let me know what errors you encounter, discoveries you’ve made, benefits you’ve realised…, probably best through the Forum, given its message editor is superior to the Comment editor.
  11. Good luck!

Returning to RM7.5 custom reports, groups and Publisher from RM8 via GEDCOM

UPDATE 2022-05-14: better than GEDCOM is to convert directly from RM8 to RM7 for which there are a couple of possibilities:

  1. RM8’s File > Export Data > DropBox creates a copy of the active .RMTREE file to a RM7 compatible .RMGC file intended for use with the RootsMagic Mobile app (now retired for Android). It is a complete conversion except, in my test, for Roles for shared events, which should be an easy fix for the developers. In my test, the RoleTable was empty which invalidates every shared event.
  2. Use one of my SQLite scripts that were developed subsequent to this original post:
    Direct Import of RM8 database into RM7 – Part 1
    Direct Import of RM8 database into RM7 – Part 2

Once the RM8 Export to DropBox is fixed, it would be operationally more attractive and is supported by RM Inc.

Issue

As of writing, still no Book Publisher feature in RM8 and along with other persistent issues, some users wish to return to the RM7.5 database from which they had ‘upgraded’. However, the only way back using the RM user interface is via #GEDCOM which, along with other attendant losses (see GEDCOM & DnD transfer losses) does not import #namedgroup and custom #reports. The original #RM7 database contains the custom reports and book specifications and possibly useful groups as they existed at the time of committing to the upgrade but importing into it results in an overload of duplicates, as described by PatrickR in his Apr 9 post to the RootsMagic User Community: Is there a way to copy custom reports to another database?

A solution

Years ago, I had an approach to creating a quasi-empty shell database file from a populated database that I thought might be adapted to this particular case: Depopulate but keep Customs, Places, Sources. Maybe a variant that depopulated but instead kept groups, custom reports, and book definitions could be used to import the GEDCOM from RM8 to good effect. I’m pleased to report that my experiment was successful, to the extent that GEDCOM transfer allows.

Procedure

  1. Review GEDCOM & DnD transfer losses and, in your RM8 database, ensure that your Fact Types are all set to export to GEDCOM. Run the RM8 database tools and Export the database having chosen to export everyone and everything.
  2. In RM7, make a copy or backup of the original database from which the RM8 ‘child’ was created. Run the RM7 database tools. Close the RM7 database file.
  3. Open the RM7 database file with your SQLite manager (one with a fake #RMNOCASE extension), load and execute this SQL file:
  1. Reopen the now-depopulated database file with RM7.
  2. Run the RM7 Database Tools.
  3. Import the GEDCOM file from RM8 choosing the option
    Preserve record numbers
  4. Done! Review what you’ve got. Note that the Media Gallery has to build thumbnails anew.

Possibilities

It should be possible to transfer data more completely by directly copying data from the RM8 database to the RM7.5 database via SQLite. However, because of changes in database structure, it’s not straightforward for several tables. Also, there may be serious incompatibility between the ConfigTable content of the two versions which may preclude the carryover of custom reports and other settings.

Privatise Extra BMD Events #events #reports #private

Problem

Many persons in my database have multiple Birth events created from different sources; likewise for other vital facts. Even though I may have marked one as Primary, the others still clutter reports: narratives look like repetitious gobbledygook; calendars have the same person multiple times, even on the same day. I want to clean up the publishing mess without losing the evidence.

This is especially the case for databases downloaded from an Ancestry Member Tree where events are generated by accepting or assigning a source and choosing to make the data into an alternate fact because it varies in some way from an existing fact.

Solution

Many report settings in RootsMagic have an option to include or exclude private events. I could reduce report clutter by setting the non-Primary Birth events to Private without losing the data that led to their creation. The Primary event should have whatever consideration and logic went into it being deemed the most accurate. I haven’t set a Primary in every case where my database has multiples so I want two things:

  1. A batch process that sets Private all the non-Primary events of a set having a Primary.
  2. A list of all the sets of multiple events that do not have a Primary so I can follow up.

Individual Summary with multiple Birth events, highlighted.

Individual Summary excluding Private events, in this case, the extra Birth events.

Implementation

BMD_private_multiples.sql 2020-02-15 Click to view; right-click to download.

The above SQLite script builds a series of temporary SQLite Views (stored queries), does the possible privatising and exits displaying the View “BMDmultstofix” which lists the persons needing follow up attention to set Primary one of the plural (multiple) events. The Views are all in memory and are lost when the SQLite manager exits the database.

Usage

The script executes very quickly on small files so it is possible to keep the SQLite manager open on the database and to re-execute the script at intervals while editing the database in RootsMagic. One could simply set the Primary event out of a group of multiple events for a person in RM’s Edit Person screen and then run the script to set the others Private or manually set them. Re-running the script will shrink the list needing Primaries by those you have set.

For family-type events (Marriage), the View “BMDmultstofix” will list both spouses separately. When the Marriage event is set to Primary on either spouse, the two separate listings will go.

Possible Enhancements

Colour Coding

The list of persons needing attention could be quite large, depending how assiduously you have been setting the Primary flag and the number of people and events in your database. So it might be nice to extend the last query or even last View to pick up the color code for each person which could be used to sort or filter the list.

Private as Default

Instead of privatising only those non-Primary events in groups having a Primary, another approach would be to privatise all non-Primaries. That would really clean out the reports and require user editing to get the desired outputs. This may be preferable.

Enhanced Version

BMD_private_multiples2.sql 2020-02-16

Both of the hypothesized enhancements have been incorporated. To ‘fix’ a group of multiples, one of the events must be set Primary and un-Privatised or another event added that consolidates one’s research into an assertion. By default, a new event is not Private and need only be made Primary.

In SQLiteSpy, any column can be sorted by clicking on the heading. Some other software, SQLite Expert is one example, also support filtering by column. These tools enable grouping color code or by surname without having to modify the script or write your own custom query of BMDmultsToFixClr view.

Screenshot

Screenshot from SQLiteSpy of results from running BMD_private_multiples2.sql

Reports – New Paragraph for General Note #paragraphing #reports

Contents

    RootsMagic 6.3.1.0 introduced in 2014 some very nice options for control of paragraphing of narrative reports after we had developed a number of scripts in 2011ff described in Paragraphing. There remain some issues for some people as of RM 7.2.1.0. One was recently expressed by Wiki member aefgen:

    Now another problem that I see has been discussed before: the last fact to print for a person in the narrative report is followed immediately by the general note for that person, even when you choose to have a space between each fact. That last fact (which will vary depending on what other facts are included in the database for that person) runs on into the text for the note.

    Reports-_New_Paragraph_General_Note.sql-_Narr_Report_Be.png
    Snippet from a narrative report showing the General (or Personal) note running on in the same paragraph as the last individual event.

    Here is a script that works around this limitation by pre-pending to the General Note two pairs of CR/LF control codes to force a new paragraph. It applies the codes only to non-empty notes.

    Reports-_New_Paragraph_General_Note.sql-_Narr_Report_Af.png
    Same report as above after running the script.
    Reports-_New_Paragraph_General_Note.sql-_Note_Editor.png
    The modified General Note as viewed in the RootsMagic Note Editor.

    When executed on a database, the script creates two temporary views that persist as long as the SQLite manager keeps the database open:

    • vBareNote: the non-empty General Note stripped of one or two pairs of leading CR/LF in readable text,
    • vNewNote: the Note from vBareNote pre-pended with two CR/LFs in readable text, which has been applied to the database Note field in PersonTable.
    Reports-_New_Paragraph_General_Note.sql-v_Bare_Note.png
    vBareNote should always show the first line of text from the General Note with no leading blank lines, else there is an issue.


    Reports-_New_Paragraph_General_Note.sql-v_New_Note.png
    vNewNote should always show two empty lines before the text, else there is an issue.

    The Note field in PersonTable is typed by RootsMagic to be BLOB (Binary Large OBject) so the script CASTs the readable text accordingly. That said, RootsMagic does not appear to preserve the type for this field converting it to Text when you next Save from the Note Editor. This change in type may be a useful indicator of edits done in RM after the last time this script was run.

    Reports-_New_Paragraph_General_Note.sql-_Person_Table.png
    After running the script, all the non-empty Note fields in PersonTable should be of type “BLOB” with two pairs of leading hex characters “0D 0A”, the ASCII control codes for CR/LF.

    If a vBareNote cell does not display the first words of the Note, there is a problem with the original Note in PersonTable that requires manual attention either through RootsMagic (the vBareNote.PersonID is the RootsMagic RIN or Record Number) or through the SQLite manager. Re-executing the script after such manual edits will assure that the PersonTable.Note fields consistently have two leading CR/LFs.

    Reports-NewParagraphGeneralNote.sql

    Language other than English #languages #reports #french

    Background

    RootsMagic Inc promised non-English language versions would be forthcoming when RootsMagic 4 was released in 2008. RootsMagic 4 was built on a database that supports Unicode and data can be stored in most languages. RoostMagic would seek volunteers to help translate the user interface and report strings into their languages. Nothing has ever come of that promise and users continue to seek ways in which they may be able to, at least, produce reports that are in another language.

    An Attempt

    This page provides the means to change what you can in RootsMagic 7 into another language and demonstrates its limitations. French is the example used because the author knows it a little bit.

    Ind_Summ_Delilah_Woods-_Franglais.png
    Individual Summary report with French fact names. However, highlighted words are hard-coded and cannot be changed.

    The Individual Summary Report has the fewest elements that are hard-coded in English, of any of the popular text reports. The Narrative reports have the most. The page above has highlighted examples of the hard-coded English text which the user cannot change.

    The report does show French fact names (maybe not the best translations but that is beside the point). The RootsMagic user interface in Lists > Fact Type List, allows the user to change the fact names and abbreviations for user-added fact types but not for the built-in fact types. However, it is possible to change the built-in fact types through SQLite as described below.

    One could open the database with a SQLite manager and edit the FactTypeTable fields Name and Abbrev directly for all having a FactTypeID under 1000 – these are the built-in ones. Everyone above 999 is custom and can be edited through RootsMagic.

    I took a different approach. I opened the FactTypeTable and copied from it to Excel, keeping just the columns FactTypeID (the key), Name and Abbrev. I added columns for the translated Names and Abbrevs and for formulas to create SQLite statements. I copied the Name column into Google Translate and its output into the translated Name column. Likewise for the Abbrevs. In Excel, I revised the translations as some were clearly wrong or poor (and maybe some still are). The spreadsheet could be used for any language.

    Then I developed a SQLite script in Excel that I could copy out to the SQLite manager. The script creates a table with the English and translated fact type names and abbreviations and revises RootsMagic’s FactTypeTable accordingly. It does not touch the fact sentences which remain in English but that is something the user can do through RootsMagic.

    Downloads

    FrenchFactTypes.xlsx Edit this spreadsheet to create the following script for another language.
    Facts-TranslateNames.sql Run this script as-is to convert your built-in fact types to French.

    Export to a French (or other) Database

    Because RootsMagic does not produce a complete non-English report even with the above translations, the only other option is to export the subjects of the desired report to another software that does. Below is an example of a report from Family Tree Builder 8.0, a free software from MyHeritage.com that can work in 40 or more languages. This is the first page of a Descendant report; it has no equivalent to the RootsMagic Individual Summary. While it is a major improvement linguistically, there are flaws due to the GEDCOM translations by both programs. And FTB outputs footnotes in its French report as blanks, not even the English originals; perhaps a bug in this new version that has undergone a major rewrite from version 7 as RootsMagic did between 3 and 4.

    RM_GEDCOM_imported_to_FTB-_Desc_Report_French.png
    Sample report from Family Tree Builder 8.0 from data imported from a GEDCOM exported by RootsMagic 7.

    Reports, Narrative, Jerry Bryan Trick #reports

    Rev 2016-08-16
    This is an extension of DummyFamily-Add.sql described below with added constraints so that the trick is applied only to those childless, spouseless persons having events other than Birth or Death or more than two of those or BD events with notes or having a General Note or an Alternate Name. Requested by wiki member aefgen in the hope that it would reduce the number of instances of “excessive white space”, a collateral consequence of the trick.
    DummyFamily-Add2.sql rev 2016-08-20 added having a General Note or an Alternate Name


    These two queries carry out the trick first reported by Jerry Bryan in the RootsMagic Forums in the topic Register Style Reports, Individuals Without Children or Spouses.

    DummyFamily-Add.sql

    /* DummyFamily-Add.sql
    Based on trick developed by Jerry Bryan to cause Narrative Descendant reports
    to output childless, spouseless children in their own generation, along with their
    married or parental siblings.
     
    Adds dummy family(ies) for this(all such) child(ren), based on the runtime parameter,
    to the FamilyTable. Requires SQLite Export Personal or equiv to support selection of a single
    child.
     
    After completing the report, run DummyFamily-Delete.sql; it is probably less risky, however,
    to run the first query and report on a copy of the database and then delete the copy.
    2012-01-15 ve3meo
    */
     
    INSERT INTO FamilyTable
     (FatherID)
    SELECT ChildTable.ChildID FROM ChildTable
    WHERE
     CASE
      WHEN @RIN NOT LIKE '' THEN ChildTable.ChildID = @RIN
      ELSE 1
     END
    AND
     ChildTable.ChildID NOT IN (SELECT FatherID FROM FamilyTable)
    AND
     ChildTable.ChildID NOT IN (SELECT MotherID FROM FamilyTable)
    ;

    DummyFamily-Delete.sql

    /* DummyFamily-Delete.sql
    Reverses the changes made by DummyFamily-Add.sql to the FamilyTable.
     
    After completing the report, run DummyFamily-Delete.sql; it is probably less risky, however,
    to run the first query and report on a copy of the database and then delete the copy.
    2012-01-15 ve3meo
    */
     
    DELETE FROM FamilyTable
    WHERE
     FatherID
     IN
      (
       SELECT FatherID FROM FamilyTable
       WHERE
        CASE
         WHEN @RIN NOT LIKE '' THEN FatherID = @RIN
         ELSE 1
        END
       AND
        MotherID IS NULL
       AND
        ChildID IS NULL
      )
    ;

    Discussions & comments from Wikispaces site


    aefgen

    Re: Dummy Family Add script (“Jerry Bryan trick”)

    aefgen
    16 August 2016 21:29:35

    Tom, thanks – on first examination, this revision definitely improves the report greatly. The choice of which individuals to carry forward to the next generation and which to keep under the parents works well and eliminates much but not all of the excess white space. I will look more carefully tomorrow and see if it might be tweaked further. One problem was that on trying to backup the file, RM said it encountered an error, and when I then used the database tool to check the file integrity, it gave me an SQ Lite error 1 – no such collation sequence: RMNOCASE. The program then froze and I had to use task manager to stop RM. But when I went back in and ran the database tools, they reported that all was well, and I was then able to backup the file. We’ll see if that happens again. – Alex


    thejerrybryan

    thejerrybryan
    20 August 2016 00:18:32

    The reports that I post process are usually 50 to 60 pages, so the scale is not quite so grand as yours. Nevertheless, I try to do as much of the cleanup as possible with global replaces. If you want to go that route, you will have to play with it to see what works for your needs and what doesn’t. I usually try to do the global replaces in a text editor, processing the RTF file produced by RM before it has been touched by Microsoft Word. That requires learning a little bit about RTF tags. The other approach (and sometimes I do both) is to do the global replaces from within Microsoft Word. You have to turn on Word’s equivalent of what WordPerfect used to call “Reveal Codes” to expose white space characters to global replace. It’s tricky business, either with a text editor or with Word, but if you can figure out what meets your needs you can save a huge amount of time with global replaces. In case you want to try the RTF file plus text editor option, I wouldn’t recommend Notepad for a file that big. I would recommend Notepad++ instead – a very powerful and free text editor.

    On your other questions, I really haven’t looked at them. What you are doing seems much fancier than what I do, and I have to admit that I don’t use Tom’s script. I still do it kind of manually where I add dummy children to anybody I want to force into the next generation in a narrative report. I leave such dummy children in my production database at all times and when I’m preparing a report for a family reunion I copy my production database into a reporting database that I can manipulate as necessary to produce the report. I use File->Copy, not Drag-and_Drop or GEDCOM Export/Import to make the copy. Then, I have an SQLite script that essentially deletes the dummy children without also deleting the FamilyTable entries for the parent of the dummy children. You can’t do the delete of dummy children that I’m talking about from within RM itself because RM is smart enough to also delete the FamilyTable entry for the person to be carried into the next generation, and using the FamilyTable entries to carry the person into the next generation is the whole point of the exercise. So my delete of the dummy children is an incomplete delete which is exactly what is needed. But I’m afraid that my rather manual technique doesn’t scale up very well to your use case. That’s part of the reason that Tom wrote his script.

    Jerry

    Reports – Point Form Narratives Setup #reports

    Contents

      Rev 2016-04-05: Version 2
      Tired of verbose and repetitious sentences in narrative reports and the effort involved in customising and tweaking them so that grammar and prepositions and pronouns were correct, I got the idea some years ago that what I might prefer is a hybrid of the tabular format of the Individual Summary and Family Group Sheet with event/fact notes embedded rather than at the end. Jerry Bryan demonstrated such a concept with his Oct 12, 2015 RootsMagic Forums posting, “Preparing an RM Descendant Narrative Report for a Family Reunion for This Year“. While he did so using largely manual procedures, this SQLite script makes it easy to try out a similar format and then restore your prior format.

      SettingsNormalPoint Form
      Normal – “Keep facts in same paragraph”

      Point Form – space character starts fact, 0 Carriage Returns

      Note that for all examples of Point Form, the RootsMagic report settings option for paragraphing is set to “Keep facts in same paragraph”.

      Although the Point Form is not strictly point form because the facts run together in the same paragraph, it is actually better for fast reading than is the Normal report because there are fact names that catch the eye, the ‘sentences’ are terse and consistent and it is the most efficient in use of space and paper.

      DescNarrJohnWoods-Normal0DescNarrJohnWoods-Normal0.pngDescNarrJohnWoods-PointForm0DescNarrJohnWoods-PointForm0.png
      Normal – “New paragraph after every fact”

      Point Form – 2 Carriage Returns start fact

      Both of these waste trees!

      DescNarrJohnWoods-Normal1DescNarrJohnWoods-Normal1.pngDescNarrJohnWoods-PointForm2DescNarrJohnWoods-PointForm2.png
      Normal – “New paragraph after facts with notes”

      Point Form – 1 Carriage Return starts fact

      This Point Form layout is the most attractive of the three or, at least, comes closest to the concept of a hybrid of the Individual Summary and Narrative report.

      DescNarrJohnWoods-Normal2DescNarrJohnWoods-Normal2.pngDescNarrJohnWoods-PointForm1DescNarrJohnWoods-PointForm1.png

      Features:

      • Backs up key tables or parts thereof to ‘x’ versions which are used by the complementary script to restore back to the previous format.
      • User control* over fact/event sentence heading format: one of plain, bold, italics, underlined.
      • User control* over fact/event sentence lead-in carriage returns.
      • Changes default sentences to point form without [person] variables.
      • Default sentences begin with the fact type name as a heading. This is the name defined in Lists>Fact Type List; the user can edit user-defined fact types in RootsMagic.
      • Alternate Names are labelled by their type, if assigned in the Edit Person screen.
      • Shared events show the role names of the sharers.
      • Adds _Heading event to provide the initial [person] sentence as the first ‘fact’ so each person’s section will start with the person’s name and not be stated as ‘factless’. Version 2 does not do this; instead it uses a Jerry Bryan trick of putting a line feed/carriage return after the [Person] variable for the Birth sentence template, normally the person’s first event. This does require a Birth event as the first event to be output to the narrative report.
      • Adds two trailing CR/LF to the Note field of the last family event (by SortDate) of each couple to force the spouse’s subsection of a person’s report section to start a new paragraph. (V2)

      *The script works best with a SQLite manager that supports run-time variables such as SQLite Expert Personal to provide control over the format of fact headings and fact paragraphing. Those that do not support them, such as SQLiteSpy, default to bold and no <CR>s. In either case, a RMNOCASE collation extension is needed. To add the RNMOCASE collation extension, see either:

      Version 2
      Reports-PointFormNarrativeSetup2.sql
      Reports-RestoreAfterPointForm2.sql
      Version 1
      Reports-PointFormNarrativeSetup.sql
      Reports-RestoreAfterPointForm.sql

      Use:

      1. Open your database (safer to use a copy) with your SQLite manager.
      2. Load the RMNOCASE extension.
      3. Load the SQLite script Reports-PointFormNarrativeSetup.sql.
      4. Execute the script.
      5. SQLite will throw an error if the database has been previously modified by this script without subsequently running Reports-RestoreAfterPointForm.sql. Load and run the latter and return to step 3.
      6. If prompted for a value for $FactNameFormat_ibun, type in one of the letters: italics, bold, underline, no and OK. Any value other than i, u, n is the same as b.
      7. If prompted for a value for $ParagraphCR_0_1_2, type in one of the numbers 0,1,2 to define the number of Carriage Returns prepended to the sentence. Any value other than 1 or 2 is the same as 0 – a leading space character.
      8. On successful completion the results window will display a status message so reporting.
      9. Open RootsMagic and generate narrative reports. Use the Report Settings Option “Keep fact sentences in same paragraph” to start, especially if you have chosen to have lead-in carriage returns.
      10. You can leave the report preview open and rerun the scripts to switch back to original and to different point form formats. After each execution of a script, you can quickly see its effects from report view with Alt-t to return to Report Settings and <Enter> to regenerate the report with the changes.
      11. You may want to try some other scripts that affect reports after having set up your database for point-form reports:

      Reports – Concordances for Indexes #names #alternatenames #places #reports #msword #index

      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

      Names-ConcordanceCapsQuery.png
      The SQLite query produced this concordance table. Copied from SQLiteSpy straight to a new MS Word document without modification.
      Names-ConcordanceCaps.png
      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.

      Names-ConcordanceCapsFirstYrsMids.PNG
      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

      Privatize Living #private #reports #gedcom

      Contents

        This script sets private the events, alternate name facts, and personal notes for persons whose Living flag is set. Thus, reports and GEDCOM can take full advantage of the options:

        • Privatize living people (GEDCOM only)
        • Include private facts
        • Include {private} notes
        • Strip { } brackets

        thus providing many more combinations by which these outputs may be tailored.

        This is a first pass script, lightly tested so use the usual precautions and feedback any problems. There is a known problem with privatizing notes that are already privatized – RM may output a closing brace character “}”.

        PrivatizeLiving.sql

        -- PrivatizeLiving.sql
        /* 2013-12-14 Tom Holden ve3meo
        Privatizes events, alt names, personal notes for persons with Living flag set
        so that reports can be made with names of living persons but no details while
        full details are outputted for the deceased.
         
        To Do: privatize family note when one spouse is Living.
         
        N.B.: There is a series of queries in a comment block at the end which can be run
        to UNDO the privatization, it is nevertheless advisable to run this script against
        a copy of your database in case the process is not perfectlt reversible. Of course,
        you may wish to keep the resulting privatization if it suits your purposes so that
        reversal is unnecessary.
        */
         
        -- Make a table of persons marked Living
        DROP TABLE IF EXISTS xLivingTable
        ;
        CREATE TEMP TABLE IF NOT EXISTS xLivingTable
        AS
        SELECT PersonID, Note, TRIM(CAST(Note AS TEXT)) AS NoteTxt FROM PersonTable WHERE Living
        ;
         
        -- Make a backup table of EventIDs and their IsPrivate setting
        -- for all persons marked Living; must cover individual and family events
        DROP TABLE IF EXISTS xLivingEventsBak
        ;
        CREATE TABLE IF NOT EXISTS xLivingEventsBak
        AS
        SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Individual events
        INNER JOIN xLivingTable Lv ON E.OwnerID = Lv.PersonID AND E.OwnerType = 0
        UNION
        SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living husband
        INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
        INNER JOIN xLivingTable Lv ON Fm.FatherID = Lv.PersonID
        UNION
        SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living wife
        INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
        INNER JOIN xLivingTable Lv ON Fm.MotherID = Lv.PersonID
        ;
         
        -- SET EventTable.IsPrivate = 1 for all events in xLivingEventsBak
        UPDATE EventTable
        SET IsPrivate = 1
        WHERE EventID IN
        (SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
        ;
         
         
        /*
        -- Alternate Names can also be set private and perhaps desirably so so we repeat
        the pattern above
        */
        -- Make a backup table of non-primary NameIDs and their IsPrivate setting
        -- for all persons marked Living;
        DROP TABLE IF EXISTS xLivingNamesBak
        ;
        CREATE TABLE IF NOT EXISTS xLivingNamesBak
        AS
        SELECT NameID, IsPrivate FROM NameTable
        WHERE OwnerID IN
        (SELECT PersonID FROM xLivingTable)
        ORDER BY NameID
        ;
         
        -- Privatize Alternate Names for Persons marked Living in the database
        UPDATE NameTable SET IsPrivate = 1
        WHERE NameID IN
        (
         SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
        )
        ;
         
         
        -- PRIVATIZE Personal Notes for persons marked Living by enclosing in braces {}
        UPDATE PersonTable
        SET Note =
        --SELECT
        CAST(REPLACE(CAST(Note AS TEXT), CAST(Note AS TEXT), '{' || CAST(Note AS TEXT) || '}') AS BLOB)
        --FROM PersonTable
        WHERE PersonID
        IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
        ;
         
        /*
        -- BLOCK OF UNDO queries to reverse the above changes.
         
        -- UNDO Privatize events by SET EventTable.IsPrivate = 0 for all events in
          xLivingEventsBak that have IsPrivate = 0
        UPDATE EventTable
        SET IsPrivate = 0
        WHERE EventID IN
        (SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
        ;
         
        -- UNDO privatization of Alt Names for Living persons
        UPDATE NameTable SET IsPrivate = 0
        WHERE NameID IN
        (
         SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
        )
        ;
         
        -- UNDO Privatize Personal Notes by updating from xLivingTable
        UPDATE PersonTable
        SET Note =
        (SELECT Note FROM xLivingTable Lv WHERE PersonTable.PersonID = Lv.PersonID)
        WHERE PersonID
        IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
        ;
        */
        -- END of script