TMG-RM+Convert+TMG_ID+to+Record+Number

toc RootsMagic 6.3.3.2+ imports the TMG_ID into the standard RootsMagic Reference No. fact type (REFN). While this fact type can be displayed after the name of the person in main views and reports, it cannot be displayed in the sidebar Index and in some other places. Some users would prefer that it did; the only number that does is the Record Number (RIN), which is also the fastest search mechanism in RootsMagic Explorer.

This procedure substitutes the TMG_ID value from REFN for all references to PersonID or RIN, which can optionally be displayed after the name. RIN is the most universal ID used across RootsMagic displays and outputs. However, it is volatile when transferred or imported into other databases, unlike the Reference Number fact or the TMG_ID fact formerly created with imports using 6.3.3.1.

Update History
2014-10-08 revised to support imports using RootsMagic 6.3.3.2; no longer supports earlier versions. 2014-09-24 handles single or small number of changes with proportional time; previous version was only suited to changing all; logs potential conflicts and warnings in temp table xRIN_TMGIDlog which should be opened if the trial query aborts in order to find and resolve the cause of the error; logs a warning if multiple TMG_ID facts for same person. Log displayed if script completes without an abort.


 * N.B. Be sure to close the database from RM before running this procedure**

Many tables are changed so the procedure may take quite a while on large databases.While speed optimisation has not been a focus, a 5000 person database with 15000 events and 11000 citations was operated on for 32 seconds to change 98% of the records, 2 seconds to change a few.

Surplus Reference No. Facts
Now that the TMG_ID is in the RootsMagic RIN, there seems little reason to preserve the Reference Number facts. Fortunately, the import placed TMG Reference tags into a custom RootsMagic fact type "TMG_REF" and added only one Reference No. (REFN) fact to each person to hold the TMG_ID. So a simple SQLite statement can get rid of them all: code format="sql" DELETE FROM EventTable WHERE EventType = 35 ; code

Convert TMG_REF to REFN?
Now that it is no longer necessary to preserve the TMG_ID in REFN or to permit only that REFN for each person for the script to be successful, do we want to keep this custom fact type. Were it exported from TMG to GEDCOM, it would be with the REFN tag. There is probably some logic that it should go to that tag from RootsMagic as well. Moreover, some users may want to optionally display the Reference value after the person Name. Both of those functions are supported if the TMG_REF type facts are converted to REFN facts. A simple SQLite statement can do so: code format="sql" UPDATE EventTable SET EventType = 35 WHERE EventType = ( SELECT FactTypeID  FROM FactTypeTable  WHERE ABBREV LIKE 'TMG_REF' ) code Then you can delete TMG_REF from the Fact Type List.