This page looks into the challenges involved in converting shared events to individual ones and tries to come up with a solution using SQLite queries (also integrated in the friendly app RMtrix).

The Quandary of Shared Events

We are caught in a quandary between using shared events in RootsMagic to advantage in its management and reporting versus their incompatibility with most external software, especially through GEDCOM. Until RootsMagic comes up with a satisfactory splitting option on export, we seek other workarounds or avoidance:

1. Use shared events to the fullest extent but forego exporting to external software that may have advantages for certain kinds of analysis and reporting.
2. Forego using shared events so that you can more fully enjoy the benefits of exporting to external software.
3. Use shared events and after export apply MarkVS’ GEDCOM splitting function integrated in his GED Image Tracer. As of writing, it does a basic split but with flaws as a consequence of the inherent challenges that we will get into below.
4. Use shared events and after export import into David Knight’s iOS app GedView and save its GEDCOM for use with other software. As of ver 3.4.1: “Basic support added for shared events / facts as used by RootsMagic. GedView will load the event/fact and copy it to the individuals who share it.” I have yet to examine the results and it does require the iOS device.

Applications that import RootsMagic Shared Events

While most other applications have no support for shared events, a small number do preserve the data when importing from RootsMagic:

Application	Converts	Preserves	Description
GedSite	X		Generates website from GEDCOM
GedView	X		GEDCOM editor/viewer for iOS
Legacy Family Tree		X	Full family tree/genealogy software

Converts: converts shared events into individual ones
Preserves: converts RM shared events into application’s version of shared events.

The Challenges in Converting Shared to Individual Events

1. The unique data for the event itself for the Principal role is stored in EventTable and can remain unchanged.
2. Witnesses or sharees of the event are identified in the WitnessTable with their unique info: RoleID, custom sentence, Note.
3. Event data from the EventTable along with the sharee’s data from the WitnessTable and other supporting data from other tables need to be combined and entered as a new record in the EventTable for each sharee of an event.
4. Some Fact Types, such as Census, can be split into individual Census facts.
5. Most other Fact Types, such as Baptism, must not be split into individual Baptism facts; only the Principal was baptised. What to do with the sharees? Convert to the Miscellaneous fact type or some custom fact such as “Share”.
6. Do we need a sentence template for these Share facts, given that the objective is to export to third party software that does not use it? I hope nothing and maybe we have to suppress the sentence if we use a standard fact. No one sentence template will likely satisfy all fact types and roles. The default Census sentence template is decidedly unsuitable for the Census ‘share’ event created.
7. What do we put in the Description field of the Share fact? Probably the name(s) of the Principal(s), the name of his/her/their event type and the role of the sharee would be essential. Note the plural names if the shared event is a Family fact type. The default Census fact types do not use Description so will not show the householder’s(s’) name(s) nor the subject’s role.
8. We cannot invent new citations for the sharee event but can copy the citations for the Principal; they may need to be customised for the Share fact.
9. Some may want to operate on an intermediary database for export, eliminating all shared events with individual events.
10. Others may want to have both shared and individual events in their working database, using the indiv share events for export, the shared events for reports. That’s possible but keeping the Census and Fact types complicates things – their settings will have to be changed back and forth.

 

Meeting the Challenge

An almost complete solution

ToDo:

1. Family events
2. special Fact Types, e.g. Census, Residence that should keep the same type
3. citation media
4. auto create ‘Share’ Fact Type, if not existing, complete with a default sentence template
5. webtags
   1. fact don’t exist
   2. citation
6. research log?
7. cleanup temp used IsPrimary and Flagsfields
8. do something with Share events generated for non-Person witness no longer generated. Now appending non-Person Name, Role Note to shared event Note in easily found privacy brackets.
9. temp use of EventTable.IsPrimary and CitationTable.Flags
   1. don’t generate spurious events or citations
   2. stop misusing these fields
10. Consider coexistence of Shared events and ‘Share’ events – former for RM internal use, latter for GEDCOM – could split script into two, second part deletes the Sharers so running the first part only leaves both in place. Done
    1. Split: would leave Shared events as is and add Share events. User control of FactType output options and Private options might prove satisfactory.
    2. Unshare: would remove the non-Principals from the shared events
    3. Undo: would restore the non-Principals to shared events and delete the Share events and related citations, media tags and web tags
    4. Hide tracks: would drop the query created tables from the database
11. Should Name, Role of Persons who shared an event be appended to Note of once-shared event?
12. Describe the queries, how to use them, illustrate results

 

Scripts

Four scripts are provided for user choice (all four are integrated in the friendly app RMtrix and are compatible with RM 6 and 7 and likely with 7.5):

1. Facts-SplitSharedToIndiv.sql is to be run first and will create an Individual fact matching the shared fact for each non-Principal role. It should not be repeated.
2. Facts-Unshare.sql would be run next if it is desirable to eliminate shared facts from the database. The decision to do so is reversible, in the short term, at least. If the motivation for splitting was that 3rd party software did not recognise shared facts, it may not be necessary to unshare for the export.
3. Facts-Split-Undo.sql can be run to eliminate the events created by the first script and restore the shared facts deleted by the second, provided the following script has not been executed
4. Facts-Split-HideTracks.sql rids the database of the tables created by the first script;

