Using RM AncestryTable to link DNA matches with RM's WebTag

[rseiter]

This is in response to a discussion on the FB DNA Gedcom 4.0 group.
The idea is to use the RM 10 (or 9) AncestryTable to facilitate connecting a DNA match to the RM person corresponding to the Ancestry person linked to the match. This is based on visual inspection of the table and this data dictionary.
https://sqlitetoolsforrootsmagic.com/rm9-data-dictionary/

AncestryTable contains three relevant fields.
LinkType - integer, 0 indicates person, we are only concerned with those entries here
rmID - integer, for LinkType = 0 this is the PersonTable.PersonID
anID - formatted text string, for LinkType = 0 described as follows.
Person = [d]{12}:[d}{4}:[d]{9} - e.g.: 121212121212:1030:123456789
It appears that format is an Ancestry standard. Some discussion here.
https://www.wikitree.com/g2g/505503/what-does-amtid-stand-for
Expressed using the notation below that string can be read as (not sure what the 1030 is):
:1030:

The way I see to use this is working with the "View in tree" links Ancestry has for matches connected to the tree. Here is a sample.
https://www.ancestry.com/family-tree/tree//family?cfpid=&showMatches=true&sid=

After extracting the ids from that link we could combine them to form an anID. That would then be used to lookup the rmID in the AncestryTable. Which would then be used edit the RM person (e.g. add DNA kit information).

Thank you for considering this!

[alerum68]

This won't work as requested. It will not link to the person in the Roots Magic tree and your Ancestry tree. Instead it would try and connect to the ancestry tree where the original record resides, i.e. - Your matches tree, not your own. The most this could be used for would be to connect a URL record to each individual to take you to the match tree's website on Ancestry. Will change title to reflect this change.

[rseiter]

That is the reason for the intermediate step of mapping the anID to the rmID using the AncestryTable.
The rmID is the RootsMagic id you need to add the appropriate DNA_kit fact to.

I am working on a manual version of this now. The input is a table of DNA kits and anIDs which I generated using a modified version of this WikiTree spreadsheet (I have almost a thousand matches connected to my tree, but not all those RM people connect to me genealogically)
https://www.wikitree.com/g2g/1362851/ancestry-dna-match-table-maker-spreadsheet
My version is here if you want to take a look. The processing I added is SLOW so need to wait for it to complete.
https://docs.google.com/spreadsheets/d/1ankqL4dGxiGTs3Z4JXxKAgddt9kxFobONX_RgKyQYYM/edit?usp=sharing

Here is a sample. From my spreadsheet I extract the Kit ID and AMTID mapping.

Kit ID | AMTID
5E586B41-0396-4743-9803-CD1F67584052 | 432249265339:1030:173574589

Using that anID (aka AMTID) I lookup the rmID 1888 in AncestryTable. (this is my closest Ancestry match in RM who I have already added a DNA_kit fact to).

In my RM DB "DNA kit" has FactTypeID 1076. I assume this varies. Is the correct thing to do using the FactTypeTable to map "DNA kit" to the FactTypeID?

I can then use that FactTypeID and rmID (as EventType and OwnerID) to lookup the existing event in EventTable. There in Details I see the DNA kit I already added matching the mapping I showed above.

The hard part is automating all of the creation (e.g. making sure not to create duplicate facts).

[rseiter]

One thing I should clarify. This only works for a RM tree connected via TreeShare to the Ancestry tree where the DNA matches are connected (maybe that is our disconnect?). Logically this should really be done as part of TreeShare (which would need access to the DNA match info, I am guessing that is not available in the API they use?), but that is up to RM and Ancestry.

One issue I see is when I add DNA kit facts they do show up in TreeShare and I have to be sure not to sync them. Even if I make them private that only adds a lock icon during the sync and they are still visible if I sync by mistake.

Do you know if Charting Companion works with the RM10 style DNA matches? Or do we just use the "DNA kit" facts there as well?

P.S. Here is a post requesting RM add DNA events automatically.
https://community.rootsmagic.com/t/rm10-dna-capabilities/11004/39
Another request (with 6 upvotes ; )
https://community.rootsmagic.com/t/rm10-dna-capabilities/11004/4

[rseiter]

Hi Jason,

Just a quick followup. I ended up doing the proof of concept of this all of the way through and it seems to have worked. The process I used was cumbersome, but better than entering about a thousand DNA kits manually.

• Create modified WikiTree spreadsheet with DNA kit tree connection information from Ancestry.
• Extract relevant subset of that into a simpler CSV.
• Import the CSV as a table in my RM DB using SQLite DB browser.
• Added RMID field to that table using the Ancestry ID and AncestryTable (mapping discussed above).
• Inserted the rows of that table into the EventTable using RMID as the key and existing (non-kit) values as placeholders.
• Removed duplicates because I had already entered some of the kits.
• (should probably delete created table, but not done yet)

I have been able to do DNA Matrix charts with the modified RM tree. Though I have not been brave enough to do this on my primary tree yet ; )