SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Ancestry.com Sync #ancestrycom #gedcom #ftm2014

Posted on by Tom Holden

Under development

This page attempts to find the best practices for working between RootsMagic and Ancestry.com with highest completeness, fidelity and efficiency. It supersedes an earlier page, Ancestry.com and RootsMagic 5, as a result of new understanding of the roles of certain custom GEDCOM tags and a change in what is exported by Ancestry.com.

Summary

Upload RootsMagic GEDCOM to Ancestry.com

  • For previously downloaded Ancestry source references to be recognised by Ancestry, convert the values stored in notes to custom “_APID” tags.
  • Be aware of the RootsMagic database fields not exported or its GEDCOM tags ignored by Ancestry.com

Download Ancestry.com GEDCOM to RootsMagic

  • For ShareMerge to operate on persons previously uploaded, find all “1 UID” and replace with “1 _UID”
  • For Ancestry source references to be preserved, find all custom “_APID” tags and convert to notes.
  • Be aware of the Ancestry Family Tree field values that are not exported

Ancestry.com GEDCOM export/import shortcomings

The GEDCOM exported from Ancestry.com (actually ancestry.ca in my case) is incomplete. Nor does its import include all the values exported. Uploading the GEDCOM to a new Ancestry Member Tree and comparing it to the original tree is a quick way of finding the deficiencies in the export-import round trip. The following have been found to be missing in the imported tree; those asterisked are exported but not imported:

Overview

  • Web Links

Media Gallery

  • Media* (link to a page for each uploaded item is exported but import does nothing with it)

Source

For a user defined source:

  • Publisher Location
  • Publisher Date

Repository

For a user defined repository:

  • Address*
  • Phone Number*
  • Email*
  • Call Number*
  • Notes*

Source Citation

For a user defined source:

Citation Information

  • Web Address moved into “Other information”*

ShareMerge Persons using RM’s UniqueID

There is a way to transport RootsMagic’s internal UniqueID for a person uploaded to an Ancestry Family Tree and have it returned to a RootsMagic database without jumping through the hoops with the custom RMID event I described in the RootsMagic Forums in this post. Ancestry imports the value of the _UID tag exported by RootsMagic, stores it invisibly, and exports it with a UID tag. RootsMagic does not recognise the UID tag but does if it is prepended with the underscore, the same as it exports. The value is imported into the UniqueID for that newly created person in a RM database. If there are two persons in a database with the same UniqueID, ShareMerge merges them.
It is a simple text editor search and replace to find all “1 UID” and replace with “1 _UID” in the GEDCOM exported from Ancestry but it is a bit of a nuisance. Persons in the AFT that did not originate from the RM export but were created within Ancestry are NOT accompanied by a UID tag.
It would be desirable that either Ancestry exported the UID value with the same tag that it saw on import OR that RootsMagic would recognise the UID tag as equivalent to its _UID tag.

Syncing Ancestry Sources via the Ancestry _APID Tag

TBD

Manually Adding _APID Tags for Ancestry Sources

The following is adapted from a post in the ReunionTalk Forum

On manually citing a source from Ancestry, add the Ancestry APID tag to the Source Details Comments, in the same format as the process that converts such tags in an Ancestry GEDCOM to a Comment.

An advantage in doing this is that you can do all your research using RootsMagic, adding a _APID tag detail against each source citation; export the GEDCOM; run a regular expression search and replace to adjust the GEDCOM file and when importing into Ancestry.com, the source citations are all linked to the original Ancestry source records. A disadvantage is it takes alot more effort and validating that the APID details are correct.

The format is _APID 1,<ID>::<Header #> where <ID> is a unique value for each Ancestry source record type and <Header #> is the header which can be extracted from the URL.

The APID tags are found in the GEDCOM file for Ancestry sources that have been saved in your tree. In Ancestry, click on Manage Tree and there is an Export Tree button to click (it takes a few seconds to generate and then you have an option to download the file). This saves a GEDCOM file (text file) which when opened contains the APID tags for each citation (i.e. search for _APID).

Ancestry to my knowledge use these tags internally in their web site and FTM software but the GEDCOM file you export, will show these (provided you have saved Ancestry sources).

