Migrants to RootsMagic from other genealogy software are often disappointed in the invisibility of the Marriage Record Identification Number or MRIN and may have a filing system based thereon. While the individual's record number (RIN) may be preserved in certain imports either in the RootsMagic RIN or in a custom fact type, no such mapping is provided for MRIN. And should data be transferred from one RootsMagic database to another or to some other software, MRINs (and RINs, for that matter) may change. Therefore it is advisable that nothing needing to be permanent should be based on a potentially volatile number such as MRIN.

However, one could make a snapshot of the current RIN and MRIN and store it in a Reference Number fact which is transmitted reliably through GEDCOM to virtually all genealogy software. That is what this script does.
Reference Number fact containing the RIN and MRINs for a person added by REFN-MRIN.sql. Tools>File Options>General>"Number to display after name" set to "Reference Number (REFN)".
Reference Number fact containing the RIN and MRINs for a person added by REFN-MRIN.sql. Tools>File Options>General>"Number to display after name" set to "Reference Number (REFN)".


The format of the Reference Number is designed so that searching through them for an MRIN or a RIN can produce unambiguous results with the "contains" operator. The RIN search term is "Rnn " - the leading "R" and trailing space character delimit the RIN so that matches are not found among the MRINs or in other RINs that have the same "nn" sequence of digits somewhere in them, e.g., the search term "17" could match within R9175 or within "817M".

Likewise, to search for a MRIN, the search term should be in the form " nnM". In this case, a leading space character and trailing "M" disambiguate search results.

At the time of the above screenshot, the script did not create Reference Number facts for persons with no children nor spouse, i.e., not in the FamilyTable and thus having no FamilyID or MRIN. The script now produces the REFN fact for them in the same format with "0M" to signify the lack of a MRIN. Note that I have assumed that MRIN and FamilyID are synonymous; no Marriage fact is required for there to be a FamilyID - the latter are created whenever a couple is paired or a person is linked to another as a parent which necessitates a couple, even if one partner is unidentified.

For each Ref# fact created, the script also does this:
  1. Sets fact private so it can be suppressed in tabular reports and exports.
  2. Sets fact primary so it is the Reference Number displayed after the name when there are other such facts; however, if another such fact is marked primary and was added earlier, it takes precedent.
  3. Sets SortDate so that the fact appears at the top of the list in the Edit Person screen.
  4. Sets the EditDate to system time but this has no effect on the "Date last edited" optionally displayed in People View. It may be of use to the power SQLite user in looking for exceptions by inspecting the EventTable.
  5. Customises the local sentence to output nothing in narrative reports.

Requires the REGEXP and GROUP_CONCAT() functions which are available in SQLiteSpy 1.9.10, on which the script was developed.



An earlier script took a different approach, adding each MRIN as a custom fact to each person so that a person with two spouses would receive a corresponding number of these facts. This may be advantageous for some things. See Facts - Add custom MRIN event to each person.