Tools – Problem Search – Spouse Order Questioned #tools #spouse

The order in which multiple spouses and families are listed in RootsMagic’s Individual Report and in narrative reports is determined by the order that they are also shown in the dropdown list from the Spouses button in the upper left of the Main View window. Some prefer that this order should always be chronological while others may want to put first the spouse that is in their line of inquiry, regardless of that spouse’s position in the series of marriages for the person of interest. The determination that the output will be in the desired order cannot be proven until the proof-reading of a perhaps voluminous report. There is no tool in RootsMagic 6 to search for such problems. This SQLite query attempts to provide such a tool for finding instances where the order of spouses does not correspond with the order of spousal (family) events such as Marriage, Divorce, Census (family) and Residence (family).

This is an example of results from the query. The first column gives the record number or RIN of the person with multiple spouses whose order appears to be inconsistent with chronology. The second column gives the color code number for that person and the third merely indicates whether the multiple spouses are husbands or wives.

I would use the RIN with RM Explorer to find, inspect events to get the sense of what the spouse order should be and maybe even fix the Sort Date of certain family events that are out of order. Then select the Person in the Main View followed by the Spouses button and Rearrange if necessary. If I have a great many spouse orders in question, I would narrow my focus to those persons in my line of interest by using color coding for that line. Then I could click on the Color column in SQLiteSpy to sort by color number and work only on those RINs having my color of interest.

SpouseOrderQuestioned.sql

/* SpouseOrderQuestioned.sql
2013-01-08 Tom Holden ve3meo
 
Lists RINs of persons with multiple spouses (families) whose
spouse order may be inconsistent with the SortDates of the
person's family events. This affects the order in which spouses
are listed in RM reports.
 
It's possible that this query can throw up some false positives
due to undated family events; it may also overlook some positives,
depending on the order that family events were entered.
 
*/
SELECT *
FROM
(
SELECT FatherID AS RIN, Color, 'Wives' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Wives,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON FatherID = PersonID
GROUP BY F.FamilyID
ORDER BY FatherID, SortDate
)
GROUP BY FatherID
)
WHERE Wives > 1
)
WHERE WifeOrder <> Wives +1
 
UNION
 
SELECT MotherID AS RIN, Color, 'Husbands' AS 'Spouse order questioned'
FROM
(
SELECT *
FROM
(
SELECT COUNT() AS Husbands,* FROM
(
-- 1 Family Event per Family
SELECT F.FamilyID, FatherID, MotherID, HusbOrder, WifeOrder, Name, SortDate, Color
FROM FamilyTable F
INNER JOIN EventTable E
ON F.FamilyID = OwnerID AND E.OwnerType = 1
INNER JOIN FactTypeTable FT
ON EventType = FactTypeID
INNER JOIN PersonTable ON MotherID = PersonID
GROUP BY F.FamilyID
ORDER BY MotherID, SortDate
)
GROUP BY MotherID
)
WHERE Husbands > 1
)
WHERE HusbOrder <> Husbands +1
)
;