MediaTags – Copy Shared Facts Media To Sharee Personal #media #sharedevent

This script responds as a workaround to a 13 Dec 2011 wish expressed by MarkVS, that RootsMagic’s Media Album/Gallery and other Media dialog windows that show MediaTags should also show the indirect tags to shared events for the Persons having non-Principal roles, sometimes called “sharees” and the shared event in their context a “sharee event”. To do so can only be done by programming changes; one year later, nothing has changed. A workaround is to generate Personal mediatags from the shared event’s media for these non-Principal sharees. That’s what this script does.

Considerations:

1. Assuming the shared event’s mediatag is Primary for that event and Included in Scrapbook, so, too is each inherited mediatag for each sharee event. Therefore, the script generates a Primary-No, Scrapbook-No mediatag for the Person to avoid conflict with the truly Personal and other script-generated Personal mediatags and so that the Person Scrapbook does not receive duplicates.
2. These workaround mediatags probably should have some unique property that would distinguish them from regular mediatags and the indirect ones that may appear if and when RootsMagic is enhanced to display them. The current script does so, perhaps too subtly, by adding a sentence to the Comment field in the Media Tag dialog window (Description field in MediaLinkTable). There are other techniques that might be better suited for future script operations – e.g., an apparently unused Note field in MediaLinkTable or wrapping the sentence in some distinct string sequence such as the “{ … }” pairing used for distinctive privacy flags in Facts – Split Shared to Individual (note that they do not function as privacy flags in the MediaTag Comments/Description field because it is not outputted in any report anyway).
3. If someone uses Facts – Split Shared to Individual in combination with this script, there certainly will be real duplicate mediatags for each person, one is this workaround Personal tag, the other is the tag for the newly created Individual event. A procedure is needed to delete the duplicate Personal ones. It could rely on a more distinctive branding as discussed in 2) or simply look for basic matches with mediatags for events as in MediaTags – Delete Personal Having Fact Duplicates.

2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
Sentence in tag description reads “Shared in the …”.
2012-12-28 V1 complete – tags for both family and non-family shared events
2012-12-27 V0 creates mediatags for shared individual events, not for shared family events

MediaTags-CopySharedFactsMediaToShareePersonal.sql

-- MediaTags-CopySharedFactsMediaToShareePersonal.sql
/*
2012-12-27 Tom Holden ve3meo
2012-12-28 V1 complete
2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
           Sentence in tag description reads "Shared in the ...".
 
Creates a media tag, for media already tagged to a shared event,
 to the Persons sharing the event. The Description contains
 the original Description appended with the Person's role name,
 the event the Person shared in and the name(s) of the Principal(s)
 in the event.
 
Version 1 deals with tags for shared non-family and family events.
 
Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script.
*/
 
 
INSERT OR REPLACE INTO MediaLinkTable
 
-- tags for shared individual (non-family) events
SELECT
 NULL AS LinkID,
 MediaID,
 0 AS OwnerType,
 OwnerID,
 0 AS IsPrimary,
 0 AS Include1,
 0 AS Include2,
 0 AS Include3,
 0 AS Include4,
 0 AS SortOrder,
 0 AS RectLeft,
 0 AS RectTop,
 0 AS RectRight,
 0 AS RectBottom,
 '' AS Note,
 Caption,
 RefNumber,
 DATE,
 SortDate,
 Description
FROM
(
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description
  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || N.Given  || ' '  || N.Surname  || '-'  || N.OwnerID
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 0 -- Person
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND ML.MediaID || '.' || W.PersonID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
 
UNION
-- tags for shared family events
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || Husb.Given  || ' '  || Husb.Surname  || '-'  || Husb.OwnerID  || ' & '  || Wife.Given  || ' '  || Wife.Surname  || '-'  || Wife.OwnerID
 
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 1 -- FamilyPerson
INNER JOIN FamilyTable Fam
 ON E.OwnerID = Fam.FamilyID
LEFT JOIN NameTable Husb
 ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary
LEFT JOIN NameTable Wife
 ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
--INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND W.PersonID || '.' || ML.MediaID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
)
 
;
 
-- USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above
;