Add TreeShared Citation Media as Fact Media
In response to a recent tool request, below is a script to add media to the event associated with each Citation Media item listed in the AncestryTable. Feedback on errors and improvements welcome.
I expect that everyone who uses TreeShare to download citations from Ancestry has at some point wished that media were automatically attached to the event as well as the citation. However, the fact that TreeShare doesn't check for duplicate citations outside of a single download session adds complexity. Given my limited SQL capabilities, this script seems best suited for use after the 1st Treeshare download from ancestry. Repeated use will result in multiple MediaLinkTable entries for the same media item. This becomes problematic if using the RM UI to clean up the extra media item(s). Also, while the script sets the Scrapbook flag to include, it does not set the Primary flag because more than one source can be (and often is) cited for a single fact. Finally, the script has only been tested with RM version 12.1.3 on a limited basis. The usual cautions about backing up in advance and/or using a test db apply.
INSERT INTO MediaLinkTable
SELECT
NULL AS LinkID,
ANC.rmID AS MediaID,
CL.OwnerType AS OwnerType,
CL.OwnerID AS OwnerID,
0 AS IsPrimary, -- Do not flag photo as Primary
1 AS Include1, -- Include in Scrapbook
0 AS Include2,
0 AS Include3,
0 AS Include4,
0 AS SortOrder,
0 AS RectLeft,
0 AS RectTop,
0 AS RectRight,
0 AS RectBottom,
'This tag added via a SQL script.' AS Comments, -- Comment is visible in the Edit Media Tag popup window
julianday('now') - 2415018.5 AS UTCModDate
FROM
AncestryTable ANC
Inner Join MediaLinkTable ML ON ANC.rmid=ML.MediaID AND ANC.LinkType=11 AND ML.OwnerType=4
Inner Join CitationLinkTable CL ON ML.OwnerID=CL.CitationID
WHERE CL.OwnerType=2
;