Disconnect but preserve Ancestry Sources links for next Upload

Subsequent to this script being published 2017-07-21, RootsMagic 7.5 was updated so that its File Option > TreeShare > Disconnect from Ancestry Tree behaves the same way or similarly in that the links with Ancestry Sources are not deleted. RootsMagic 7 Update History is silent on when that happened.  The text below has been edited accordingly.

SUPERSEDED: TreeShare-DisconnectButNotCitations.sql 2017-07-21 rev to correctly handle erroneous connection to multiple Ancestry Trees.
This is an intriguing discovery that means one could make many changes on the RM side of TreeShare, disconnect with this script instead of TreeShare’s Disconnect, and then upload to a new tree. It has the labour saving effect that batch updating of changes would have, if it were available. TreeShare’s Disconnect no longer causes all linked sources seen on the AMT as “Ancestry Sources” to be converted to “Other Sources” in the next upload and hints are generated all over again for “Ancestry Sources”. This script’s partial disconnect precludes that from happening. So both one-by-one acceptance of changes and one by one clearing of redundant hints are avoided. The downside is that one should thus work on only one end at a time followed by the up or download. And media migration will be repeated over and over at the expense of network bandwidth quota, computer time and storage space. If the only media is that of “Ancestry Sources” on the AMT, then there is an option in TreeShare upload to exclude the media thus saving bandwidth in one direction.

LinkAncestryTable #RM7.5 renamed AncestryTable #RM8

While the RootsMagic 6 database structure has been preserved with the introduction of Ancestry TreeShare in RootsMagic 7.5, an Ancestry specific table has been added:

CREATE TABLE LinkAncestryTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, STATUS INTEGER, Note BLOB );
 
CREATE INDEX idxLinkAncestryExtId ON LinkAncestryTable (extID);
 
CREATE INDEX idxLinkAncestryRmId ON LinkAncestryTable (rmID);

For any modification made directly through SQLite to a database connected to an Ancestry Tree, TreeShare will not report that there has been a change unless the Modified field of the record in LinkAncestryTable corresponding to the modified person, event, citation, … has been set. Thanks to member alerum68 for flagging this issue.

Structure

LinkAncestryTable structure appears to be identical to that of LinkTable used with FamilySearchFamilyTree. While LinkTable anticipated connections with other online tree servers, RM Inc possibly chose to add a dedicated table for AMT TreeShare to simplify development and debugging or to allow interchange of RM 7.5 database files with prior versions down to RM6.

Field Usage

One download of an AFT indicates the usage of these key fields:
LinkType: 0 = PersonTable; 4 = CitationTable; 11 = MultiMediaTable
rmID: the record number or rowid of the linked table
extID: Ancestry’s key for access to its record used by RM according to LinkType…

IF LinkType = 0 THEN extID contains what I think is the unique key for the Ancestry Family Tree and Person and extVersion contains the Ancestry Universally Unique ID for the linked person in that tree
IF LinkType = 4 THEN extID contains the unique key to access the Ancestry source database record for the linked citation.
If LinkType = 11 THEN extID contains the unique key to access the Ancestry image file and MultiMediaTable.MediaFile = extID.extension (the media filename)

There may be more LinkTypes to be identified as this was a tree developed solely on Ancestry.

Temporary ADP File During TreeShare Download

I caught this because of an error message during a TreeShare download that could not complete. A temporary file of the name databasefilename.ADP was visible. While a binary file, some of the content is readable with a hex editor and contains a mix of SQLite statements and data about some people. Also while Windows associated the extension with Microsoft Access, it clearly has nothing to do with that program. Perhaps ADP stands for Ancestry Data|Download Procedure|Process|Progress. The ADP file is deleted on completion of the TreeShare download.

Transfer via Drag and Drop and Export

A drag’n’drop from a TreeShare database to a new database surprisingly preserved the Ancestry WebHints. The new database’s LinkAncestryTable was populated with LinkType=0 records for the persons transferred. The extID value was carried over but the extVersion value was blank. The extID value is also exported to the custom GEDCOM tag _AMTID which suggests that RM’s drag’n’drop transfer continues to rely solely on GEDCOM export/import in the background.

TreeShare connected to the same Ancestry Member Tree as the original (that, too, was a surprise) and executed a Compare Databases and reported no close matches nor any matches through the “Show All” link. However, the name list showed that the transferred people were paired with an AMT person; selecting them showed the green match for all facts but all citations were unmatched (pink). That follows from there having been no transfer of LinkAncestryTable records for any LinkType other than 0.

Citations for the Ancestry Name were now seen in the RM Person field whereas they disappeared from TreeShare on the downloaded database. It appears that the drag’n’drop moves these hidden citations from being Name citations to Person citations (see Citations Invisible Revealed for background).

LinkAncestryTable.extVersion field

Copying the extVersion value from the source database to the destination database had no apparent effect on the latter’s TreeShare results. It did not result in a reported match and had no effect on the sidelist icons – the RM icon remained pale green tree on gray background.

LinkAncestryTable.Modified field

Copying the Modified value from the source database to the destination database had no effect on the reported number of changes (0) but did invert and saturate the colours of the RM icon in the sidelist to white tree on darker green background.

From 2 TreeShare databases to 1

