SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Reports, Narrative, Jerry Bryan Trick #reports

Posted on by Tom Holden

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

One Reply to “Reports, Narrative, Jerry Bryan Trick #reports”

  1. Jerry, I have always loved using this script to print bulleted reports when it suits my needs or audience. I can’t get it to work in RM9. I get the error SQLite 3 Error 1 – no such table: xEventTableSentences. Is it possible to change the script (and the restore version) to make it compatible with RM9?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.