TMG-RM Convert TMG_ID to Record Number #tmg #rin

Posted on 2014-10-082019-01-27 by Tom Holden

Why?

RootsMagic 6.3.3.2+ imports the TMG_ID into the standard RootsMagic Reference No. fact type (REFN). While this fact type can be displayed after the name of the person in main views and reports, it cannot be displayed in the sidebar Index and in some other places. Some users would prefer that it did; the only number that does is the Record Number (RIN), which is also the fastest search mechanism in RootsMagic Explorer.

How?

This procedure substitutes the TMG_ID value from REFN for all references to PersonID or RIN, which can optionally be displayed after the name. RIN is the most universal ID used across RootsMagic displays and outputs. However, it is volatile when transferred or imported into other databases, unlike the Reference Number fact or the TMG_ID fact formerly created with imports using 6.3.3.1.

Update History

2014-10-08 revised to support imports using RootsMagic 6.3.3.2; no longer supports earlier versions.
2014-09-24 handles single or small number of changes with proportional time; previous version was only suited to changing all; logs potential conflicts and
warnings in temp table xRIN_TMGIDlog which should be opened if the trial query aborts in order to find and resolve the cause of the error; logs a warning if
multiple TMG_ID facts for same person. Log displayed if script completes without an abort.

N.B. Be sure to close the database from RM before running this procedure

Many tables are changed so the procedure may take quite a while on large databases.While speed optimisation has not been a focus,
a 5000 person database with 15000 events and 11000 citations was operated on for 32 seconds to change 98% of the records, 2 seconds to change a few.
TMG-RM_convertTMG_IDtoRIN.sql

Surplus Reference No. Facts

Now that the TMG_ID is in the RootsMagic RIN, there seems little reason to preserve the Reference Number facts. Fortunately, the import placed TMG Reference tags into a custom RootsMagic fact type “TMG_REF” and added only one Reference No. (REFN) fact to each person to hold the TMG_ID. So a simple SQLite statement can get rid of them all:

DELETE FROM EventTable WHERE EventType = 35 ;

Convert TMG_REF to REFN?

Now that it is no longer necessary to preserve the TMG_ID in REFN or to permit only that REFN for each person for the script to be successful, do we want to keep this custom fact type. Were it exported from TMG to GEDCOM, it would be with the REFN tag. There is probably some logic that it should go to that tag from RootsMagic as well. Moreover, some users may want to optionally display the Reference value after the person Name. Both of those functions are supported if the TMG_REF type facts are converted to REFN facts. A simple SQLite statement can do so:

UPDATE EventTable
SET EventType = 35
WHERE EventType =
(
  SELECT FactTypeID
  FROM FactTypeTable
  WHERE ABBREV LIKE 'TMG_REF'
)
;

Then you can delete TMG_REF from the Fact Type List.

TMG-RM Fact Sentence Tweaks #tmg #facttypes #roles #events #sharedevent

Posted on 2014-09-282019-01-27 by Tom Holden

After importing a TMG project, there is considerable cleanup to be done in the resulting RootsMagic database. There are differences in their respective sentence template languages that cannot translate well or could be translated better (improvement possible in the direct import process). This script addresses some of these outcomes and some unwanted artifacts that could have more to do with the TMG user’s style in customizing sentences in TMG. There are very likely more common problems that could be ameliorated by extensions to the script; post suggestions as comments on this page.

There are limits to what can be done within SQLite because it lacks regular expression search and replace within a field. A higher level language using a SQLite connection is required to go beyond SQLite’s own limitations. This script does use the REGEXP extension to find records having a field value that matches a regular expression but the REPLACE() function does not support regex for its search and replace, not even a wild card character; only explicit strings can be used as parameters.

The script processes all sentences associated with facts/events in RootsMagic:

the default fact type sentence for the principal;
the custom override sentence for the principal(s) for each fact/event;
the default role sentence for each sharer role of a shared fact type;
the custom override sentence for each sharer (witness) of a fact/event.

The functions performed include:

adds [PlaceDetails] to sentences having only [Place] (Direct import translated [L] to [Place])
adds :plain modifier to [Date], [Place], [PlaceDetails] if preceded by a common preposition to correct, e.g., “of in”; RootsMagic prepends a default preposition if the :plain modifier is not present.
reverses order in switch “<was|and [Spouse] were>” which RM expands to “was” regardless; this first part is considered true in the absence of a variable in it.
deletes “[L=English]” (revise the script to cover other unsuppressed language statements).
relaces “<|” with “< ” (RM would always output nothing; the first part is true in the absence of a variable)
juggles some instances of space characters surrounding switches to correct spacing. This covers some patterns; there are others it does not.