For example, a residence event would look like this (some date and some address and source# would be specific to your research entry. The APID details would be specific to the Ancestry record for this research.

1 RESI
2 DATE Some date
2 PLAC Some address
2 SOUR @Source#@
3 PAGE Some date
3 _APID 1,1836::21884408

The format is 1, followed by a APID database tag (sounds complicated but each record collection in Ancestry has a separate tag ID number followed by 2 colons, followed by the header tag for your record. When you look at the URL for each Ancestry record, there is a long text but it has a h=, the number that follows is the header.

This information is keyed into RootsMagic. Once you know the Ancestry tag for one type of records, e.g. UK census 1911, you can use this for all 1911 census citation details with only the header part changing for each. The example above is the tag ID for NZ electoral roll information.

In the above example, the citation Comment you would enter in RootsMagic would be “{_APID 1,1836::21884413}”, sans quotes.

Source Templates #sources #sourcetemplates #rmnocase #conversion #editable

Posted on by Tom Holden

This page collects info and queries about Source Templates.

A Trio of Templates

Framing Citations of the 1930 US Census with RootsMagic 5 Free Form, Standard and Custom Source Templates.
This is a 15 page study of the usage and results of three very different templates, coming to the conclusion that RootsMagic 5’s Source Template feature is still buggy and incomplete and that a custom variant of the built-in Free Form has advantages over other templates.
Source Templates, A Comparative Example.pdf
For an argument against the conclusions of this study, see Rebuttal by Jeff La Marca.

User-Editable Copies of Master Source Templates

The RootsMagic user is blocked from editing the ‘built-in’ Source Templates. Within RootsMagic 4&5, you can copy individual templates; the copy is user-editable. Alternatively, you can import all 413 of the Master source templates as user-editable copies from this file.
RootsMagicSTuser.rmst Updated 6 Jan 2012 to the RM5 templates.

The templates have all been renamed with a leading asterisk and will precede the uneditable ones in the Source Templates List.

Convert Sources With Uneditable Templates to Editable

Revised 18 Feb 2010

The superseded procedure picked out only those built-in templates actually used in the database, copied them to user-editable templates and re-pointed the Sources accordingly. It was later discovered that future importing of user-defined templates could result in a collision of TemplateIDs preventing further imports so that procedure has been abandoned. This new procedure requires the importing of RootsMagicuser.rmst into the Source Templates List first, using RootsMagic itself. The SQLite procedure is then run to match those built-in templates, which are used by Sources in the database, with the corresponding editable one and generates a result set containing SQL UPDATE commands to revise the SourceTable. These commands are copied to a SQL editor and run against the database.

No further work is required such as copying Source data over to the new Source and likewise for each citation of that source. For a large source list using several built-in templates requiring modification and extensively cited, the savings in effort would be significant.

N.B. All these procedures modify tables in the RootsMagic database and require a SQLite manager that can provide a RMNOCASE collation or spoof thereof. SharpPlus SQLite Developer has been used successfully with these procedures. SQLiteSpy with a fake RMNOCASE extension loaded should also work as will SQLite Expert Personal.

SrcTmpltsConvert2.sql Converts Sources from using built-in templates to the previously imported user editable copies of same. rev 2014-06-15 to match names of built-in templates with names of user-editable copies prepended by the asterisk used in the current RoostMagicSTuser.rmst file, rather than the underscore character of the initial file.
SrcTmpltsRevert.sql Reverts a database having been processed by the superseded procedure (SrcTmpltsConvert.sql) to its pre-conversion state.
SrcTmpltsRevert2.sql Reverts a database having been processed by the new procedure (SrcTmpltsConvert2.sql) to its pre-conversion state. N.B. revise with asterisk instead of underscore as the prepended character for user-editable copies of built-in templates.

List of Source Templates used and by what

This query is on a page of its own.

Fix Extra Line Feeds in Footnote

Vital Records (state-level, online derivatives)

This problem was first reportedby David E. Cann on 1 Oct 2011 in the ROOTSMAGIC-USERS newsgroup. The sentence template for the long footnote for this source type contains two extraneous Carriage Return control codes which results in unwanted whitespace when it is printed in a report’s footnotes and endnotes, as well as in the Source List report. It appears to be present in the RootsMagic.st file from 3 Feb 2010 and may even pre-date that file. Correcting it in RootsMagic.st using a no-wrap text editor appears to neither affect a pre-existing database nor, surprisingly, pass it on to new database files created post-correction. It’s as though the Source Template specifications contained in RootsMagic.st are now embedded in the program file (ver 4.1.2.1 20 Sep 2011) but the program has not been stripped of all the vestiges of the old way templates were distributed.

Until the developers release an update of RM4 that corrects this template and previously created databases, the following SQLite query may be the only way to remove the extra lines from within the database. Of course, one can edit a report’s RTF file but that would have to be done every time the report is generated. Better to fix the fault at source.

UPDATE SourceTemplateTable SET Footnote = '<[Jurisdiction] ><[Creator], ><?[ItemTitle]|"[ItemTitle]," <[ItemType:Lower:Abbrev], >><i>[WebsiteTitle]</i><?[ItemTitle]| |, <[ItemType:Lower:Abbrev] >>([URL] : <[AccessType]|accessed> [AccessDate]), [ItemOfInterest].'
 WHERE Footnote LIKE '<[Jurisdiction] ><[Creator], ><?[ItemTitle]|"[ItemTitle]," <[ItemType:Lower:Abbrev], >><i>[WebsiteTitle]</i><?[ItemTitle]| |, <[ItemType:Lower:Abbrev] >>([URL] : <[AccessType]|accessed> [AccessDate]), [ItemOfInterest].';

A Better Free Form Template

This section has been moved to a new page: Source Template, A Better Free Form

Discussions & comments from Wikispaces site


ve3meo

Attempted correction of existing databases

ve3meo
31 December 2009 19:52:10

The proprietary RMNOCASE collation sequence prevents the 3rd party SQLite managers from updating sourcetemplatetable. I had thought to offset the corrected templates TemplateID by 11000, save as a .rmst file, import as user defined templates, delete all records with TemplateID < 10000, and then subtract 11000 from TemplateID where it’s >= 11000. But none of the 3 or 4 SQLite managers I tried would allow the transaction to complete|commit|end. RMNOCASE always got in the way.

A wild card would be to do the import as I describe, create a new table identical to sourcetemplatetable without RMNOCASE, populate it with the TemplateID >=10000 from existing sourcetemplatetable, do the subtraction on the new table (that works), delete the old table, rename the new table to sourcetemplatetable, recreate the index. I wonder what would happen???
TomH

Group – Descendants #descendants #namedgroup #recursive

Posted on by Tom Holden

This query creates or updates a named group of descendants of a given person. This new query is unlimited in the number of generations as it is based on Recursive Queries – Ancestors and Descendants which exploit capability introduced to SQLite in Feb 2014. When run, you will be prompted for the Record Number (RIN) of the person whose descendants are to be in the group and for the type of descendant relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).

Group-Descendants.sql

-- Group-Descendants.sql
/*
2014-04-15 Tom Holden ve3meo
Creates and updates a named group of descendants of a person using an unlimited
recursive tree climb. Does not include starting person in the group. An example
of an auto-generated group name is "!Desc: Holden, Robert Alexander-155." but the
query will update any group with a name beginning "!Desc:" followed by anything
to "-nn.", where nn is the RIN number followed by the period character.
 
Requires support for SQLite 3.8.3 or later and named parameters.
Developed and tested with SQLite Expert Personal 3.5.36.2456
*/
-- Register the RIN of the starting person for following queries
-- to preclude repeated entry
DROP TABLE
 
IF EXISTS xGroupIDTable;
    CREATE TEMP TABLE
 
IF NOT EXISTS xGroupIDTable AS
    SELECT @StartRIN AS RIN
        ,-- named parameter is prompted for user entry
        NULL AS GroupID -- to be filled in after finding or creating the group label
        ;
 
-- Create Named Group if it does not exist '!Desc: Surname, Given-RIN.'
INSERT
    OR IGNORE
INTO LabelTable
VALUES (
    (
        SELECT LabelID
        FROM LabelTable
        WHERE LabelName LIKE '!Desc:%-' || (
                SELECT RIN
                FROM xGroupIDTable
                ) || '.'
        )
    ,0
    ,(
        SELECT ifnull(MAX(LabelValue), 0) + 1
        FROM LabelTable
        ) -- ifnull() needed if LabelTable is empty
    ,'!Desc: ' || (
        SELECT Surname || ', ' || Given
        FROM NameTable
        WHERE OwnerID = (
                SELECT RIN
                FROM xGroupIDTable
                )
            AND + IsPrimary
        ) || '-' || (
        SELECT RIN
        FROM xGroupIDTable
        ) || '.'
    ,'SQLite query'
    );
 
-- register GroupId in the temp table for following queries
UPDATE xGroupIDTable
SET GroupID = (
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE '!Desc:%-' || (
                SELECT RIN
                FROM xGroupIDTable
                ) || '.'
        );
 
-- Delete all members of the named group
DELETE
FROM GroupTable
WHERE GroupID = (
        SELECT GroupID
        FROM xGroupIDTable
        );
 
-- Add members to the named group
INSERT INTO GroupTable
SELECT NULL
    ,(
        SELECT GroupID
        FROM xGroupIDTable
        )
    ,DescendantID AS StartID
    ,DescendantID AS EndID
FROM (
WITH RECURSIVE
  child_of(ParentID, ChildID) AS
    (SELECT PersonID, ChildTable.ChildID FROM PersonTable
       LEFT JOIN FamilyTable ON PersonID=FatherID
       LEFT JOIN ChildTable USING(FamilyID)
       WHERE
         CASE $BirthOnly(YN)
         WHEN 'Y' OR 'y' THEN RelFather=0
         ELSE 1
         END
         --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
     UNION
     SELECT PersonID, ChildTable.ChildID FROM PersonTable
       LEFT JOIN FamilyTable ON PersonID=MotherID
       LEFT JOIN ChildTable USING(FamilyID)
       WHERE
         CASE $BirthOnly(YN)
         WHEN 'Y' OR 'y' THEN RelMother=0
         ELSE 1
         END
         --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)
     ),
  descendant_of_person(DescendantID) AS
    (SELECT ChildID FROM child_of
       WHERE ParentID=(
                    SELECT RIN
                    FROM xGroupIDTable
                    ) --RIN of starting person entered at runtime
     UNION --ALL
     SELECT ChildID FROM child_of
       INNER JOIN descendant_of_person ON ParentID = DescendantID)
SELECT DescendantID FROM descendant_of_person, PersonTable
 WHERE descendant_of_person.DescendantID=PersonTable.PersonID
);

