WebTags – Consolidate #webtags #delete #citations

Rev 2023-02-23 added versions for #RM8

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.

RM8 Version

Pre-RM8 Version

WebTags-Consolidate.sql

WebTags-DeleteDuplicates.sql Use if WebTags-Consolidate is repeated or there is other evidence of many duplicate WebTags.

-- 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 ----
;