TMG-RM Convert TMG_ID to Record Number #tmg #rin

Why?

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.

How?

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.
TMG-RM_convertTMG_IDtoRIN.sql

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:

DELETE FROM EventTable WHERE EventType = 35 ;

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:

UPDATE EventTable
SET EventType = 35
WHERE EventType =
(
  SELECT FactTypeID
  FROM FactTypeTable
  WHERE ABBREV LIKE 'TMG_REF'
)
;

Then you can delete TMG_REF from the Fact Type List.