Jump to content


Photo

Sync with Ancestry.com

Ancestry.com GEDCOM Merge SQLite

  • Please log in to reply
7 replies to this topic

#1 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5464 posts

Posted 06 November 2012 - 10:22 PM

I have been noodling over ways to improve my working between my Ancestry.com Family Trees and my RootsMagic databases. Made progress on downloading images and regaining the URLs to source citation pages plus improving the footnotes but, in the back of mind, there is this niggling question about what happens with subsequent imports of the same but revised or expanded Ancestry tree or overlapping branches from other trees I may build. I guess that's the core of the challenge in getting Family Search Family Trees going.

In a small way, I have succeeded in an experiment to get RootsMagic's automatic ShareMerge feature to work in conjunction with an Ancestry Family Tree so that changes and additions I make online will more accurately find their way back into my database. It's early in the trial but I'll throw it out for discussion.

RootsMagic assigns a UniqueID to each 'person' in its database. The actual value looks similar to '2A6784DDA70D43F1B5000A51906F073F2FE7', a hexadecimal string that has an extremely low probability of being duplicated for any other person in any other RootsMagic database on any computer. This value travels with the database 'person' when transferred to another database by drag and drop or by GEDCOM with RM-specific features included. It is the key to Share Merge:

If you send a copy of your database to a friend or relative (either in backup or GEDCOM format), and they make changes to the database and return it to you (in GEDCOM format), you can import that GEDCOM into your database, and use this option to automatically consolidate the changes.

So what if there is a way to get the UniqueID value into the Ancestry persons corresponding with persons in my database so that when I bring back the revised tree from Ancestry to RM, the latter won't mistake them for new people and open up that whole duplicate search/merge issue?

In my RM database, I created a custom Personal fact, RMID, Date: No, Place: No, Description: Yes, Include only GEDCOM and Lists, Sentence template: empty. Using SQLite, I added a RMID event to the EventTable for each person with the UniqueID for the person stored in the Details field (Description on the Edit Person screen). I did the same with the built-in REF# fact type. Then the database was exported to a plain GEDCOM (RM specific extra details unchecked).

This GEDCOM was uploaded to a new Ancestry Family Tree, where, to my surprise, the RMID custom fact appeared but the REF# fact did not. A note that I had included with each RMID fact gave rise to an "Unsourced citation" for each RMID fact. I made no changes to the Ancestry tree and downloaded its GEDCOM and imported same into a new database.

In this new database, the persons were assigned new UniqueIDs and therefore they cannot be ShareMerged when transferred to the original. Using SQLite, I replaced these new UniqueIDs with the RMID value for each person; now the persons in the database from the Ancestry download have the same UniqueID as the persons in the database uploaded to Ancestry.

I drag'd and drop'd from the Ancestry download database to the Ancestry upload database and ran the ShareMerge. Voila! Every pair of duplicate persons merged as did all the events, with the exception of ... RMID! Some more fiddling with its event values might correct its migration to not-exactly duplicate.

However, Sources are not without issues. The "Unsourced citation" created from the upload of the RMID Note resulted in a new source named "Source #n" for each person; again, I hope to obviate that with some tweaking of the RMID event. Worse is that the uploaded Sources come back different and won't automerge; there might not be any easy way around that but I have an idea... One impossibility I'm almost certain cannot be overcome is to get an online Ancestry source to return to Ancestry as an online source via GEDCOM through RootsMagic; they become user-generated sources in Ancestry with no links to the Census or other databases. Synchronisation a la Family Tree Maker 2012 is not on without an API - I'll bet that's where the _APID tag in the Ancestry GEDCOM comes into play.

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#2 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5464 posts

Posted 07 November 2012 - 04:07 PM

Thinking out loud after solving the minor problem that the RMID fact did not merge with the original after its trip to Ancestry and back, I have an idea on how to handle that the sources get transformed on the round trip so that they don't merge either - not even Free Form. What if the source citations get stamped with a code on the way out of RM that is preserved through Ancestry and thus makes its way back to RM. Then that code is a key to deleting the round-trip sources and citations. Any that are edited on Ancestry would need the code removed and any new sources added on Ancestry would not have the code and would escape the deletion-on-return.

