Logo Home    Page list

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

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:
Name-AddMarried-EditPersonCapture.PNG
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.

Download

Name-Add_Married.sql RMtrix_tiny_check.png
Requires SQLiteSpy with the fake RMNOCASE collation.
DO NOT USE ON A DATABASE EARLIER THAN RM5.
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
;
 
INSERT OR REPLACE INTO NameTable
SELECT
 NULL AS NameID,
 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
WHERE
 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
)
;
 
----- BE SURE TO REBUILD INDEXES IN ROOTSMAGIC ----
www.000webhost.com