Why? How?

Example of an MRIN event added by the script. (click for full size)

Main Script | Mismatched FMNO Report | Earlier GEDCOM Solution

RootsMagic 6 hides the record number commonly known as MRIN except as an option in the title of the Family Group Sheet and in the Marriage List report. This is not good enough for some users who, perhaps migrating from experience with other family tree software, have a document filing system organised on MRIN. In RM, the “MRIN” comes from the FamilyID in the FamilyTable and is not based on a Marriage event; hereafter, I refer to it as FMNO. It is not preserved in data transfers via GEDCOM or drag’n’drop between RM databases, except in special circumstances. With that caveat, the following SQLite script creates a custom FMNO family event and adds it to every couple and single parent with their FamilyID number stored in the event description with the prefix “FMNO “. A second script reports on mismatches between the FamilyID number and the FMNO event value as could arise from a transfer to another database. And to round things out showing that there is always more than one way to accomplish something, see a non-SQLite method for batch creation of the FMNO events at the bottom of the page.

Main Script

Events-FMNO.sql

-- Events-FMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Exposes otherwise invisible FamilyID (except optionally in title of FGS)
by adding an event to each spouse containing the FamilyID.
The event added is a custom Family event named *FMNO, abbreviated FMNO which is
first created by the script if one having the same abbreviation does not exist.
*/
-- Add a FMNO fact type if none exists
INSERT
    OR IGNORE
INTO FactTypeTable(OwnerType, NAME, Abbrev, GedcomTag, UseValue, UseDate, UsePlace, Sentence, Flags)
SELECT 1
    ,'*FMNO'
    ,'FMNO'
    ,'EVEN'
    ,1
    ,0
    ,0
    ,CAST('[Desc].' AS BLOB)
    ,- 1
WHERE (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) ISNULL;
 
-- Delete all FMNO events  N.B. RM will delete all events for a FactType that you delete through it.
-- DELETE FROM EventTable WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE ABBREV LIKE 'FMNO' AND OwnerType=1);
-- Add FMNO event to each couple without such an event, description to contain FamilyID (invisible FMNO)
INSERT
    OR
 
ROLLBACK
INTO EventTable(EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate, IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
 
SELECT (
        SELECT FactTypeID
        FROM FactTypeTable
        WHERE ABBREV LIKE 'FMNO'
            AND OwnerType = 1
        ) AS EventType
    ,1 AS OwnerType -- a Family event is type 1
    ,FamilyID AS OwnerID
    ,0 AS FamilyID
    ,0 AS PlaceID
    ,0 AS SiteID
    ,'.' AS DATE
    ,1 AS SortDate -- places event at or near top of list in Edit Person screen
    ,0 AS IsPrimary
    ,1 AS IsPrivate -- presumably having this event set as private will provide needed output control
    ,0 AS Proof
    ,0 AS STATUS
    ,(
        SELECT JULIANDAY('now', 'localtime') - 2415018.5
        ) AS EditDate -- does not affect Date last edited in People view
    ,NULL AS Sentence
    ,'FMNO ' || FamilyID AS Details -- having the prefix "FMNO" helps to stand out in tabular reports
    ,NULL AS Note
FROM FamilyTable
WHERE FamilyID -- don't add an FMNO event to those couples already having one
    NOT IN (
        SELECT DISTINCT OwnerID -- list of all FamilyID's already having the FMNO event
        FROM EventTable
        WHERE OwnerType = 1
            AND EventType IN -- in case there is more than one FMNO family fact type
            (
                SELECT FactTypeID
                FROM FactTypeTable
                WHERE ABBREV LIKE 'FMNO'
                    AND OwnerType = 1
                )
        );

Mismatched FMNO Report

Example of mismatches after a drag’n’drop transfer to a new database, as displayed in SQLite Expert. (click for full size)

Unfortunately, a drag’n’drop of Everyone from one database to a new, empty database does not necessarily preserve all FamilyID and PersonID numbers. Many were preserved but many not, as seen in this extract from the following script’s output; fully 1/3 of almost 400 FamilyIDs were changed while PersonID changes were many more. What happens is that new PersonTable and FamilyTable tables are generated and records unused in the originating database are reused in the new, thus shifting the record numbers down. GEDCOM import into an empty database offers the option to Preserve record numbers but this only applies to the person, not the family. A document filing system based on either or both the MRIN/FMNO and RIN of the originating database will be out of sync with the internal record numbers for many couples and, probably, persons in the target database. Carrying over the FMNO and RIN as events to the target database preserves the document trail for as long as those events are preserved and helps to realign the filing system with the database. Also see Copy RIN to REFN.

Events-MismatchedFMNO.sql

-- Events-MismatchedFMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Compares the OwnerID for Family events having the Abbrev "FMNO" against
the value stored in the event description, listing those that do not match
along with the names and RINs of the couple.
Used in conjunction with Events-FMNO.sql.
*/
SELECT SUBSTR(CAST(E.[Details] AS TEXT), 6) AS FMNO -- extracts everything after "FMNO "
    ,E.[OwnerID] AS FamilyID
    ,ifnull(N1.Given || ' ' || UPPER(N1.[Surname]) || '-' || FM.FatherID, '')
   || ' + ' ||
   ifnull(N2.Given || ' ' || UPPER(N2.[Surname]) || '-' || FM.MotherID, '') AS Couple
FROM EventTable E
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
LEFT JOIN NameTable N1 ON FM.FatherID = N1.OwnerID
    AND + N1.IsPrimary
LEFT JOIN NameTable N2 ON FM.MotherID = N2.OwnerID
    AND + N2.IsPrimary
WHERE E.OwnerID NOT LIKE SUBSTR(Details, 6)
    AND E.OwnerType = 1 -- Family event
    AND FT.[Abbrev] LIKE 'FMNO';

Earlier GEDCOM Solution

I discovered Alfred’s 9 April 2009 solution using Microsoft Word wildcard search and replace on the GEDCOM and Romer’s 8 July 2009 reiteration of that after having developed the SQLite scripts.