WebTags – from Ancestry.com and FTM #ancestrycom #webtags

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:

  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.

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.

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.

WebTags-MakeFromAncestryComments.sql RMtrix_tiny_check.png

-- 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://%'
;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.