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