WebTags+-+Consolidate

Getting at a WebTag for a Citation from the Edit Person screen takes many clicks and loses the big picture. If Citation WebTags were also accessible from the WebTags button on the Edit Person screen (and, hopefully, we will soon see this button also on the TimeLine View and RootsMagic Explorer), it would be much faster and the full picture of the person maintained. Ideally, RootsMagic would automatically populate the WebTags dropdown list with the citation WebTags. Until this enhancement is provided, a workaround is to create a duplicate of the citation WebTags for the person whose facts are supported by the citations.

For further consideration, whether to translate WebTags for Research Items on the person to WebTags for the person.

Use if WebTags-Consolidate is repeated or there is other evidence of many duplicate WebTags. ||
 * [[file:WebTags-Consolidate.sql]] [[image:RMtrix_tiny_check.png link="Bundled Utilities - RMtrix"]] || [[file:WebTags-DeleteDuplicates.sql]] [[image:RMtrix_tiny_check.png link="Bundled Utilities - RMtrix"]]

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

Generates a WebTag for the Individual from all WebTags attached to citations of that Individual, the Family in which he/she is a spouse, all his/her events and alternate names. This results in ready access to all the person's WebTags from the button on the Edit Person screen. URL Owner Type (0 = Individual, 3 = Source, 4 = Citation, 5 = Place, 15 = Research Item) Citation Owner Type (0 = Personal, 1 = Family, 2 = Event, 7 = Alternate Name)

INSERT OR REPLACE INTO URLTable --- Citation WebTags -- Person for citations of Persons SELECT NULL AS LinkID, 0 AS OwnerType, Cit.OwnerID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 0 UNION -- Fathers for citations of families SELECT NULL AS LinkID, 0 AS OwnerType, Fam.FatherID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN FamilyTable AS Fam ON Cit.OwnerID = Fam.FamilyID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 1

UNION -- Mothers for citations of families SELECT NULL AS LinkID, 0 AS OwnerType, Fam.MotherID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN FamilyTable AS Fam ON Cit.OwnerID = Fam.FamilyID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 1

UNION -- Person for citations of individual events SELECT NULL AS LinkID, 0 AS OwnerType, Evt.OwnerID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN EventTable Evt ON Cit.OwnerID = Evt.EventID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 2 -- Event citation AND Evt.OwnerType = 0 -- Person

UNION -- Husband for citations of Family events SELECT NULL AS LinkID, 0 AS OwnerType, Fam.FatherID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN EventTable Evt ON Cit.OwnerID = Evt.EventID INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 2 -- Event citation AND Evt.OwnerType = 1 -- Family

UNION -- Wife for citations of Family events SELECT NULL AS LinkID, 0 AS OwnerType, Fam.MotherID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN EventTable Evt ON Cit.OwnerID = Evt.EventID INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 2 -- Event citation AND Evt.OwnerType = 1 -- Family

UNION -- Person for citations of Alternate Names SELECT NULL AS LinkID, 0 AS OwnerType, Nam.OwnerID AS OwnerID, 0 AS LinkType, URL.Name AS Name, URL.URL AS URL, URL.Note AS Note FROM URLTable URL INNER JOIN CitationTable AS Cit ON URL.OwnerID = Cit.CitationID INNER JOIN NameTable Nam ON Cit.OwnerID = Nam.NameID WHERE URL.OwnerType = 4 -- Citation webtag AND Cit.OwnerType = 7 -- Alt Name citation --- End of Citation WebTags code