Contents
Problem
RootsMagic provides no means to change all events of one fact type to another fact type, e.g., convert Residence events to Census events or vice versa. Or convert events for a custom fact type to a standard one. All it offers as of version 7.6.3 is to do so for one person, one event at a time which is laborious and slow.
Solution
A script that converts all events of one fact type to another in a fraction of a second.
Usage
Enter the name of the current event as you see it in the Edit Person screen or as you see it in the Abbrev field of Lists > Fact Type List. Then the value of the Abbrev field for the fact type to which you want the events transformed. When entered, the UPDATE process proceeds.
Requires SQLite Expert Personal with fake RMNOCASE extension loaded or an equivalent SQLite manager that also supports the entry of runtime variables.
Because this modifies your database, BACKUP FIRST! After executing, you should use RootsMagic Database Tools to check integrity and, if not OK, rebuild indexes.
Scripts
Two scripts are offered: one is for only for a fact type having no shared events and is simpler and faster. The other supports conversion of a fact type that does have shared events; use it if you are uncertain but you can check for shared events with the help of People Who Share a Fact with a Principal List and with People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List.
2021-12-30: Facts_shared-ChangeType-RM8.sql for #RM8.
Facts_shared-ChangeType.sql for prior versions.
These much more complex scripts than Facts-ChangeType.sql below do support shared events in addition to unshared ones.
Facts-ChangeType.sql This simpler script than the ones above can change the Fact Type of unshared events but not shared events. 2023-03-14: protects shared events from being changed and tested compatible with #RM9. Should be compatible right to RM4.
For the conversion of shared events, the more complex scripts look for role(s) in the target fact type that match the name and sentence template of those used by events of the originating fact type. For those that do not match, a duplicate of the current role is created for the target fact type. Sharers of the transformed event are assigned these existing or new roles. The original role definitions remain intact. Should events be converted back to their original fact type, no new roles will be created provided no change has been made in either the original or target role name or sentence.
Discussions & comments from Wikispaces site
Newbie successfully used this!
25 July 2017 18:33:32
Have never touched SQLite before today (although I do have a somewhat minimal exposure to programming) but was able to piece together the proper program, with the proper fake RMNOCASE extension, and the above script (after a couple of false starts) and within two hours from start to finish had successfully modified my RootsMagic database that was imported from FTM 2014.
I didn’t like the way the import put the FTM Description fields on major facts (Birth, Baptism, Marriage, Naturalization, Death, Burial, Probate) into Place details so, before the import, I used the built-in FTM option to move all those facts to corresponding temporary facts (TBirth, TBaptism, TMarriage, TNaturalization, TDeath, TBurial, TProbate). Imported the file to RootsMagic and used the above script to move all the data back to the original fact. Worked like a charm. I ran the Database tools after and everything worked splendidly. On 18,165 people tree. Would have taken years to do this manually person by person which is the only option within RootsMagic.
Have spot checked people and everything, including the more complex shared marriage fact is functioning exactly as they should.
Thank you Tom! Wish I had tried it back in Jan of 2016 instead of continuing with FTM but I guess things happen at the time they are meant to happen.
You are a lifesaver! You have saved me eighty hours of manual labor! I used to be a programmer (40 years ago, BASIC and FORTRAN), and was able learn how to use SQLite Expert, with your script, to change an offending fact type very easily.
Can you help with another problem? I need to move information (a person’s age) from the Place Detail field in a Census fact to the Description field, but only IF the Description field is blank.
How can I send you a private communication?
Glad to hear of your success, Miguel. I was never employed as such but my first exposure to programming was to a flavour of Fortran called Kingstran at Uni around 54 years ago.
Tom, I have a similar problem. I am working on a huge database for one of the indian reservations compiling their annual reservation census records with the federal and state records. I recently realized that I used the wrong fact type “Residence” when I should have used “Census” for the first 15 years of annual census. The each year of the annual census shares a common date being 30 Jun followed by the census year. I need something that will search the fact types looking for Residence and when it sees a date starting with 30 Jun, change that corresponding record to Census. Spent years as a programmer, just never have used SQLite; but I’m sure I can pick it up quick. Can you point me in a direction?
Ron, I just noticed that I had not responded to your request in the fog of Christmas. Do you still need a pointer? Basically, it is simply to add another condition to constrain the Step 2 UPDATE to modifying those Residence records having a Date value that matches “0630” in positions 8-11 and that also match the other conditions.
Tom
Hi Tom,
Do you know if this script will still work on RM8?
Cheers
Mike
Sorry, Mike, I received no notification of your comment so just saw it now. I think with one revision it should work with RM8. This statement needs one more field:
— create Roles for target fact type
INSERT INTO RoleTable
SELECT RT.NewRole
, R.RoleName
, (SELECT FactTypeID FROM xEventTargTable) AS EventType
, R.RoleType
, R.Sentence
, NULL AS UTCModDate — for RM8
FROM RoleTranspose RT, RoleTable R
WHERE RT.OrigRole = R.RoleID
The topic “Delete all facts containing specific text AND change fact type if it has a specific source“(Mar 10, 2023) in the Forum resulted in short scripts that may be of interest to some. One is changing events to a different Fact Type conditional on the name of the Source.
The simpler script Facts-ChangeType.sql has been revised to protect shared events from being changed and should be compatible with all versions of RootsMagic from 4 to 9.
How do the family fact types complicate the use of this script and the splitting script? Do the Data Table articles elaborate on the table structures in family events?
I have never used the share facility but I wound up with Census(fam) tags that I would like to split to Census tags.
I have spent a few hours looking for detail, but have not found anything to instill confidence yet. I have SQLite Expert Personal working on my test data and plan to run tests there. I’m 20 years from my last swim in the SQL pool, so relearning as I go along.
Assuming you are using RM9, citations are linked to ‘family’ events via the CitationLinkTable (CLT) just as they are for ‘individual’ events when the CLT.OwnerType=2 in which case the CLT.OwnerID = EventTable.EventID. It is the Event OwnerType that distinguishes a family event (ET.OwnerType=1) from an individual’s event (ET.OwnerType=0). When ET.OwnerType=1, the ET.OwnerID=FamilyTable.FamilyID which identifies the PersonTable.PersonID for each of the two spouses.
For citations for the Family (the couple), CLT.OwnerType=1 so CLT.OwnerID points directly at FamilyTable.FamilyID from which you can get the PersonID of each member of the couple.
I am using RM9. Thank you for the linkages.
I’ll explore these a bit to understand more of the structure. I need to map what I see in the models to the physical tables.