WebTags+-+from+Ancestry.com+and+FTM

A GEDCOM downloaded from Ancestry.com contains an URL in the Citation Comments pointing to the online source. Previously, in Ancestry.com and RootsMagic 5, I outlined a method to marry images automatically downloaded to a synchronised FamilyTreeMaker 2012 database with the citation URLs from the Ancestry GEDCOM in a common RootsMagic database. RootsMagic 6 introduced the WebTag which instantly links to online resources by passing to the default web browser the URL stored in the WebTag. WebTags can be added to any person, source, citation, place, or research log item. Wouldn't it be great to get those URLs from the Citation Comments into WebTags for the citations!

The following query does a good job at creating a citation WebTag for each citation that has an URL in its Comments, with limitations: These shortcomings can be largely or entirely overcome by high level programming, thus requiring an application to be developed or an extension created for SQLiteSpy.
 * 1) The comment must begin with "http://" else it will be skipped.
 * 2) If the comment contains more than the URL, the entire comment will go into the WebTag URL against which the browser will fail to connect; the URL will need to be edited.

The WebTag Name is taken from the citation's Source Name while the WebTag Note is taken from the citation's Research Notes.

A related query, WebTags - Consolidate, creates Person WebTags from Citation WebTags to consolidate all WebTags pertaining to a person under the WebTags button on the Edit Person Screen.



code format="sql" -- WebTags-MakeFromAncestryComments.sql /* 2012-12-10 Tom Holden ve3meo

Inspects for Citation Comments beginning with "http://" and generates a corresponding WebTag. GEDCOMs downloaded from Ancestry.com have this characteristic, typically followed by two linefeed-carriage returns that are stripped by this query.

Due to limitations of SQLite, should the Comment contain more than the trailing white space, the complete Comment is placed in the URL field and will almost certainly fail to open the web page. -- Make Citation webtags for Citation Comments starting with "http://" INSERT OR REPLACE INTO URLTable SELECT (SELECT LinkID FROM URLTable WHERE OwnerType=4 AND OwnerID=Cit.CitationID) AS LinkID, 4 AS OwnerType, Cit.CitationID AS OwnerID, 0 AS LinkType, Src.Name AS Name, SUBSTR(Cit.Comments,1, LENGTH(Cit.Comments)-4) AS URL, -- strip four bytes off the end, typ two pairs of CR-LF Cit.ActualText AS Note FROM CitationTable AS Cit INNER JOIN SourceTable AS Src USING (SourceID)

WHERE Cit.Comments LIKE 'http://%'

code