Names – Set Alternate of type Birth to Primary #names #alternatenames #birth

This script does two things:

  1. For those persons having one Alternate Name of type “Birth” and a Primary name of “undefined” type, it sets the Alt name as the Primary name and changes the previous Primary name to an Alternate Name.
  2. For those persons having more than one Name of type “Birth”, it lists the Record Number (RIN) and Surname and Given Names so that the user may resolve the ambiguity down to one so that the script may swap it on the next pass.

If the conclusion is that the current Primary (birth) name is the preferred name, the user may choose to delete the Alternate Name, modify its name type or leave it as is. Either way, the script will not change the Primary to Alternate unless the Primary type is “undefined”.

Sources and media remain attached to the name, whatever its Primary/Alternate state is. However, until RM8, those attached to the Primary Name are essentially inaccessible and unusable (see Citations Invisible – Reveal and Revert).

Download

Names-Swap_AltBirth_to_Primary.sql

 

Births – Add from Christening or Baptism #birth #events

RootsMagic 7, as does some other software, uses the date of the Christen event as a substitute birth date for age calculations and summary displays when there is no Birth event. But RootsMagic’s own mobile application for iOS and Android does not. Moreover, some users object to using the Christen fact type, on principle or perhaps having already extensively used the Baptism fact type and not wanting to have to change those instances one at a time, just to get the benefit of the auto-substitution. If that is all that is wanted, then the script Facts – Change Fact Type should suffice. This script removes the reliance on auto-substitution by creating a Birth event from the Christen or Baptism event for all persons having one or more of those but no Birth event.

Birth-AddFromChristenBaptism.sql

-- Birth-AddFromChristenBaptism.sql
/* 2017-02-11 Tom Holden ve3meo
For persons having no Birth event but with either a Christen
or Baptism event, adds a Birth event duplicating the CHR or BAPM
event.
 
It does not copy citations or media and does not modify
the Date or SortDate. If a person has both CHR and BAPM, it
copies CHR. If a person has multiple CHR or BAPM events,
it copies only one which with priority given to IsPrimary
and secondly to lowest SortDate.
*/

…and it does not replicate sharers if the source event is shared.

Children – Set Lineage to Birth #birth #ancestors #lineage

RootsMagic 7 and prior has the peculiar shortcoming that ancestral lineage reports, charts and other important functions follow the lineage set by the parent family displayed in the last view of each person in one of the Main screens. If the last time you viewed your mother showed her stepfather as her father, then her birth father’s line is completely omitted while the stepfather’s line is included. This affects several reports and charts:

  • Ancestor narratives
  • Ahnentafel
  • Ancestor Box Chart
  • Pedigree Chart
  • Photo Tree
  • Ancestor Wall Chart

Moreover, it also affects the filter for selecting ancestors of the highlighted person in RootsMagic Explorer’s Mark/Unmark Group functions used for

  • other reports
  • GEDCOM export
  • colour coding
  • Named Groups

RootsMagic provides no option for any of these to be directed to follow the birth line despite having the feature in the Edit Person window to set the type of relationship between a child and a parent couple to other kinds such as adoptive or step. There seems to be little point to the Relationship setting because it is visible only in the Edit Person screen and is otherwise totally ignored. There has been a long outstanding Wish List item for the system to put the relationship setting to effective use.

Meanwhile, here is a potentially useful script if you have the typical family tree with several people having more than one parent couple and want to restore the focus to the birth line without having to manually search for those with other relationships to ensure that their Main View is set to the birth parents. It has been tested on a small trial database so, as always, be sure you have a backup before using it on your database or try it on a copy of your database. If you have the same database open in both RootsMagic and the SQLite manager from which you ran the script, you will not see any effect in RootsMagic until you refresh the Main Screen by switching to a different View.

Children-SetLineage2Birth.sql

-- Children-SetLineage2Birth.sql
/*
2015-10-18 Tom Holden ve3meo
 
Sets the displayed lineage to that of the Birth parents so that
lineage views and reports follow the birth line.
In the case of multiple birth parents (permitted by RootsMagic),
sets the birth family to the one with the highest Proof setting,
else to the lowest FamilyID.
 
It does not (or cannot?) cover all possible combinations that
a user might wish to allow or consider to be the preferred lineage
because a parent family (couple) may have only one member (mother
or father) and each spouse of a couple may have any of the four
proof settings for the birth relationship (0-undefined, 1-proven,
2-disproven, 3-disputed).
 
*/
 
