COPY FamilySearchID to REFN fact #refno #FamilySearch

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.

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

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.

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.

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


REGEXP error

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 (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



06 September 2016 00:13:54

Hi Gina, use SQLiteSpy. It supports REGEXP.


Leave a Reply