Copy RIN to REFN #update #insert #refno #rin

Some RootsMagic users would like to have a utility to add a Reference Number (REFN) fact to persons that would contain the current Record Number (RIN). The intent is that this REFN would persist through GEDCOM exports and imports, drag’n’drop transfers, and possibly merges, thus providing a reference to the record’s history. I have used this idea to maintain a reference to a family tree published by a cousin from an early version of PAF, to facilitate finding people in his book. There could be many reasons for snapshotting the current RIN. This SQLIte query and attendant procedure makes it easy to do so for any number of persons in a RootsMagic database.

Use RootsMagic:

1. Backup your database.
2. Add a REFN fact to any person in your database. You can fill its Description field with garbage or leave blank as you will delete it later. Do NOT add any more facts to anyone until you finish this procedure.
3. Define a Named Group of persons to whom you want to add REFN facts containing their RINs.
4. Close RootsMagic (not required but safety first!).

Then use a SQLite manager such as SQLiteSpy:

1. Open your database
2. Copy the SQLite query below into the SQL Editor
3. Open the EventTable and get the EventID from the last row (its EventType should be 35 which is the REFN type).
4. Substitute the value of EventID from step 2 into the SQLite query in the Editor in place of ‘??EventID??’
5. Open the LableTable and get the LabelID LabelValue of the row whose LabelName corresponds to the name of your target Group.
6. Substitute the value of LabelID LabelValue  from step 5 into the SQLite query in the Editor in place of ‘??LabelID??’ ‘??LabelValue??’.
7. Execute the query.
8. Assuming no error message, close your database (not required but safety first).

When you next open your database with RootsMagic, you should see the newly added REFN facts in every member of your Named Group. Return to the person you initially added the dummy REFN and delete it.

Here’s the query: rev 2020-05-16 LabelValue instead of LabelID

INSERT OR ROLLBACK INTO EventTable 
        (EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
         IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
  SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
         IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, 'RIN '||N.OwnerID, Note
   FROM (SELECT P.PersonID AS OwnerID FROM PersonTable P, GroupTable 
         WHERE OwnerID >=StartID AND OwnerID <= EndID 
          AND GroupID=??LabelValue??) N,
         EventTable E 
          WHERE EventID=??EventID?? AND EventType=35;

It’s a variant of the Copy Fact to Group query. The differences are few but one significant change was to get the OwnerID from the PersonTable, not the NameTable, as the latter resulted in the addition of multiple facts to the same person if that person had multiple names.

As written, the Description field of the REFN fact will contain ‘RIN n’, where n is the PersonID number. You can change the query to put out some other string in combination with the PersonID by editing the part

'RIN '||N.OwnerID

If you want leading zeroes or blanks so that the REFN is right-justified, the following will work (increase the number of blanks or zeroes if any RINs>9999):

'RIN'||SUBSTR('   ',LENGTH(N.OwnerID))||N.OwnerID -- leading blanks
'RIN'||SUBSTR('000',LENGTH(N.OwnerID))||N.OwnerID -- leading zeroes

All of the added REFN facts will have every other parameter identical to that of the dummy REFN fact, including the same EditDate, hence it is preferable to use a dummy created on the same day that you run the procedure.
If you want the REFN to appear first in the Edit Person screen, then set the SortDate of the dummy REFN to 1 before running the procedure.

Discussions & comments from Wikispaces site