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