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


DaiyuHurst

Forcing REFN to be first fact

DaiyuHurst
17 September 2018 18:54:37

I like my REFNs up top. Before even birth. An easy way to do this is to use this slightly altered SELECT statement.
Leaving out the FROM clause for brevity:

SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, DATE, ‘1’,
IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, ‘RIN ‘||N.OwnerID, Note

The curious thing about doing this is, when you examine the individual records in RM, the Sort Date field will no longer be populated, but the effect of the procedure was good, as the REFN fact is the very first fact in each record.

I hope this is useful to someone.


DaiyuHurst

DaiyuHurst
17 September 2018 18:58:30

Oddly, I did not see the last paragraph describing this.

One Reply to “Copy RIN to REFN #update #insert #refno #rin

  1. I rewrote the query slightly for a colleague. The three changes are: 1) provided a DELETE statement in case the colleague wanted to delete all existing Reference Number facts, 2) removed the references to a Group so the colleague would not have to make a group of everybody in his database, and 3) ran a MAX function on the EventID in the EventTable so the colleague would not have to look up the last EventID in the EventTable and plug it into the script. Item #3 is probably the most significant difference.

    Jerry

    — DELETE FROM EventTable — run this script if you want to delete all Reference Number
    — WHERE EventType = 35; — facts before you Start. NB!!! If you delete all Reference Number facts,
    — You will need add one dummy Reference Number fact to one person before
    — you run the main script. But you need to do that anyway. It’s just that the
    — sequence is important. Normally, it’s step #1 add a dummy Reference Number
    — fact, step #2 run the main script. But if you want to clear out the old
    — Reference Number facts before you start, it’s step #1 run the DELETE,
    — step #2 add a dummy Reference Number fact, step #3 run the main script.

    — The script below adds a Reference Number fact to each person, where the Reference Number that is added
    — is a five digit number with leading zeros that matches the RM Record Number (also called RIN number).
    — If you want more or less than five digit numbers, change the -5,5 in the SUBSTR to the desired number,
    — e.g., -6,6 for six digit numbers, -4,4 for four digit numbers, etc. This script required that you add
    — a dummy Reference Number fact to any one person in RM as the last thing you do before you run the script.
    — The script will copy any attributes contained in the dummy Reference Number fact to all the facts it
    — adds, such as dates or sort dates. You will probably will want to remove the dummy Reference Number fact
    — after you are done. The dummy Reference Number fact may contain a date or sort date or similar data that you
    — wish to be applied equally to all Reference Number facts that are added to the database.

    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, SUBSTR(‘00000000’ || N.OwnerID,-5,5), Note
    FROM (SELECT P.PersonID AS OwnerID FROM PersonTable AS P) N,
    EventTable E
    WHERE EventID = (SELECT MAX(EventID) FROM EventTable) AND EventType = 35;

Leave a Reply