Group – Ancestors #ancestors #namedgroup #recursive

Posted on by Tom Holden

This query supersedes Ancestors Named Group which was limited to 12 generations due to its dependence on the non-recursive Ancestors Query. This new query is unlimited as it is based on Recursive Queries – Ancestors and Descendants which exploit capability introduced to SQLite in Feb 2014. When run, you will be prompted for the Record Number (RIN) of the person whose ancestors are be in the group and for the type of ancestral relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).

Group-Ancestors.sql

-- Group-Ancestors.sql
/*
2014-04-15 Tom Holden ve3meo
Creates and updates a named group of ancestors of a person using an unlimited
recursive tree climb. Does not include starting person in the group. An example
of an auto-generated group name is "!Anc: Holden, Robert Alexander-155." but the
query will update any group with a name beginning "!Anc:" followed by anything
to "-nn.", where nn is the RIN number followed by the period character.
 
Requires support for SQLite 3.8.3 or later and named parameters.
Developed and tested with SQLite Expert Personal 3.5.36.2456
*/
-- Register the RIN of the starting person for following queries
-- to preclude repeated entry
DROP TABLE
 
IF EXISTS xGroupIDTable;
    CREATE TEMP TABLE
 
IF NOT EXISTS xGroupIDTable AS
    SELECT @StartRIN AS RIN
        ,-- named parameter is prompted for user entry
        NULL AS GroupID -- to be filled in after finding or creating the group label
        ;
 
