Names – Add Married #names #alternatenames #marriage #spouse

Update 2021-03-11: #RM8 version by Pat Jones added.

Having the married name of a person as an Alternate Name can be useful to those more familiar with it than the birth name as per LessTX's posting on the Wish List:
Add Married Name as Alternate Name and has been requested over the years in these postings found in the RootsMagic Forums. Or for looking up someone in the database from a source that cites a married name. While RootsMagic 6 currently does not index alternate names for reports, it does for its new Publish Online website generator. This query adds a Married name as an Alternate to those persons not already having one.

The Alternate Name fact generated fills the fields as follows:

Example of an Alternate Name fact created by the query.

Given Names: Husband's
Surname: Husband's
Name Type: Married
Prefix: Mrs.
Suffix: (Wife's_primary_surname, Wife's_Given)
Nickname: Wife's
Proof: empty
Date: MarriageDate
SortDate: After MarriageDate
Private: unchecked

The Name Index for RM Reports does not include Alternate Names but that for the Publish Online website of RM6 does; so Private is left unchecked.

The default sentence for this fact reads clumsily and adds nothing to a narrative; by setting the Custom sentence for each such fact to one space character " ", no sentence is outputted. However, the Alternate Name field values are outputted in Individual Summary and other table type reports.


Name-Add_Married-RM8 2021-03-11 rev by Pat Jones for #RM8

Name-Add_Married-RM7 RMtrix_tiny_check.png compatible with RM5 to RM7

Requires SQLiteSpy with the fake RMNOCASE collation.
USE RootsMagic File > Database tools > Rebuild Indexes after executing the query.

-- Name-Add_Married.sql
2012-11-24 Tom Holden ve3meo
Inserts an Alternate Name of type "Married" for female "Wife" spouses
in a "Family" where no Alternate Surname matches the male "Husband's" and
a (family) Marriage fact exists.
Prefixes with "Mrs.".
Suffixes the birth name in parentheses ().
Assigns the Marriage date and sortdate to the Alternate Name.
Sets the fact to Not Private and the fact sentence to ' ', assuming the user wishes to have
the Married Name show on the Publish Online website Name Index but not in narrative reports. It will
show in other reports but alternate names do not come out in report Name Indexes.
Because the NameTable uses the RMNOCASE collation, the query first REINDEXes
the database against the fake RMNOCASE collation. Following the query,
it is essential to use RootsMagic's File > Database tools > Rebuild Indexes, i.e.,
do not use this procedure on databases prior to RootsMagic 5.
-- Following deletes all Alternate Names of type Married
-- DELETE FROM NameTable WHERE NameType = 5 AND NOT +IsPrimary;
-- Name-Add_Married
REINDEX -- because following procedure does string matches on columns indexed using RMNOCASE
 Wife.OwnerID AS OwnerID,
 Husband.Surname AS Surname,
 Husband.Given AS Given,
 'Mrs.' AS Prefix,
 '(' || Wife.Surname || ', ' || Wife.Given || ')' AS Suffix,
 Wife.Nickname AS Nickname,
 5 AS NameType,
 Event.DATE AS DATE, -- set to ',' for undated, Event.Date to match Marriage date.
 CASE Event.SortDate & 1023
  WHEN 1023 THEN Event.SortDate --
  ELSE Event.SortDate + 1
 END AS SortDate,
-- Event.SortDate +1 AS SortDate, -- set to 9223372036854775807 to sort with undated Alt Names, Event.SortDate to match Marriage
 0 AS IsPrimary,
 0 AS IsPrivate, -- set to 1 to make Private
 0 AS Proof,
 0.0 AS EditDate,
-- 'After [person:hisher] marriage, [person] was also known as [Desc].' AS Sentence,
 ' ' AS Sentence, -- a blank space to prevent the default sentence from being outputted
 '' AS Note,
 Wife.BirthYear AS BirthYear,
 Wife.DeathYear AS DeathYear
FROM NameTable Wife
INNER JOIN FamilyTable Family ON Wife.OwnerID=MotherID AND +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
INNER JOIN EventTable Event ON Family.FamilyID = Event.OwnerID AND Event.EventType = 300
INNER JOIN PersonTable Person2 ON Wife.OwnerID = Person2.PersonID AND Person2.Sex=1
INNER JOIN PersonTable Person1 ON Husband.OwnerID = Person1.PersonID AND Person1.Sex=0
 Wife.Surname NOT LIKE Husband.Surname AND
 Wife.OwnerID NOT IN
-- Wives with Alternate Surnames matching Husband's Surname
SELECT Wife.OwnerID --, Wife.IsPrimary, Wife.Given, Wife.Surname, Husband.Surname
FROM NameTable Wife
INNER JOIN FamilyTable ON Wife.OwnerID=MotherID AND NOT +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
WHERE Wife.Surname LIKE Husband.Surname
   OR Wife.Surname LIKE Wife.Surname || '-' || Husband.Surname

2 Replies to “Names – Add Married #names #alternatenames #marriage #spouse

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.