Split Shared to Individual

Facts-SplitSharedToIndiv.sql
Facts-SplitSharedToIndiv-NoRIN.sql 2016-01-17 As above but does not include Principal’s RIN in the Description field in split events
Kim Mills has done a very clear, well illustrated explanation of how to use these functions in RMtrix to split shared events prior to exporting and uploading to Rootsweb and Ancestry.com trees. Read it on her Footsteps of the Past blog.

-- Facts-SplitSharedToIndiv.sql
-- 2012-12-19 Tom Holden ve3meo
/*
2012-12-21 rev. divided script into separate files.
 
Run this query to convert shared events to Individual 'Share' events,
except Census, Residence fact types whose Fact Name is preserved.
 
Creates a new Individual Fact Type named and abbreviated 'Share', without the quotes,
if none exists.
 
Witness not in File and their note is appended to the shared event note
and enclosed in privacy brackets, thus {** ... **}.
 
See end of script for steps to UNDO the changes
 
*/
 
-- DELETE FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share';
-- Create Share facttype if it does not exist
INSERT OR IGNORE INTO FactTypeTable
 
SELECT
  (
   SELECT FactTypeID FROM FactTypeTable
   WHERE LOWER(Abbrev) LIKE 'share'
   ) AS FactTypeID,
  0 AS OwnerType,
  'Share' AS Name,
  'Share' AS Abbrev,
  'EVEN' AS GedcomTag,
  1 AS UseValue,
  1 AS UseDate,
  1 AS UsePlace,
  '[person] was involved as a< [Desc]>< [Date]>< [PlaceDetails]>< [Place]>.' AS Sentence,
  -1 AS Flags
;
 
 
-- keep track of the current last key rowid's before the adding of the Share events
DROP TABLE IF EXISTS zTmpShareSplit
;
 