-- Create Named Group if it does not exist '!Anc: Surname, Given-RIN.'
INSERT
    OR IGNORE
INTO LabelTable
VALUES (
    (
        SELECT LabelID
        FROM LabelTable
        WHERE LabelName LIKE '!Anc:%-' || (
                SELECT RIN
                FROM xGroupIDTable
                ) || '.'
        )
    ,0
    ,(
        SELECT ifnull(MAX(LabelValue), 0) + 1
        FROM LabelTable
        ) -- ifnull() needed if LabelTable is empty
    ,'!Anc: ' || (
        SELECT Surname || ', ' || Given
        FROM NameTable
        WHERE OwnerID = (
                SELECT RIN
                FROM xGroupIDTable
                )
            AND + IsPrimary
        ) || '-' || (
        SELECT RIN
        FROM xGroupIDTable
        ) || '.'
    ,'SQLite query'
    );
 
-- register GroupId in the temp table for following queries
UPDATE xGroupIDTable
SET GroupID = (
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE '!Anc:%-' || (
                SELECT RIN
                FROM xGroupIDTable
                ) || '.'
        );
 
-- Delete all members of the named group
DELETE
FROM GroupTable
WHERE GroupID = (
        SELECT GroupID
        FROM xGroupIDTable
        );
 
-- Add members to the named group
INSERT INTO GroupTable
SELECT NULL
    ,(
        SELECT GroupID
        FROM xGroupIDTable
        )
    ,AncestorID AS StartID
    ,AncestorID AS EndID
