Jump to content


Photo

Register Style Reports, Individuals Without Children or Spouses


  • Please log in to reply
5 replies to this topic

#1 c24m48

c24m48

    Advanced Member

  • Members
  • PipPipPip
  • 2613 posts

Posted 14 January 2012 - 08:00 PM

I've written before about individuals without children or spouses in Register and Modified Register reports. Such individuals appear only in the list of children of their parents in Register and Modified Register reports. Individuals with children or spouses appear a second time in the next generation.

Normally, this behavior is exactly what is desired. However, if an individual without children or spouses has lots of data (especially notes), the report would read much better if the individual in question could be forced to appear in both generations - with summary information only in the list of children of their parents, and with complete information the next generation. A solution to this problem is a long term Wish List item.

I have found a solution, but it involves modifying my RM5 database with SQLite. The process seems pretty safe, but it makes me nervous enough that what I actually do is to make a copy of my RM5 database, make the required modification in the copy, run the Register or Modified Register report from the copy, and delete the copy.

Here's the trick after making a copy of my database:
 

  • Add a dummy child to the person in question without adding a spouse to the person in question. For example, if the person in question is John Doe, add a dummy child to John Doe (for example, add "Temporary Doe" as a child of "John Doe") without adding a spouse to John Doe. Make note of the RIN number of the dummy child (also known as the record number).
  • With SQLite, execute the following: DELETE FROM ChildTable WHERE ChildID=xxxxx. Replace xxxxx with the RIN number of the dummy child. This will have the effect of unlinking the dummy child from John Doe while leaving in place the family of John Doe where John is the father/husband.
  • At this point, John's family where he is the father/husband will have no mother/wife and no children, but the fact that there is a family will cause him to appear with a summary of his data in the list of children of his parents and to appear with all of his data in the next generation. The reason that the same cannot be accomplished from within RM5 itself is that if you unlink the temporary child from John Doe, then the family where John is father/husband will be deleted.
  • The dummy child will still be in the database but will be unlinked from any family. The dummy child probably should be deleted, but I don't bother since I'm going to delete the entire temporary copy of the database as soon as I run the report anyway.
  • For reasons I don't quite understand, the data for John Doe in the report is followed by much more than the usual amount of excessive white space. I just clean that up manually. There are very, very few people in my database that need this kind of special handling in reports, so the special manual handling is not a problem.

By the way, the trick does not survive a GEDCOM export or a drag and drop. Essentially I have deliberately introduced a structural error into my database to get the desired effect in a report (the structural error is a family with a father/husband and no wife/mother or children), and the GEDCOM export or drag and drop does not include the structural error that I created.

Jerry



#2 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5467 posts

Posted 14 January 2012 - 09:26 PM

Neat trick, Jerry! Might it also be accomplished by using SQLite to add a childless, spouseless family of the person? One or two less steps, no orphan to delete, just this dummy family.

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#3 c24m48

c24m48

    Advanced Member

  • Members
  • PipPipPip
  • 2613 posts

Posted 14 January 2012 - 09:40 PM

Neat trick, Jerry! Might it also be accomplished by using SQLite to add a childless, spouseless family of the person? One or two less steps, no orphan to delete, just this dummy family.

Yes, the exact same thing could be accomplished by adding one row to the Family Table. The entire row would contain zeros except for the FamilyID for the new family (primary key for the table), and for either the FatherID (for males) or the MotherID (for females) but not both. For males, the MotherID would be zero and for females the FatherID would be zero. Doing it this way would not require any activity in RM5 at all. Well, I would still want to make a copy of my database rather than making such an intrusive change to my production database.

Jerry

#4 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5467 posts

Posted 14 January 2012 - 10:04 PM

I imagine two queries, for the time being, until your wish for the report is fulfilled by RM:
  • Add a dummy family to the FamilyTable for every person not in the FamilyTable nor in the ChildTable.
  • Delete all dummy families from the FamilyTable.
When you want to print a narrative report, run 1.

When you return to work on your database, run 2.







Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#5 c24m48

c24m48

    Advanced Member

  • Members
  • PipPipPip
  • 2613 posts

Posted 15 January 2012 - 08:58 AM

I imagine two queries, for the time being, until your wish for the report is fulfilled by RM:

  • Add a dummy family to the FamilyTable for every person not in the FamilyTable nor in the ChildTable.
  • Delete all dummy families from the FamilyTable.
When you want to print a narrative report, run 1.

When you return to work on your database, run 2.

I really wouldn't want to do it that way for the following reasons.

  • The number of individuals for whom this special treatment is appropriate is usually very small. You really don't want to apply this special treatment to every person not in the FamilyTable nor in the ChildTable. For example, if all you have for an individual is a birth date, or if all you have for an individual is a birth date and death date, then this special treatment is typically not appropriate unless you also have extensive notes for the individual. Which is to say, if all you have is birth date (or birth date and death date) then it typically is quite satisfactory for the individual to appear only in the list of children of their parents. For example, at a family reunion I might have a family with two children born in 2005 and 2007, respectively. This special treatment typically wouldn't be appropriate for such children. Therefore, I want to apply this special treatment very selectively on a case by case basis rather than for every person not in the FamilyTable nor in the ChildTable.
  • I'm creating a family structure that doesn't occur natively in RM5. Namely, I'm creating a family consisting of only a single individual. Natively in RM5, every family consists of at least two individuals - a least a person and their spouse, or at least a person and one child. So I worry about possible unintended side effects of creating a family structure that doesn't occur natively in RM5, and therefore I don't want to create these oddball families in my production database. Therefore, I only do it in a copy of my production database for the purpose of creating a report. And since I only create the rather odd family structure in a copy of my database, I just delete the copy when I'm done and there is nothing that needs to be undone.
Therefore, I just add the dummy family for the few individuals for whom it is appropriate for a particular report that I'm working on.

Jerry

#6 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5467 posts

Posted 15 January 2012 - 10:03 AM

In honour of your inventive genius, Jerry, I have created Reports, Narrative, Jerry Bryan Trick with queries that allow you to selectively add a dummy family for a child or for all such childless, spouseless children and to do the complementary delete. As far as I can tell, the database returns to normal after the deletion(s). Of course, as long as the dummy family exists, the main view will show the person as having 1 spouse with no name, and possibly the opposite gender from the common case.

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.