Contents
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:
- “Find people with the same Ancestral File numbers (ignore all other information)”
- “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.
 |
| 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
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 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
06 September 2016 00:13:54
Hi Gina, use SQLiteSpy. It supports REGEXP.
Tom
Hallo Tom,
I realise this is a rather old post, but I need some clarity please. I have copied the FSID to the reference number as per your sql file and it works well. My question is if rootsmagic will merge all the records with the same changed reference number or must I still change them manually.
I find that although some records are merged, a lot remains with the same reference number but unmerged.
Regards
Herman du Plooy
Hello Herman,
RootsMagic won’t automatically merge them; you will have to do it manually.
I doubt if Duplicate Search Merge pays any attention to the REFN fact so it may not even help in finding likely duplicates. And I’m not sure if FamilySearch Central helps find duplicate people in your database matched to the same record in FamilySearch Family Tree – that would be independent of the REFN value.
A SQLite query could identify those with duplicate REFN. That would help you proceed with manual merging. I suppose a more complex query could also “merge” duplicates but that’s fraught with other issues such as which one is the primary person and what should be preserved from the secondary person.
Tom
Hallo Tom,
Thank you for the prompt reply. It look like the hard one by one merge is what I will have to do. I started off slow with linking my South African family Tree to my RootsMagic family Tree (you know here we are al related somehow). I became gready and started loading a progenitor and his direct line down into a data base for each and merge once there are a couple of these trees and then I started getting al the duplicates and Rootsmagic does not merge them all in the database on the computer. That is why I thought the FSPID in the REFN maybe the way to merge them all on my computer. I do not want to change any on FamilySearch just mearge them in their correct place in my database.
Regards
Herman