FROM (
    WITH RECURSIVE parent_of(ChildID, ParentID) AS (
            SELECT PersonID
                ,FatherID AS ParentID
            FROM PersonTable
            LEFT JOIN ChildTable ON PersonID = ChildTable.ChildID
            LEFT JOIN FamilyTable USING (FamilyID)
            WHERE CASE $BirthOnly(YN)
                    WHEN 'Y'
                        OR 'y'
                        THEN RelFather = 0
                    ELSE 1
                    END
            --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
 
            UNION
 
            SELECT PersonID
                ,MotherID AS ParentID
            FROM PersonTable
            LEFT JOIN ChildTable ON PersonID = ChildTable.ChildID
            LEFT JOIN FamilyTable USING (FamilyID)
            WHERE CASE $BirthOnly(YN)
                    WHEN 'Y'
                        OR 'y'
                        THEN RelMother = 0
                    ELSE 1
                    END
                --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)
            )
        ,ancestor_of_person(AncestorID) AS (
            SELECT ParentID
            FROM parent_of
            WHERE ChildID = (
                    SELECT RIN
                    FROM xGroupIDTable
                    ) --enter RIN of starting person at runtime
 
            UNION --ALL
 
            SELECT ParentID
            FROM parent_of
            INNER JOIN ancestor_of_person ON ChildID = AncestorID
            )
    SELECT AncestorID
    FROM ancestor_of_person
        ,PersonTable
    WHERE ancestor_of_person.AncestorID = PersonTable.PersonID
    );

Ancestors Query – Recursive #recursive

Posted on by Tom Holden

The result of this query is a list of the Record Numbers of the birth or bloodline ancestors of a given person. Not very useful by itself, it is a first effort at a recursive query exploiting the WITH RECURSIVE syntax and common-table-expressions support introduced in SQLite 3.8.3 2014-02-03. The earlier RecursiveAncestors.sql

-- RecursiveAncestors.sql
/*
2014-04-13 Tom Holden ve3meo
 
Generates the list of RINs for the ancestors of a person.
 
Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
*/
 
