I have some experience with SQL but would like to create a query that would create a named group of Orphans that would contain the people in the file that have no parents, spouses, or children. These people are most likely remnants from deleting trees in my old program that I used. Another reason is that one of my gedcoms had family information missing so the entire file imported as individuals that were unconnected.
In the sample file there is only one orphaned person in the file. In my personal file there are actually hundreds of them. Since the file is large, I would like to be able to run the query that would update the named group as I work through merging two large files (over 300,000 people) so I can try to keep track of who is still orphaned in the tree.
This approach involves two steps of development:
- Script to store the RIN (PersonID) of these so-called “Orphans” in a temporary table
- Script to create/update a Named Group from the temp table
They are combined into a single script downloadable from the bottom of the page. It is efficient: in a database of 160,000 people, it created a group of the 1000 unconnected persons in ~3 seconds. It takes longer than that for RootsMagic to initially display the group in the sidebar; People View displays the group very quickly.
A message about this solution has been posted to the RootsMagic Forums at http://forums.rootsmagic.com/index.php?/topic/13326-dynamic-group-for-unconnected-persons/.
The “Orphans” Table
-- Persons-NoParentNoSpouseNoChild.sql /* 2014-01-23 Tom Holden ve3meo 2014-01-24 rev changed table name to more generic Creates a temporary table xGroupTempTable of PersonIDs (RINs) of those who have neither a parent nor a spouse nor a child. To be used with another procedure to update a named group. */ DROP TABLE IF EXISTS xGroupTempTable; CREATE TEMP TABLE IF NOT EXISTS xGroupTempTable AS SELECT * FROM ( --- Persons not in FamilyTable, either no spouse or no child SELECT PersonID FROM PersonTable EXCEPT SELECT * FROM ( SELECT FatherID AS PersonID FROM FamilyTable UNION SELECT MotherID AS PersonID FROM FamilyTable ) ) NATURAL INNER JOIN ( -- persons with no parent SELECT PersonID FROM PersonTable EXCEPT SELECT ChildID FROM ChildTable );
Try the above script. Examine the temp table and look up some persons identified therein using RootsMagic to confirm that it is working as expected.
The “Orphans” Named Group
This script was extracted and adapted to work with the above script from Group – Persons with Text Dates. It creates and maintains a RM Group named “SQL: Unconnected Persons” from the temporary table xGroupTempTable.
After running this script with RootsMagic open simultaneously on the same database, it is necessary to refresh the Sidebar Group display. One does so by selecting a different group and re-selecting “SQL: Unconnected Persons”. Unfortunately, if this is the only group in the Groups list, you may have to close and reopen the database to refresh the list of members.
-- Group-UnconnectedPersons.sql /* 2014-01-24 Tom Holden ve3meo Creates and updates a named group of persons having no parent, no spouse, nor a child in the database from a temp table xGroupTempTable created by another script */ -- Create Named Group if it does not exist 'SQL: Unconnected Persons' INSERT OR IGNORE INTO LabelTable VALUES ( ( SELECT LabelID FROM LabelTable WHERE LabelName LIKE 'SQL: Unconnected Persons' ) ,0 ,( SELECT ifnull(MAX(LabelValue), 0) + 1 FROM LabelTable ) -- ifnull() needed if LabelTable is empty ,'SQL: Unconnected Persons' ,'SQLite query' ); -- Delete all members of the named group DELETE FROM GroupTable WHERE GroupID = ( SELECT LabelValue FROM LabelTable WHERE LabelName LIKE 'SQL: Unconnected Persons' ); -- Add members to the named group INSERT INTO GroupTable SELECT NULL ,( SELECT LabelValue FROM LabelTable WHERE LabelName LIKE 'SQL: Unconnected Persons' ) ,PersonID AS StartID ,PersonID AS EndID FROM ( SELECT DISTINCT PersonID FROM xGroupTempTable );
Download complete script
This file combines the two scripts into one so that a single pass takes care of updating the group. Group-UnconnectedPersons.sql
Discussions & comments from Wikispaces site
Comment: “keep track of who is still orphaned”
03 September 2018 21:24:57
ve3meo Jan 22, 2014
Have you tried RM’s Tools > Count Trees function? Each orphan will be listed as a tree with a Count of 1 and the Start Person will be the orphan.
mom2twinzz Jan 22, 2014
Yes. However, since I am dealing with potentially hundreds of these orphans, grouping them is more helpful than having the tree finder find each tree each and every time I am trying to connect a person.
What happened is that I had a computer malfunction that apparently truncated my gedcom from Legacy family tree. I no longer have the original LFT file, so direct importing the file into RM is impossible. Looking at a tree count, I have over 11,000 trees more than 10000 are orphaned trees, but that have information that I still want to merge in. Automatic merging has failed since the information was conflicting (middle names exist in one file but not the other, etc.) and failed to merge most of the people. So I am forced to manually merge people. Having a named group of orphans helps me to keep track of where I am in the merge process.