REMATCH to FamilySearch ID in REFN fact #FamilySearch #refno

An earlier script copied the FamilySearch Family Tree ID into the Reference Number fact. One user uploaded a file to an Ancestry Member Tree where it was grown through online evidence-based research and then downloaded the GEDCOM back into RootsMagic. RootsMagic’s Auto-Match feature in FamilySearch Central failed to rematch all the previously matched people. However, the FSID was preserved in the REFN fact through this round trip so his wish was to regenerate the matches from the REFN value. This script does just that.

The script is written as the complement to the one at COPY familySearchID to REFN fact , that is, the REFN value is of the form “fsid: XXXX-XXX”. If your format is different, it will have to be revised.

At time of writing, we are perhaps just weeks away from the Ancestry TreeShare update to RootsMagic 7 which may result in a change to the LinkTable that could cause the script to be rendered invalid without being adapted. However, it also follows that there should also be even less reason for someone to move their database from RM through Ancestry and out again with the attendant loss of FS matches.

REFN-RematchFSID.sql

REFN-RematchFSID.sql-newinfo.PNG
RM’s FamilySearch Central will report there is “new information” for every one of the matches remade by the script.
REFN-RematchFSID.sql-LinkTable.PNG
The rematches in the LinkTable made by the script are like records 1 and 3 with the extVersion empty. After opening the FamilySearch Person Tools on one of the people with “new information”, the record is revised as per #2.

COPY FamilySearchID to REFN fact #refno #FamilySearch

Update 2023-11-22: #RM9 version

Problem Description

This script responds to a user’s problem posted on FaceBook: she had two databases with overlapping people in them. One was her master with people matched to FamilySearch; the other was developed independently using FamilySearch. Because the duplicated people were created independently, she cannot rely on the RootsMagic File>Compare Files tool to unambiguously pair them based on a common UID (RootsMagic’s hidden Universal Identification) because their UID’s are different. But they have been matched to the same FamilySearch persons and thus have the FamilySearch ID (FSID) in common. Unfortunately, Compare Files does not a high match make for duplicate FSIDs. That seems to be a shortcoming that should be addressed.

Duplicate Search Merge has two options:

  1. Find people with the same Ancestral File numbers (ignore all other information)”
  2. “Find people with the same reference numbers (ignore all other information)”

Wouldn’t it be nice if there was another option:

  • “Find people with the same FamilySearch ID (ignore all other information)”

Absent that third option, the user figured that if she could get the FSID into a Reference Number (Ref#) fact for each person, the databases could be combined and DSM with option 2 would reliably pair up the duplicate people. This should also be useful when people matched to FamilySearch get duplicated in a database through other avenues such as overlapping downloads from FamilySearch into the same database.

Getting the FSID into a Ref# fact is a laborious task if there are more than a few people to do. Moreover, it is complicated if the people have pre-existing Ref# facts for other purposes that must be preserved. Duplicate Search Merge compares only the first Ref# fact for a person to the first Ref# fact for each other person. Thus it requires this FSID Ref# fact to be the lowest record number of all the Ref# facts for a person in the EventTable.

Solution

Enter a SQLite script that addresses these issues enabling DSM to pair up matching FSID Ref# facts.

REFN_CopyFSID.sql.PNG
Sample of database after script has run to copy the FamilySearch ID into the first Ref# fact for each person for those persons having been matched to FamilySearch.

Download Script

REFN_CopyFSID.sql for RM versions before RM8

REFN_CopyFSID-RM9 for RM versions after RM7 (tested on a RM9 database) 2023-11-22

Description

Creates a Ref# fact containing the FamilySearch ID for each person in the database matched to a person on FamilySearch Family Tree in the format “fsid: XXXX-XXX”. This enables the Duplicate Search Merge (DSM) option “Find people with the same reference numbers (and ignore everything else)” to pair up people with the same FSID Ref#.

This script shuffles existing Ref# facts to follow the FSID Ref# facts it creates by copying the former out to a temp fable xRefnBak, deleting the originals from the EventTable and then appending them after the FSID Ref# facts are created.

The script also preserves and pushes to the front existing FSID Ref# facts for a person not currently matched in the database to Family Search, i.e., the FSID has been somehow lost or the fact was manually added. This feature is of uncertain value.

A temporary table xRefnBak is created by the script and is deleted when the SQLite manager closes the database.

Requirements & Caveats

Requires the REGEXP extension which is not included in all SQLite managers; SQLiteSpy does support it.

N.B.: the script assumes there are no sources, media, or any other element attached to the existing Ref# facts and makes no attempt to preserve those linkages nor to delete the records in the corresponding tables. It is possible that some will become erroneously attached to new FSID Ref# facts.

Discussions & comments from Wikispaces site


gualco

REGEXP error

gualco
05 September 2016 23:03:56

Thank you so much for this script!!!

I tried using it and got an error. I’m wondering if I am just using it incorrectly–does it need to be run in pieces? do I need a different SQL app? etc.

When I try to run this script, I get this error:

no such function: REGEXP.

This is what I did:
1. Open SQLite Expert Personal 4.2.0.693 (x86)
2. Open my RootsMagic DB file within SQLite
3. Click on the EventTable in the left sidebar
4. Click on the SQL tab and paste in the script
5. Click the “Execute SQL” button
6. Error pops up

Gina


ve3meo

ve3meo
06 September 2016 00:13:54

Hi Gina, use SQLiteSpy. It supports REGEXP.

Tom