SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

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

Posted on by Tom Holden

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.

Principal’s Edit Person screen showing Maggie Wood-1149 in the Daughter role of a shared Census event. Rose Ann Owen is a person not-in-database (no RIN) in the Boarder role.
Maggie Wood-1149 after splitting two shared events: Census and Immigration, showing both the shared events and the script created individual events. Note that Notes, Citations and Media carried over.
Principal’s Edit Person snippet showing a not-in-database sharer of the Census event in the role of Boarder. See the following shot showing the retention of the above information in the Principal’s Note.
Snippet from Find Everywhere for “{**” which begins the paragraph appended to the Principal’s Note for the original shared event to preserve information about sharers not-in-database. See WOOD, George-367 to find Rose Ann Owen and the other Boarders.

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:

ApplicationConvertsPreservesDescription
Family Historian 7XFull family tree/genealogy software
GedSiteX Generates website from GEDCOM
GedViewX GEDCOM editor/viewer for iOS
Heredis 2021XFull family tree/genealogy software
Legacy Family Tree XFull 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.
  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-RM9Download (also for RM8)

Facts-SplitSharedToIndiv.sql RMtrix_tiny_check.png 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 RMtrix_tiny_check.png for RM4-9

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

Undo Splits and Reshare

Facts-Split-Undo-RM9Download (also for RM8)

Facts-Split-Undo.sql RMtrix_tiny_check.png 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 RMtrix_tiny_check.png 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.

19 Replies to “Facts – Split Shared to Individual #facttypes #events #sharedevent #RM9”

  1. 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

  2. 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

  5. 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.

  6. 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.

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

    • I tested the script and it worked great on my test shared facts.
      I do have one question: I mainly want to use shared events for census’ and residence facts since there are often almost a dozen people in a family between the kids and parents (farming families in W.Va). Once I convert them I would like to be able to delete the shared events.

      Is that hard to do while maintaining the census fact on the ancestor it was shared from? (I never have any intention of restoring the shared events, really just trying to save time since I have probably close to 100+ census’ to enter for a lot of families in my tree).
      Thanks again for your hard work 🙂

      • Yes, that’s the second script Facts-Unshare.sql. The 3rd provides an UNDO of the splits and restores the shares while the 4th clears out the tables used by the process should you need to send your file to Tech Support!

        Thanks for the feedback – glad to hear it’s doing what you want on the splitting. I can see how your goal will be expedited by sharing 100 Census events rather than creating 1200. It’s one of the things I admire about Ancestry: its ability to create new people, facts and citations in your Member Tree from one Hint. I may not like other aspects but it is far more efficient than doing so through the RM UI (ignoring the cost of cleanup after a download from the AMT).

        • Thanks again.
          Regarding Ancestry lol. The first couple of years of doing Genealogy my main program was FTM 2019. I found it soooo easy to add a bunch of people to my tree. I thought everything was going great.
          What I didn’t realize because I just accepted the citations that Ancestry gave, was that I had literally thousands of citations where Ancestry filled in just the source. When I downloaded to FTM that was fine (even though the citations were worthless). I discovered the problem when I moved to RM 8 and did a citation merge. Since the citation merge in RM 8 didn’t look at the link field or the media field it lumped literally hundreds of citations together with some having dozens of media belonging to many different families. I learned my lesson the hard way and spent a solid month fixing citations.
          Anyways you are the best Tom!!

  12. I am wanting to use this query in RM9 but omit certain Shared facts (or only do certain shared facts, whichever is easy to accomplish).

    I have a few shared facts that I used for DNA that I would like to keep as shared facts as I don’t want that information to sync and it works the way I need it to for reports etc.

    The FactTypeID’s I want to omit are 1057, 1058, 1059, 1064. I have tried to work it out but I am very new to this still and this query is just a little to involved for me to work out exactly what and where I have to include this information.

    Alternatively can I make it to only do the facttypeID’s I specify?

    Would really appreciate any guidance on how to do this.

    • Assuming your four FactTypes for exclusion from splitting are all Individual types. If so, then I think this modification to the following block of the script would work as you want:
      -- Split Shared non-family facts
      INSERT INTO EventTable

      SELECT
      NULL AS EventID,
      ...
      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
      AND F.FactTypeID NOT IN (1057, 1058, 1059, 1064) --Maree 2023-05-01
      ; -- tested RM9

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.