Contents
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).
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:
- Use shared events to the fullest extent but forego exporting to external software that may have advantages for certain kinds of analysis and reporting.
- Forego using shared events so that you can more fully enjoy the benefits of exporting to external software.
- 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.
- 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.
While most other applications have no support for shared events, a small number do preserve the data when importing a GEDCOM from RootsMagic:
Application | Converts | Preserves | Description |
---|---|---|---|
Family Historian 7 | X | Full family tree/genealogy software | |
GedSite | X | Generates website from GEDCOM | |
GedView | X | GEDCOM editor/viewer for iOS | |
Heredis 2021 | X | Full family tree/genealogy software | |
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 unique data for the event itself for the Principal role is stored in EventTable and can remain unchanged.
- Witnesses or sharees of the event are identified in the WitnessTable with their unique info: RoleID, custom sentence, Note.
- 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.
- Some Fact Types, such as Census, can be split into individual Census facts.
- 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”.
- 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.
- 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.
- 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.
- Some may want to operate on an intermediary database for export, eliminating all shared events with individual events.
- 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:
- Family events
- special Fact Types, e.g. Census, Residence that should keep the same type
- citation media
- auto create ‘Share’ Fact Type, if not existing, complete with a default sentence template
- webtags
- fact don’t exist
- citation
- research log?
- cleanup temp used IsPrimary and Flagsfields
- 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.
- temp use of EventTable.IsPrimary and CitationTable.Flags
- don’t generate spurious events or citations
- stop misusing these fields
- 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
- Split: would leave Shared events as is and add Share events. User control of FactType output options and Private options might prove satisfactory.
- Unshare: would remove the non-Principals from the shared events
- Undo: would restore the non-Principals to shared events and delete the Share events and related citations, media tags and web tags
- Hide tracks: would drop the query created tables from the database
- Should Name, Role of Persons who shared an event be appended to Note of once-shared event?
- 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):
- 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.
- 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.
- 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
- Facts-Split-HideTracks.sql rids the database of the tables created by the first script;
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. */
-- 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 ;
-- 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 /* 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 ;
This worked great but I would like to make 2 changes.
1. I have additional shared facts and would like their name.
Add aditional where clauses WHEN 18 THEN 18 — Census to Census
2. The description field gets overlayed with information I already have entered.
” AS Sentence,
LOWER(R.RoleName) || ‘ in the ‘ || LOWER(F.Name) || ‘ of ‘ || N.Given || ‘ ‘ || N.Surname || ‘-‘ || N.OwnerID AS Details,
#1 I can handle OK but need help with # 2
How do I get the description that is already there to be at the front of the field with the appended text following?
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
This worked great but I would like to make 1 changes.
” AS Sentence,
LOWER(R.RoleName) || ‘ in the ‘ || LOWER(F.Name) || ‘ of ‘ || N.Given || ‘ ‘ || N.Surname || ‘-‘ || N.OwnerID AS Details,
W.Note AS Note
How do I get the information that is already in the description field to be in front of this text string?
Roger
The value of the field Details for the Principal is included in the new split event with this mod:
” AS Sentence,
E. Details || ‘ ‘ ||LOWER(R.RoleName) || ‘ in the ‘ || LOWER(F.Name) || ‘ of ‘ || N.Given || ‘ ‘ || N.Surname || ‘-‘ || N.OwnerID AS Details,
W.Note AS Note
Thanks
It will take some time but I am learning a lot.
Roger
Tom
One question.
I understand that Details comes from the EventTable but how dose SQL know the E. is the EventTable?
Roger
You can alias a table name like this:
SELECT alias.Details FROM EventTable (AS) alias;
The SELECT statement has many options as described at https://sqlite.org/lang_select.html . Scroll down to the table-or-subquery heading and click on its “show” button.
Tom
If I start being a pest let me know.
The code you gave me work except it dose not have a space between E.Details and the next field so I added one || ‘ ‘ || but this puts a blanks space if E.Details is blank.
So I tried which did not work and I could not even get it into the query
IF E.Details NULL E.Details || ‘ ‘
Any help?
Thanks
Roger
Wrap the replacement expression in the TRIM() function to strip both leading and trailing blanks.
TRIM(E. Details || ‘ ‘ ||LOWER(R.RoleName) || ‘ in the ‘ || LOWER(F.Name) || ‘ of ‘ || N.Given || ‘ ‘ || N.Surname || ‘-‘ || N.OwnerID) AS Details
I’d be concerned that your expanded string may exceed what RM supports for GEDCOM (including drag’n’drop) or TreeShare transfer. It may be advisable to put this info into the Note instead.
I just ran the script Facts-SplitSharedToIndiv.sql and it works great on RM7.6.3 except that the shared fact ‘note’ did not get created. (I did not run the other scripts.) It seems that the script is looking to the Note field from the WitnessTable as the source for creating the new individual event note. In my db, the WitnessTable note field is always blank (not sure how to even force a witness table note entry). Changing “W.Note AS Note” to “E.Note AS Note” in the family and non-family segments of the script produced the results I was expecting. Not sure if this was unique to my implementation or if it’s a switch to where notes from shared facts are stored.
Tom, apologies in advance for posting multiple things here. I was going to create a new post but it seems that I neglected to request this capability when I joined so I’m relegated to commenting for now. I’ve taken a stab at updated your Facts-SplitSharedToIndiv.sql for RM8. It seems to work but needs more eyes on it, since my sql skills are weak. Am hoping one of the admins will reach out so that I can send them the file for review.