DROP VIEW IF EXISTS BirthCouple
;
-- this view of the parents includes only birth parents in descending order of proof
CREATE TEMP VIEW BirthCouple AS
SELECT  ChildID, FamilyID FROM ChildTable
WHERE RelFather = 0 AND RelMother = 0  -- birth relationships
AND ProofFather < 2 AND ProofMother < 2  -- relationship proof is proven or undefined
ORDER BY ChildID, ProofFather DESC, ProofMother DESC -- priority to 'proven' relationships
;
 
-- set the display lineage to the birth parents with highest level of proof and
-- do not touch persons with no parents
UPDATE PersonTable
  SET ParentID =
    (SELECT FamilyID FROM BirthCouple BC
       WHERE PersonID = BC.ChildID)
  WHERE PersonID IN (SELECT ChildID FROM BirthCouple)
;
 
-- the temporary SQLite View BirthCouple will be deleted when the SQLite
-- manager closes the database

Rebuild Indexes and Update Birth and Death Years #update #date #birth #death #index #speed #rmnocase

Rebuilding the indexes of large databases using RootsMagic’s File > Database tools > Rebuild Indexes function is ponderously slow, still at version 6.0.0.2. A 162000 person database took over 100 minutes. This page provides procedures that take but a few minutes.

In RootsMagic version 5.0.2, the Rebuild Indexes tool was introduced under Menu > File > Database Tools; it not only rebuilds the SQLite database indexes which must be kept accurate but also updates the BirthYear and DeathYear columns of the NameTable. These values are displayed optionally in the sidebar Index and are used in some other views and reports. Prior to 5.0.2, there was no tool to rebuild the SQLite indexes and when the sidebar BirthYear and the Birth fact got out of sync (they still can), the only way to update them in RM was to edit the Birth fact and save it. To help find such mismatches, the following procedures were developed:

I do not recall for certain why no batch update procedure was published at the time. Perhaps it had something to do with the RMNOCASE issue, now worked around by RMNOCASE – faking it in SQLite Expert, command-line shell et al or RMNOCASE – faking it in SQLiteSpy and the fact that whatever index discrepancies between the fake and the real collation can be resolved by running RM’s Rebuild Indexes!

I suspect that what takes RM so long is not the SQLite Re-indexing but rather the recalculating and display of the Birth and Death years because re-indexing with the fake collation in SQLiteSpy takes less than two minutes with this big database. So the shortcut procedure outlined here relies on the assumption that RootsMagic’s SQLite database engine is as efficient as SQLiteSpy’s and completes the critical database indexing in a similar length of time, if such re-indexing is required.

  1. Run RM’s Menu > File > Database tools > Test database integrity. Despite the warning, you can expect it to complete in tens of seconds, not tens of minutes, on even a fairly low end computer, varying with size.
  2. If the result of 1 is OK, skip the next step.
  3. If the result of 1 is NOT OK, then run RM’s Rebuild Indexes. After a few minutes, use the Windows Task Manager to stop RootsMagic. Return to step 1.
  4. Once database integrity is OK, then it is safe to proceed with SQLiteSpy to update the Birth and Death Years using the query below.
  5. Having completed the SQLite query, close and reopen RootsMagic Explorer to view the results. Retest database integrity to be satisfied.

UpdateBirthDeathYears.sqlRMtrix_tiny_check.png
2012-12-06
Rev A: revised to respect the Primary fact if there are multiples. Completed update of 162,000 person database in 25 seconds.
Rev B: Christen, Baptism and Burial, Cremation now alternate Birth, Death dates in that order. BC dates. Update time doubled to 55 seconds.
Some may prefer the faster, leaner version.

