Reports,+Narrative,+Jerry+Bryan+Trick

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. 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].

code format="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) code

code format="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  ) code