Despite that many of my sources originate on Ancestry, once downloaded from Ancestry and then uploaded back again, they are no longer displayed on Ancestry as Ancestry sources but as Other sources and are not detected by the Hints or Searches as already attached to the person. But it's still useful to have them online as a check to see if a Hint or Search offers anything not previously found. I had thought of not exporting sources at all from RM but that could prove inconvenient if working on Ancestry away from a RM device. Even with the RM Reader on iPad, it's no fun switching between apps.

Where to stamp the code is probably a tougher question than what the code should be. Ancestry's GEDCOM is ANSI so that narrows the character set; the code needs to be short and unlikely to occur in citations, something like '#-@=' without the quotes. But where? In the GEDCOM PAGE tag is one thought but I find some citations without that tag which complicates the procedure. Moreover, there is a length limit on a GEDCOM line but if the code is prepended to the PAGE value, the possible loss of a few characters off the end is minor since the citation is thrown away later. It would be desirable to do it in the database with SQLite but then those darned Source Templates make for a challenge.

Any other ideas welcomed... :rolleyes:

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5464 posts

Posted 07 November 2012 - 05:33 PM

It would be desirable to do it in the database with SQLite but then those darned Source Templates make for a challenge.

Looks like both the Source Comments and Detail or Citation Comments make the round trip unscathed and are easy to diddle in the database. They aren't visible, though, on Ancestry except when editing the source or citation.

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#4 tomhoover

tomhoover

    New Member

  • Members
  • Pip
  • 1 posts

Posted 10 January 2013 - 11:10 AM

I'm brand new to the forum, having decided to switch to a "real" genealogy program (RM6). Previously, I've used ancestry.com to maintain my family tree. The initial GEDCOM export from ancestry.com, and import to RM6, was not a problem; however, my wife would prefer to continue the use of ancestry.com for those times she updates our family tree.

TomH (good name, BTW)...have you made further progress on your (lossless) method of syncing between ancestry.com and RM6?

Thanks!

Tom Hoover

#5 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5464 posts

Posted 10 January 2013 - 12:38 PM

I haven't closed the loop or the book on syncing between Ancestry and RM but admit to not having done anything more since November-ish. I really must get back to it because I had also frozen my R&D on my Ancestry tree until I had done as much as I could on the sync front. This work is not yet documented but other things I have done with Ancestry imports might be of interest - see Ancestry.com and RootsMagic 5

Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.


#6 sandykp

sandykp

    Member

  • Members
  • PipPip
  • 11 posts

Posted 08 March 2014 - 07:12 PM

Has any of this changed in RM6? I hope RM will come up with an easy conversion for those of us who don't understand the SQlite interface. We all know how many users use ancestry and need an easy fix to import to RM6. Thanks :)

#7 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 7093 posts

Posted 10 March 2014 - 09:06 AM

RootsMagic hasn't made any changes. We are really hoping that Ancestry will provide us with an API to talk directly to their site, like FamilySearch does with Family Tree. We need our users to continue to ask Ancestry to provide us with that tool.
Renee
RootsMagic

#8 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 5464 posts

Posted 08 July 2014 - 09:16 PM

I am dismayed that it is almost two years since I was plugging away at this, having reliably paid for my subscription, growing more trees on Ancestry and daunted by the implications of bringing them together in RM and then starting the cycle all over again. RasillonRegenerated got me back into this with his discovery of a way to carry Ancestry's database source references in an Ancestry Family Tree from its GEDCOM through RM to its export GEDCOM and back to a new Ancestry FT so that the same sources are not "hinted' all over again. See http://forums.rootsm...om-ancestrycom/ . Digging into the process again, some things may have changed since then or I missed seeing them.

 

As RR discovered for Ancestry's APID, I now see that 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 at the start of this thread. 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.  

 

Of course, there is a lot more to wrestle with in attempting to sync with Ancestry but APID and UID processing are two critical pieces of the puzzle!


Tom user of RM7230 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> Rmtrix_tiny.png app, a growing bundle of RootsMagic utilities.