-- UpdateBirthDeathYears.sql
/*
2012-11-14 Tom Holden ve3meo
2012-12-06 revA: priority to first record set to Primary, else first record
 when multiple Birth or Death facts.
           revB: incorporated Christen, Baptism as alternate Birth facts; Burial, Cremation
 as alternate Death facts - in that order. Same priority for Primary facts in same type.
 Now supports BC dates.
 
Sets Birth and Death years as seen in the sidebar index and various other
reports and displays to match the corresponding facts.
 
Close and reopen RootsMagic Explorer to see the results.
 
*/
UPDATE NameTable
  SET
    BirthYear=
    (
      SELECT BirthYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS BirthYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (1,3,7) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Births
      WHERE NameTable.OwnerID = Births.OwnerID
     ),
    DeathYear=
    (
      SELECT DeathYear
      FROM
      (
       SELECT
        E.OwnerID,
        CASE
          WHEN E.DATE REGEXP '[DR]..dddd.+'
          THEN CAST(MAX(SUBSTR(E.DATE,3,5),0) AS NUMERIC)
          ELSE 0
        END AS DeathYear, E.IsPrimary
       FROM
         Nametable N ,
         Eventtable E
       WHERE
         N.Ownerid = E.Ownerid AND E.Eventtype IN (2,4,5) AND E.Ownertype = 0 AND +N.IsPrimary
       ORDER BY E.OwnerID, E.EventType, +E.IsPrimary DESC
       ) AS Deaths
      WHERE NameTable.OwnerID = Deaths.OwnerID
    )
;

Discussions & comments from Wikispaces site


ve3meo

Inline comment: “A 162000 person database took over ‍100 minutes‍”

ve3meo
04 September 2018 01:44:23

ve3meo Dec 6, 2012

This is now questionable. I have seen Rebuild Indexes with RM 6.0.0.2 complete in 2-3 minutes on some versions of this large database. I suspect there may have been interference from a background backup procedure causing the earlier prolonged result.

Inline comments


ve3meo

Comment: This is now questionable. I have seen…

ve3meo
06 December 2012 22:34:20

This is now questionable. I have seen Rebuild Indexes with RM 6.0.0.2 complete in 2-3 minutes on some versions of this large database. I suspect there may have been interference from a background backup procedure causing the earlier prolonged result.

Birth Year Mis-Match #date #birth

Contents

    Lists individuals whose Birth Year is missing from the sidebar (and other views and reports where just the YEAR is outputted) or mismatches the value that has been stored in the Date field of the Birth fact. This is less of an issue as of RM 5.0.2.0, which incorporates an update of the BirthYear and DeathYear fields in the NameTable under the Rebuild Indexes tool in the menu File > Database Tools. However, the utility is still useful in drawing attention to where there are multiple Birth facts and where more than one is marked Primary.

    New: see Rebuild Indexes and Update Birth and Death Years for a batch procedure to match up the Birth and Death years with their facts.
    Download latest: BirthYearMisMatch.sqlRMtrix_tiny_check.png
    Ver 4 corrects an overcount of Birth facts due to alternate names.
    Ver 3 reports number of Birth facts per individual and suppresses facts with ‘UNKNOWN’ in the Date field.
    Ver 2 added checking for years before 1000.

    SQLiteDeveloperSQLeditor-BirthYearMisMatch.png
    SQLite Developer screen shot from Ver 1.

    This sql query uses some sqlite core functions to measure string length, extract a sub-string and compare to another string.

    1. In the above screenshot from the original version of the query, all the Birth Years shown are 0. The Date field could be worked on in RM4’s Edit Person dialog to change from free text (stored with a ‘T’ prefix) to a recognisable date format (stored with a ‘D’ prefix) and that should update the Birth Year.
    2. In the case of multiple Birth Facts for an individual, it is the last one saved that sets the Birth Year; any other Birth Fact not matching the Birth Year will be detected. The query could be modified to suppress reporting a mis-matched Birth Fact when another for that individual does match but it was thought to be useful to draw attention to the mis-match for review and possible correction or deletion.
    3. For RIN 681, a valid date format is stored for Jan 17 in the year 0! The mismatch that was detected compared ‘0’ to ‘0000’. The original version of the query reported a mismatch for all years <1000; later versions do not.

    Births of children as facts #birth #child #events

    Contents

      The birth of a child is a major life event for a person so I thought it would be worth adding to the stream of events query for a person. This query is designed to be pasted into the AllFacts4Person5.sql file – it follows the same columnar format – or it can be run on its own. For some unknown reason, it was unnecessary to mess with INDEXED BY and NOT INDEXED clauses to get fast results so this query should run on all flavours of SQLite3.

      File updated with labels for Parent name columns and tested on RM7 and #rm8. TH 2021-03-03

      Screen shot from SQLite Developer – note the filter drop-down list box on the Count column.