A drag’ndrop from a second TreeShare database connected to a different Ancestry Member Tree than the target database does not carry over any data from the source database LinkAncestryTable. Thus the Ancestry WebHints from the second database are unavailable to the target. The transferred persons are unmatched people that have to be manually Linked or Added to the Ancestry Tree to which the target database is connected. A fresh set of Ancestry Hints for these added people will eventually generate on the AMT and become available to the target database but will ignore any prior Accepts and Rejects in the source database. The ostensibly identical person is now in two different Ancestry Trees, effectively two unique persons in the Ancestry database.

Separate drag’n’drops of multiple sets of people from the same TreeShare database to a second database that has not had any such transfers from a different TreeShare database nor has been TreeShared with an Ancestry Family Tree other than the one connected to the source database do retain their links to the AMT people so their WebHints are immediately available.

Rename Downloaded Media Files

Because LinkAncestryTable contains the Ancestry key for its media file, one can rename a downloaded file from AncestryMediaKey.ext to something more meaningful without apparently affecting anything, provided that the MultiMediaTable itself has the new filename. The MultiMediaTable.filepath can also be changed if the file is moved without effect on TreeShare. This might suggest the possibility of some procedure renaming and reorganizing media files that have been downloaded through a TreeShare Tree download. See TreeShare – Rename Cryptic Filenames for Media

New Source Templates #RM7.5 #RM8

Two new Source Templates have been added in the RM7.5 update, for a total of 415 built-in templates (unchanged as of RM7.9.310):

1. TemplateID 438: Ancestry Member Tree
2. TemplateID 439: Ancestry Record

I’m not sure that the template for Ancestry Member Tree is complete because it lacks sentence templates for the Short Footnote and Bibliography. If it is used in a TreeShare download and if a user wishes to use the Short Footnote option or include cited Trees in the Bibliography of a report, they will not be included. The template cannot be edited so there is no user fix; a future RootsMagic update would be needed.

The template for Ancestry Record was used by all citations in a TreeShare download of an Ancestry Member Tree. It is identical to the Book, Basic format source template except it omits the [SubTitle] field. Thus it is very compatible with GEDCOM with a 1:1 relationship between its fields and the available standard GEDCOM fields. However, RootsMagic exports sources to just two standard GEDCOM fields, TITL and PAGE so there is potential distortion of footnotes when exported to other systems. A source downloaded through TreeShare, stored through the Ancestry Record template, and transferred to FamilySearch Family Tree will become a Free Form source when brought from FSFT into another RM database or back to the same database.

Script Revision

Because the only obvious database changes are the additions of the LinkAncestryTable and two more built-in source templates, prior scripts should function as they did before the update. However, those that modify or add records will not cause TreeShare to flag that a person’s RootsMagic records have changed. Differences between RM and the connected AMT are detected but there is no easy way to find them. It is desirable to set up SQLite triggers that detect a change to a table record and set the Modified flag for a person accordingly. Further investigation and development is needed.

The first script to be updated for RM 7.5 is to help a SQLite user see what the new table LinkAncestryTable relates to, just as it did for LinkTable used in conjunction with FamilySearch Family Tree. This revised Waymarks script is described on the page: Search – wayfinding from data tables to RM screens . RM7_5_WaymarksViews.sql

Correction of the Flawed Ancestry Record Source Template #RM7.5 #RM8

This template was added in RM7.5 to handle sources downloaded through TreeShare from Ancestry. Problems with the way it renders Footnotes and Bibliography for most of these sources were reported in the RootsMagic Forums discussion Plz correct configuration of Ancestry Record source template (lost or deleted before May 2019). It works fine for a conventional human name in the Author field but not for a blank name, a one-word name such as Ancestry.com nor an institutional name such as “The Church of Jesus Christ of Latter Day Saints”. There are unwanted commas or laughable reversed names.

Here’s a possible workaround using a SQLite query to modify the built-in Ancestry Record template. Yes, they can be edited through SQLite but the customisation is lost in a RootsMagic transfer.

Original sentences:

Revised sentences:

SQLite statement:

UPDATE SourceTemplateTable
SET Footnote =      '<i>[Title]</i> (<[PubPlace]|N.p.>: <[Publisher]|n.p.>, <[PubDate]|n.d.>)<, [Page]>.'
   ,ShortFootnote = '<i>[Title:Abbrev]</i><, [Page]>.'
   ,Bibliography =  '<i>[Title]</i>. <[PubPlace]|N.p.>: <[Publisher]|n.p.>, <[PubDate]|n.d.>.'
WHERE TemplateID = 439 -- Ancestry Record template
;

I eliminated the [Author] field completely to avoid the problem of parsing it correctly or the extraneous comma in its absence. I don’t know if this will work okay for all possible sources that Ancestry may deliver through TreeShare with this template. If you want to keep {Author] at risk of the “Saints, …” issue, then:

UPDATE SourceTemplateTable -- keeps [Author] but clears extraneous comma
SET Footnote      = '<[Author], ><i>[Title]</i> (<[PubPlace]|N.p.>: <[Publisher]|n.p.>, <[PubDate]|n.d.>)<, [Page]>.'
   ,ShortFootnote = '<[Author:Surname], |[Author], ><i>[Title:Abbrev]</i><, [Page]>.'
   ,Bibliography  = '<?[Author:Surname]|[Author:Reverse]. |[Author]. ><i>[Title]</i>. <[PubPlace]|N.p.>: <[Publisher]|n.p.>, <[PubDate]|n.d.>.'
WHERE TemplateID = 439 -- Ancestry Record template
;

Discussions & comments from Wikispaces site