TMG-RM_Sentence_Tweaks.sql N.B. Requires a SQLite manager such as SQLiteSpy that supports the REGEXP extension; SQLite Expert Personal does not.

The script is not restricted solely to be used on databases imported from TMG. It may help with other databases, too. The caveat is that it, too, may have some unwanted results so it is important to keep the original and compare it to the tweaked one to conclude whether the benefits outweigh the collateral damage.

Events – Move Short Note to Description #events #tmg #notes

Posted on 2014-09-252019-01-27 by Tom Holden

Do you have a lot of empty Description fields for facts or events that you would like to fill with short contents from the corresponding Note field? This might be the case for certain fact types after an import from TMG, directly or via GEDCOM. Or maybe you have chosen to enable the Description field for a fact type after adding many with sentence-useful content in the Notes. This script may help you with that chore.

The query moves content from Event Note to empty but enabled Event Description if the note is 100 chars or less for all such events of a selected Fact Type. To select a Fact Type, you must use a SQLite manager that supports SQLite run-time variables, such as SQLite Expert Personal (SQLiteSpy does not). When the query is run, a box will pop out prompting for the entry of the Fact Type’s abbreviation. This is the label as seen in the Edit Person screen. By entering the wildcard character (%), all Fact Types having the Description enabled will be processed.

EventNoteToDescription-Move.sql

Some TMG users exploit its split-Memo capability in which the text is sub-divided by the “||” separator. It may be that a variant of this query would move only the first part (M1) to the Description field provided it is 100 characters or less.

Conversely, given that imported Descriptions may contain the separator, a query that would move everything after it from Description to Note might be useful.

Comments?

Discussions & comments from Wikispaces site

moving only part, based upon separator, would be awesome!

robertjm
25 December 2015 21:07:42

I’d love to be able to move only a part of the Note to different fields. When I first used TMG I made copious use of the Note field, with separators. Now, I’m finding I want to move stuff around. But, I want to be particular of what I move, and where. Having a rudimentary understanding of Sqllite, I’m not able to “role my own” right now.

ve3meo

ve3meo
25 December 2015 21:18:34

To be able to programmatically extract something from the Note field requires that it be delineated systematically.

Notes Invisible Revealed #notes #tmg #gedcom

Posted on 2014-08-232019-01-27 by Tom Holden

GEDCOM files imported from The Master Genealogist and probably other sources may exhibit disappearance of citations and notes for tags in the TMG Name Group. Citations and memos for tags from the Name group that export to the GEDCOM NAME tag will disappear from those that are the first or only NAME tag imported by RootsMagic. They are in the database – just in places you cannot get at through the current user interface. The loss of citations from GEDCOMs of other origins was addressed with SQLite queries in Citations Invisible Revealed. This page provides queries to address the Notes issue.

Notes, Invisible – List.sql Lists the persons and their invisible notes.

Notes, Invisible – Convert to General.sql Appends the invisible note from the primary name to the general note for the person and deletes the first.

Ancestry.com Sync #ancestrycom #gedcom #ftm2014

Posted on 2014-07-222019-01-27 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 2014-06-152021-07-18 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

Group – Descendants #descendants #namedgroup #recursive

Posted on 2014-04-152019-01-27 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 2014-04-152019-01-27 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 2014-04-132019-01-19 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 2014-04-132019-01-19 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

Backup your database before you do this every time until you are confident that it does no harm.
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).
Open your RM4 database with SQLite Expert Personal and load AncestorsGroup.sql into the SQL editor.
Execute the query (F5)
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 #).
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.

Why?

How?

Update History

Surplus Reference No. Facts

Convert TMG_REF to REFN?

Discussions & comments from Wikispaces site

moving only part, based upon separator, would be awesome!

Under development

Summary

Upload RootsMagic GEDCOM to Ancestry.com

Download Ancestry.com GEDCOM to RootsMagic

Ancestry.com GEDCOM export/import shortcomings

Overview

Media Gallery

Source

Repository

Source Citation

Citation Information

ShareMerge Persons using RM’s UniqueID

Syncing Ancestry Sources via the Ancestry _APID Tag

Manually Adding _APID Tags for Ancestry Sources

A Trio of Templates

User-Editable Copies of Master Source Templates

Convert Sources With Uneditable Templates to Editable

List of Source Templates used and by what

Fix Extra Line Feeds in Footnote

Vital Records (state-level, online derivatives)

A Better Free Form Template

Discussions & comments from Wikispaces site

Attempted correction of existing databases

Intro

Download

Usage

Discussion

Discussions & comments from Wikispaces site

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