/* modelled on http://www.sqlite.org/lang_with.html example:
 
The next example uses two common table expressions in a single WITH clause. The following table records a family tree:
 
CREATE TABLE family(
  name TEXT PRIMARY KEY,
  mom TEXT REFERENCES family,
  dad TEXT REFERENCES family,
  born DATETIME,
  died DATETIME, -- NULL if still alive
  -- other content
);
The "family" table is similar to the earlier "org" table except that now there are two parents to each member. We want to know all living ancestors of Alice, from oldest to youngest. An ordinary common table expression, "parent_of", is defined first. That ordinary CTE is a view that can be used to find all parents of any individual. That ordinary CTE is then used in the "ancestor_of_alice" recursive CTE. The recursive CTE is then used in the final query:
 
WITH RECURSIVE
  parent_of(name, parent) AS
    (SELECT name, mom FROM family UNION SELECT name, dad FROM family),
  ancestor_of_alice(name) AS
    (SELECT parent FROM parent_of WHERE name='Alice'
     UNION ALL
     SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
 WHERE ancestor_of_alice.name=family.name
   AND died IS NULL
 ORDER BY born;
 
*/
 
WITH RECURSIVE
  parent_of(ChildID, ParentID) AS
    (SELECT PersonID, FatherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
       WHERE RelFather=0 --birth father (remove WHERE constraint to include all relationships)
     UNION
     SELECT PersonID, MotherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
       WHERE RelMother=0 --birth mother (remove WHERE constraint to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of
       WHERE ChildID=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ParentID FROM parent_of
       INNER JOIN ancestor_of_person ON ChildID = AncestorID)
SELECT AncestorID FROM ancestor_of_person, PersonTable
 WHERE ancestor_of_person.AncestorID=PersonTable.PersonID
;

Don’t ask me how it works – I’m not sure…

 

Ancestors Named Group #namedgroup #pedigree

Posted on by Tom Holden

Intro

RootsMagic 4 users have been seeking an improvement to Named Groups, namely "dynamic refresh" or, at least, a manual refresh button. The idea is that a group’s membership should be updated on demand following the rules that set it up in the first place. Currently, a group is static, requiring the user to edit the group, setting up the rules all over again.This query provides a manual refresh for a specific group, i.e., the ancestors of a specified person. While not the most challenging criterion for group membership and thus not a big time-saver, nonetheless it demonstrates the principle upon which more complex rules could be based. AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.

Download

AncestorsGroup.sql
NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST

Usage

  1. Backup your database before you do this every time until you are confident that it does no harm.
  2. In RM4, create a named group having the words "ancestors" and "#RIN" in it, where RIN is the number of the person whose ancestors are to be in the group, e.g., "Tom’s ancestors #269". Do not leave this group empty, add anyone immediately or do the next step before creating any more new groups (bug in RM4).
  3. Open your RM4 database with SQLite Expert Personal and load AncestorsGroup.sql into the SQL editor.
  4. Execute the query (F5)
  5. You will be prompted to enter a value for @StartRIN: enter the number of the person in the name of the group, in this example "269" (without quotes and the leading #).
  6. When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.

Discussion

The RM4 GroupTable allows for group members to be defined in ranges of consecutive numbers between a StartID and EndID, thus shortening the table versus one row per member. This script makes no attempt to use this capability so it consumes one row per member with EndID=StartID. I don’t know if there is any significant efficiency gained as a result of ranges.

Discussions & comments from Wikispaces site


ve3meo

Comment: “AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.‍”

ve3meo
03 September 2018 19:34:25

ve3meo Apr 13, 2014

This query would benefit from Ancestors+Query+-+Recursive . Unlimited number of generations and a much shorter, faster query.

Citations – Delete Duplicates #citations #sources #duplicates

Posted on by Tom Holden

In RootsMagic 4 (and maybe still in RM6), merging databases having common people with identical citations has resulted in duplicate citations for the merged person or couple and their events. These are counted in RM’s database properties report but not flagged as a problem and may not be detected until running a report or editing a person. An early SQLite query provides a more comprehensive report – see the page RMGC_Properties – Query and/or the RMtrix program. Another early query generates a spreadsheet report of all citations with a column counting duplicates – see All Citations & Dupes Count – Query, from which one could work to locate all instances and manually delete them one at a time. In March, 2014, RootsMagic Forums user Vyger posted under a 2010 topic on anomalies from merging that he was faced with a large number of duplicate citations and no easy way to get rid of them – pick up the the discussion here. He was sent the script below and reported that it successfully cleaned out all 916 duplicates with no apparent side effects. As this script may be useful to others, it is posted for the first time here, four years after its initial development.

Caveat Emptor: the script was written at the time of RM4 and does not check for differences between webtags for citations, a feature introduced later, and some other long-established fields, e.g. Quality. The Media system also changed from RM4 so the script may not handle differences in citation media tags correctly. Therefore, be sure to run this on a copy of your database, not your master, and review carefully before adopting the resulting database to go forward.

Citations-Dupes-Delete.sql

-- Citations-Dupes-Delete.sql
-- 2010-02-13 ve3meo
-- Deletes duplicate citations for each person, family and events
-- Uses LEFT OUTER JOINS to include the most orphaned citations
--     Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
--     and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
--     reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
--     Requires a temp table because of speed degradation when incorporated in main selects;
--     filtering can be done on screen in SQLiteDeveloper.
--
--     QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
--      managers merely say BLOB.
-- rev 2010-12-11
--  align with RMGC_Properties procedure; prev version was too loose.
--
-- BEGIN
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations
;
CREATE TEMP TABLE tmpCitations (CITID INTEGER, SrcID INTEGER, RIN INTEGER, Uniq INTEGER, RinCitation TEXT);
 
INSERT INTO tmpCitations
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || 'Personal' || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)
  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Couple citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=1 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- Citations for Alternate Names
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  n.OwnerID || 'Alternate Name' || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=7 AND +n.IsPrimary=0
-- GROUP BY RinCitation
ORDER BY rincitation
;
 
-- Delete Duplicates
DELETE FROM CitationTable
 WHERE CitationID IN
  (
   SELECT c.CitID
    FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
    ORDER BY c.CitID
   )
;
 
-- Count duplicates
--SELECT '- Duplicate Citations', COUNT(1), 'identical in most respects, cluttering reports'
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;
 
-- List Duplicates
--SELECT c.CitID
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;

Group – Unconnected Persons #namedgroup

Posted on by Tom Holden

Problem

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.

sample data.rmgc

A Solution

This approach involves two steps of development:

  1. Script to store the RIN (PersonID) of these so-called “Orphans” in a temporary table
  2. 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


ve3meo

Comment: “keep track of who is still orphaned”

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

Facts – Reference Numbers for person, spouse and parents. #facttypes #events #refno

Posted on by Tom Holden

Purpose

This page responds to a request posted in the RootsMagic Forums by Forum member JoyceAE5 on 21 Jan 2014.

Basically, what I want is a list of everyone in my database with the following information:
 
Person's RefNo, Person's Name, Spouse's RefNo, Spouse's Name, Father's RefNo, Father's Name, Mother's RefNo & Mother's Name

Building Blocks

List of RINs and Names

It’s easy enough to get every person’s RIN and Name:

SELECT Per.PersonID AS "RIN"
    ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.Given AS
    "Name"
FROM PersonTable Per
INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
    AND +Nam.IsPrimary -- excludes Alternate Names;

Sample results: 

RIN Name
...
286 CLENDENON , Magdalena
287 HOLDEN , Abigal
288 HOLDEN , Barbery
289 MCDONALD , Samuel Charles
...

List of RINs and RefNos

What about each Person’s RefNo value?

SELECT Per.PersonID AS "RIN"
    ,CAST(Evt.Details AS TEXT) AS "RefNo"
FROM PersonTable Per
LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
    AND Evt.EventType = 35 -- the FactTypeID for the Reference Number fact is 35
    AND Evt.OwnerType = 0
    -- restricts to events for individuals; redundant in this case because the FactType is so restricted
    ;

which gives:

RIN    RefNo
...
328    Groves340
329    Groves284
329    HLAF139
330
...

Note that RIN 329 has two RefNo and 330 has none. If many persons have two or more RefNos, the number of combinations reported will go up exponentially; if a person, her spouse and her parents all have two RefNos, that person will be listed 16 times (2^4).

Combine two lists into a lookup table

Since we want the RefNo and Name for each Person, his spouse and parents, it would be most efficient to combine these results in a single lookup table so that the processing need not be repeated for each person and these relatives. That’s achieved by JOINing the two queries on the common field, RIN and storing a query of the results to a temporary table:

DROP TABLE IF EXISTS xNamesRefNoTable;
 
CREATE TEMP TABLE IF NOT EXISTS xNamesRefNoTable AS
    SELECT *
    FROM (
        SELECT Per.PersonID AS RIN
            ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.
            Given || ' -' || Per.PersonID AS NAME
        FROM PersonTable Per
        INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
            AND + Nam.IsPrimary -- excludes Alternate Names;
        ) NATURAL
    INNER JOIN (
        SELECT Per.PersonID AS RIN
            ,CAST(Evt.Details AS TEXT) AS RefNo
        FROM PersonTable Per
        LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
            AND Evt.EventType = 35
            -- the FactTypeID for the Reference Number fact is 35
            AND Evt.OwnerType = 0
            -- restricts to events for individuals; redundant in this case because the FactType is so restricted
        );

Sample from this table, displayed as tab-delimited:

RIN    NAME    RefNo
66    FITCHETT Sr., U.E.,  Joseph -66    Groves073
66    FITCHETT Sr., U.E.,  Joseph -66    HLAF337
67    MCARTHUR ,  Nancy Ann -67    Groves074
68    FITCHETT ,  Dennis -68    Groves075
68    FITCHETT ,  Dennis -68    HLAF361

Note that the RIN has also been appended to the Name for convenience.

Report Query

Now to put together the Person, Spouse and Parents. I’m leaping to a complete report query that uses the temporary table created above, rather than building it up piece-meal. If you examine it from the inside out, you will see how it builds up a query listing the RINs of the person, spouse, father and mother. Then the temporary table is looked up from each RIN to retrieve the corresponding RefNo and Name. 

SELECT RINS.RIN
    ,Person.[RefNo]
    ,Person.[NAME]
    ,Spouse.[RefNo]
    ,Spouse.[NAME]
    ,Father.[RefNo]
    ,Father.[Name]
    ,Mother.[RefNo]
    ,Mother.NAME
FROM (
    SELECT Pert.PersonID AS RIN
        ,Spouses.SpouseID
        ,Parents.[FatherID]
        ,Parents.MotherID
    FROM PersonTable Pert
    LEFT JOIN (
        -- Get RIN of Spouse (MotherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[MotherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[FatherID]
 
        UNION
 
        -- Get RIN of Spouse (FatherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[MotherID]
        ) AS Spouses ON Pert.[PersonID] = Spouses.RIN
    LEFT JOIN (
        -- Get RINs of Parents
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID]
            ,Fam.[MotherID]
        FROM PersonTable Per
        LEFT JOIN ChildTable Child ON Per.PersonID = Child.[ChildID]
        INNER JOIN FamilyTable Fam USING (FamilyID)
        ) AS Parents ON Pert.[PersonID] = Parents.RIN
    ) AS RINS NATURAL
INNER JOIN xNamesRefNoTable AS Person
LEFT JOIN xNamesRefNoTable AS Spouse ON RINS.SpouseID = Spouse.[RIN]
LEFT JOIN xNamesRefNoTable AS Father ON FatherID = Father.[RIN]
LEFT JOIN xNamesRefNoTable AS Mother ON MotherID = Mother.[RIN];

Sample output

RINS.RINPerson.[RefNo]Person.[NAME]Spouse.[RefNo]Spouse.[NAME]Father.[RefNo]Father.[Name]Mother.[RefNo]Mother.Name
917HLAF061HARTLEY , Florence -917HLAF102BLAKESTON , Sidney -918HLAF039HARTLEY , Rev. George -875HLAF040COWIESON , Annetta Jane -876
918HLAF102BLAKESTON , Sidney -918HLAF061HARTLEY , Florence -917
919HLAF103BLAKESTON , Zella -919HLAF102BLAKESTON , Sidney -918HLAF061HARTLEY , Florence -917

Download Final Script

The whole script combining all the queries can be run in one fell swoop. Download it here: Facts-RefNos_person_spouse_parents.sql