Facts – Split Shared to Individual #facttypes #events #sharedevent #RM9

Revised scripts for #RM8 and #RM9 added 2023-03-09. Lightly tested – feedback appreciated.

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 for RM4-7).

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. Support for shared events is even turned off in RootsMagic Essentials, which is what is included in the RM Shareable Drive feature! Until RootsMagic comes up with a satisfactory splitting option on export and TreeShare exchange, 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.

None of the above may satisfy your requirements. This page is about a fifth approach – convert shared events within the database to individual ones. You choose whether to convert temporarily or permanently and whether to use both.

Screenshots

Here are some screenshots of the results using RM9.0.2.

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 a GEDCOM from RootsMagic:

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.
11. TreeShare complicated things when introduced in RM7.5: it does not support shared events for non-Principal persons, does not support the Census and Residence Family-type facts and transferring links with the Ancestry databases for Ancestry sources and media to the split events is uncertain. Splitting sharers into individual events helps with the first two and the normalising of Citations in RM8 facilitates the links. (added 2023-03-09)

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? for RM8+: Tasks – should anything be done? Complicated…
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 (CitationTable.Flags unused for RM8+)
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? Done for persons not-in-database.
12. Describe the queries, how to use them, illustrate results
13. Consider whether there is merit in converting sharers-not-in-database to Persons-in-database with an Association fact type to the Principal sharer.

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 or Facts-SplitSharedToIndiv-RM9.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 or Facts-Split-Undo-RM9.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 for RM4-7
Facts-SplitSharedToIndiv-NoRIN.sql for RM4-7 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.

Unshare

Facts-Unshare.sql for RM4-9

This simple script unshares all shared facts by deleting
all rows from the WitnessTable.

Undo Splits and Reshare

Facts-Split-Undo.sql for RM4-7

Reverses the changes made by Facts-SplitSharedToIndiv-RM9.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. Cleans out the not-in-database sharers
appended to the original fact.

Requires sqlite manager with support for REGEXP_REPLACE() function.

Hide Tracks

Facts-Split-HideTracks.sql for RM4-9

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.