CREATE TABLE IF NOT EXISTS zTmpShareSplit
(TableName TEXT, Maxrowid INTEGER)
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'FactTypeTable',
  (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share')
;
 
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'EventTable', MAX(EventID) FROM EventTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'CitationTable', MAX(CitationID) FROM CitationTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'MediaLinkTable', MAX(LinkID) FROM MediaLinkTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'URLTable', MAX(LinkID) FROM URLTable
;
 
 
-- Split Shared non-family facts
INSERT INTO EventTable
 
SELECT
  NULL AS EventID,
  CASE E.EventType
    WHEN 18 THEN 18  -- Census to Census
    WHEN 311 THEN 18 -- Census (family) to Census
    WHEN 29 THEN 29  -- Residence to Residence
    WHEN 310 THEN 29 -- Residence (family) to Residence
    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share
  END
    AS EventType,
  E.OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID,
  E.DATE, E.SortDate,
  E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0
  E.IsPrivate, E.Proof, E.STATUS, E.EditDate,
  '' AS Sentence,
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details,
  W.Note AS Note
FROM
  WitnessTable W
  INNER JOIN EventTable E USING(EventID)
  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
  INNER JOIN RoleTable R ON W.ROLE = R.RoleID
  INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE E.OwnerType = 0 -- Person
AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree
;
 
-- Split Shared family facts
INSERT INTO EventTable
 
SELECT
  NULL AS EventID,
  CASE E.EventType
    WHEN 18 THEN 18  -- Census to Census
    WHEN 311 THEN 18 -- Census (family) to Census
    WHEN 29 THEN 29  -- Residence to Residence
    WHEN 310 THEN 29 -- Residence (family) to Residence
    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share
  END
    AS EventType,
  0 AS OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID,
  E.DATE, E.SortDate,
  E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0
  E.IsPrivate, E.Proof, E.STATUS, E.EditDate,
  '' AS Sentence,
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N1.Given || ' ' || N1.Surname || '-' || N1.OwnerID || ' & ' || N2.Given || ' ' || N2.Surname || '-' || N2.OwnerID AS Details,
  W.Note AS Note
FROM
  WitnessTable W
  INNER JOIN EventTable E USING(EventID)
  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
  INNER JOIN RoleTable R ON W.ROLE = R.RoleID
  INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID
  LEFT JOIN NameTable N1 ON F.FatherID = N1.OwnerID AND +N1.IsPrimary -- to get Husband Names
  LEFT JOIN NameTable N2 ON F.MotherID = N2.OwnerID AND +N2.IsPrimary -- to get Wife Names
WHERE E.OwnerType = 1 -- Family
AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree
;
 
-- Copy citation from shared event to Share events
INSERT INTO CitationTable
SELECT
  NULL AS CitationID,
  C.OwnerType,
  C.SourceID,
  E.EventID AS OwnerID,
  C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber,
  C.CitationID AS Flags, -- temp use of Flags to link media and webtags to new citations, set to 0 later
  C.FIELDS
FROM CitationTable C
INNER JOIN EventTable E ON C.OwnerID = E.IsPrimary
AND C.OwnerType = 2
AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;
 
-- Copy Media tag from shared event to Share events
 
INSERT INTO MediaLinkTable
 
SELECT
  NULL AS LinkID, ML.MediaID, ML.OwnerType,
  E.EventID AS OwnerID,
  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder,
  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom,
  ML.Note, ML.Caption, ML.RefNumber, ML.DATE, ML.SortDate, ML.Description
FROM MediaLinkTable ML
INNER JOIN EventTable E ON ML.OwnerID = E.IsPrimary -- remember IsPrimary temp holds the shared EventID
AND ML.OwnerType =2
AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;
 
-- Copy Media tag from citation of shared event to citation of Share events
 
INSERT INTO MediaLinkTable
 
SELECT
  NULL AS LinkID, ML.MediaID, ML.OwnerType,
  C.CitationID AS OwnerID,
  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder,
  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom,
  ML.Note, ML.Caption, ML.RefNumber, ML.DATE, ML.SortDate, ML.Description
FROM MediaLinkTable ML
INNER JOIN CitationTable C ON ML.OwnerID = C.Flags -- remember Flags temp holds the CitationID of the shared Event
AND ML.OwnerType =4
AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')
 
;
 
-- Copy WebTag from citation of shared event to citation of Share events
 
INSERT INTO URLTable
 
SELECT
  NULL AS LinkID, 4 AS OwnerType, C.CitationID, U.LinkType, U.Name, U.URL, U.Note
FROM URLTable U
INNER JOIN CitationTable C ON U.OwnerID = C.Flags -- remember Flags temp holds the CitationID of the shared Event
AND U.OwnerType = 4
AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')
;
 
-- Append Name and Note from witness not in database to the original shared event note
 
UPDATE EventTable
SET Note =
-- SELECT
  Note ||
-- SELECT
  '{** ' || CAST(X'0A0D' AS TEXT) ||
  (SELECT W.Given || ' ' || W.Surname || ' was ' || LOWER(R.RoleName) || '. '
    || W.Note
   FROM WitnessTable W
   INNER JOIN RoleTable R ON W.ROLE = R.RoleID
   WHERE EventTable.EventID = W.EventID
   AND W.PersonID = 0
  ) || CAST(X'0A0D' AS TEXT) || '**}'
--  FROM EventTable
  WHERE EventTable.EventID
  IN (SELECT EventID FROM WitnessTable WHERE PersonID = 0)
;
 
-- Reset EventTable.IsPrimary temp used for Shared EventID to 0
UPDATE EventTable SET IsPrimary = 0
WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;
 
-- Reset CitationTable.Flags temp used for Shared Event CitationID to 0
UPDATE CitationTable SET Flags = 0
WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')
;
 
-- Make a backup of the WitnessTable before wiping it
DROP TABLE IF EXISTS WitnessTableSafe
;
CREATE TABLE IF NOT EXISTS WitnessTableSafe
AS
SELECT * FROM WitnessTable
;
 
/*
 DONE! Shared events still shared but now the sharers also have individual unshared events
 with their particular note.
 When SQLiteSpy closes the database, any temp tables in memory will be gone
 but WitnessTableSafe will remain in the database and could be used to restore the Sharings
 before running the script again. So, too, remains zTmpShareSplit. Facts-Split-Undo.sql
 depends on their presence to reverse the splits.
 
 Facts-Split-HideTracks.sql drops these tables from the database, no going back.
 
*/

Unshare

Facts-Unshare.sql

-- Facts-Unshare.sql
/*
2012-12-21 Tom Holden ve3meo
 
This simple script unshares all shared facts by deleting
all rows from the WitnessTable.
 
*/
 
-- Delete Sharings
DELETE FROM WitnessTable
;

Undo Splits and Reshare

Facts-Split-Undo.sql

-- Facts-Split-Undo.sql
/*
2012-12-21 Tom Holden ve3meo
Reverses the changes made by Facts-SplitSharedToIndiv.sql
Requires tables zTmpShareSplit and WitnessTableSafe from the Split query
 
Deletes the new Indiv Share, Census and Residence events and
related citations, media tags and webtags
and restores the Sharings. Sort of an UNDO... but not complete;
the appends to the original fact note remain.
*/
 
DELETE FROM EventTable WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable');
DELETE FROM CitationTable WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable');
DELETE FROM MediaLinkTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'MediaLinkTable');
DELETE FROM URLTable WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'URLTable');
-- To restore the Sharings:
INSERT INTO WitnessTable
SELECT * FROM WitnessTableSafe
;

Hide Tracks

Facts-Split-HideTracks.sql

-- Facts-Split-HideTracks.sql
/*
2012-12-21 Tom Holden ve3meo
 
Drops the tables created by Facts-SplitSharedToIndiv.sql
It does not hide all traces as the Share events themselves attest and
THERE IS NO GOING BACK: Facts-Split-Undo.sql will fail.
*/
 
DROP TABLE WitnessTableSafe
;
DROP TABLE zTmpShareSplit
;