* Query Expression to display "preferred" marriage data

[Little.auk]

Hi,

I am trying to build a custom query to display the media records linked to Birth, Marriage and Death events for my ancestors.
The expressions %INDI.BIRT[1].OBJE[1]>% and %INDI.DEAT[1].OBJE[1]>% work for Births and Deaths, but I have a problem with Marriages, because some ancestors married more than once, so in some cases I need SPOU[1] (FAM[1]) data for the ancestral line and in others I need SPOU[2] (FAM[2]) as per examples below.

Rose Bowles – SPOU[1] Harry Heath – SPOU[2] William Farmer == SPOU[1], (FAM[1]) is the ancestral line.
Emma Smith - SPOU[1] William Taylor – SPOU[2] William Rollin == SPOU[2], (FAM[2]) is the ancestral line.

I have flagged the “preferred” marriages, but I can’t find an expression to make the “preferred” marriage data display in the query.

What expression do I need to use to set the focus to the correct marriage / spouse?

NOTE: I also have other “SPOUSE” specific data, in particular, a Custom “Rel ID” assigned to each family that also needs to be focussed on the correct relationship.



Peter Rollin

[tatewise]

Let me check what you are requesting:
Rose Bowles – SPOU[1] Harry Heath – SPOU[2] William Farmer == SPOU[1], (FAM[1]) is the ancestral line.
Emma Smith - SPOU[1] William Taylor – SPOU[2] William Rollin == SPOU[2], (FAM[2]) is the ancestral line.
Thus Harry Heath is an ancestor of the File Root but William Farmer is not?
Also William Rollin is an ancestor of the File Root but William Taylor is not?

What type of Query are you building? Individual or Fact?

If using an Individual Query you will need separate columns for Birth, Death, 1st Marr, 2nd Marr, 3rd Marr, etc.
You will need tricky Expressions for the multiple instances of Marriage to find the ancestral line.

If using a Fact Query it gets easier and you get all the details in just a few columns.
The Rows tab filters needed will include Birth, Death and Marriage events,
e.g.
Add if =FactLabel(%FACT%) matches Birth
Add if =FactLabel(%FACT%) matches Death
Add if =FactLabel(%FACT%) matches Marriage

Exclude those without linked Media,
e.g.
Exclude if %FACT.OBJE[1]>% is null

Exclude those whose owner is not an ancestor,
e.g.
Exclude unless =IsTrue(IsAncestorOf(FileRoot(),FactOwner(%FACT%,1,MALES_FIRST)) or IsAncestorOf(FileRoot(),FactOwner(%FACT%,2,MALES_FIRST))) is true

[Little.auk]

I am relatively new to FH, and have not yet looked at fact based queries. I am using an Individual query, I basically took the "Individuals with ID" standard query and built on that as a custom query.

I have separate columns for Birth, Marriage and Death, and the query expressions for Births and Deaths work fine, as they apply to a single individual.

Your check is correct, William and Harry are direct line. However, I have no problem with them as they both married only once, so their query results work fine. Rose Is also OK as Harry was her first husband, so the basic query using SPOU[1] works for her.

So my problem is with Emma Smith, here using SPOU[1] returns data relating to her first husband, William Taylor.

I have moved to FH from Family Tree Maker, in FTM (and in Legacy Family Tree) there is a facility to select a preferred spouse when there are multiple marriages, then all queries automatically work with the preferred couples data.

I don't want to exclude those without linked media as one purpose of this query is to identify those people (particularly direct line) who could have linked media for B,M and D (i.e. scanned certificates or registration copies) but don't. If I do use a filter it will be based on Year of Birth.

[tatewise]

As you have discovered, Expressions get complicated with multiple Marriages in Individual type Queries.
You would have to use the IsAncestorOf() function on the Spouses of each Marriage to determine which are ancestors.
Also, you only discuss two Marriages, but it is quite common for there to be three or more.

It was for this reason that Fact type Queries were introduced years ago.
Instead of querying each Individual record, they query each Fact.
So it does not matter how many instances of each fact exist, they will all be queried in a similar way.
See the FHUG Knowledge Base An Overview of Queries especially under Custom Query Types.

See the standard Query > Facts and Events > All Facts for how the two owners of Family facts like Marriage are shown.
See the FHUG KB Download All Facts Filter by Label to see how Facts can be filtered by using the Rows tab.
This leads to a much neater solution as explained in my previous posting.

If you don't want to filter on whether a Fact has any Media then omit: Exclude if %FACT.OBJE[1]>% is null
But if you only want to list those Facts without Media then use: Exclude unless %FACT.OBJE[1]>% is null

BTW:
Attaching source documents directly to Facts is somewhat unconventional.
Usually, those documents are attached to Source records or Citations associated with the Facts.
So, for example, a Birth Certificate attached to a Source record would have Citations for not just the Birth Event but also the parents' Occupation and Residence facts. Similar associations would apply to Marriage and Death certificates and especially Census records.

[BillH]

Mike,

If the fact flag "preferred" has been set on the correct marriage, is there a way to interrogate that fact flag in a query?

Bill

[tatewise]

Yes, in an Individual Query you have to test each FAMS instance and assume only one Marriage instance:
%INDI.FAMS[1]>MARR[1]._FLGS.__PREFERRED%
%INDI.FAMS[2]>MARR[1]._FLGS.__PREFERRED%
%INDI.FAMS[3]>MARR[1]._FLGS.__PREFERRED%
and so on...

In a Fact Query its Data Reference would be %FACT._FLGS.__PREFERRED%
But it would be difficult to apply it to just Marriage facts.

To determine Data References, use the Fields pane of the Columns tab of the Query.
Set the menu option for the display box at the bottom to Show Both in Box.

[BillH]

So, if you had more than one marriage and one was marked as the preferred one, how could you select that preferred marriage if you didn't know which one was the preferred one and how many marriages there were in total?

Would you use:

Add if...
%INDI.FAMS[1]>MARR[1]._FLGS.__PREFERRED%
is set

and

Add if...
%INDI.FAMS[2]>MARR[1]._FLGS.__PREFERRED%
is set

etc. enough times to catch the most marriages you might have?

Would there be an easier way?

Bill

[BillH]

I also see this in the help:

It is possible to set a flag on events and attributes to indicate that a fact is a preferred fact. For example, you may have recorded numerous occupations for a given person, but one of them may be the occupation that they were most closely associated with; and you may choose to mark this occupation as the preferred one, by setting the Preferred flag for that fact. You can reference a preferred fact by using [preferred] as the index. e.g.:

%INDI.OCCU[preferred]%

If a given person has no occupation which has the Preferred flag set, this will return the first one.

Sometimes you may want to just find a Preferred fact - that is, return nothing if there is no fact of that type with the Preferred flag set. To do that, you can use the [prefonly] index. e.g.

%INDI.OCCU[prefonly]%

This will match the first occupation which has the 'Preferred' flag set, or return nothing if there is no 'Preferred' occupation.

I tried using this with a Family query, but I couldn't figure out how you might be able to select a preferred marriage using this (if it is even possible).

Bill

[tatewise]

In a Family type Query the Rows tab filter could be:
Add unless %FAM.MARR[prefonly]% is null
which (assuming only single instance Marriage events) is identical to:
Add if %FAM.MARR[1]._FLGS.__PREFERRED% is set

If you used Exclude if %FAM.MARR[preferred]% is null instead it would simply list all Family records that have a Marriage event because if the Preferred flag is not set then %FAM.MARR[1]% used instead.
%FAM.MARR[preferred]% is useful to choose between multiple Marriage instances in one Family record, which rarely arises.

Similarly, in an Individual type Query the Rows tab filter could be:
Add unless %INDI.FAMS[1]>MARR[prefonly]% is null
which (assuming only single instance Marriage events) is identical to:
Add if %INDI.FAMS[1]>MARR[1]._FLGS.__PREFERRED% is set
But that must be repeated for FAMS[2], FAMS[3], etc...

However, I'm not sure any of that helps much in solving Peter's criteria unless the following Data Refs prove useful.
%INDI.FAMS[1]>MARR[1].OBJE[1]>%
%INDI.FAMS[2]>MARR[prefonly].OBJE[1]>%
It would need some careful analysis to work out how to use them.
I still think a Fact Query is the way to go.

[BillH]

Mike,

Ok... thanks for the explanation. I agree, it might not help Peter. I was just curious as I had never used this type of thing in a query before and was trying to wrap my head around it.

Thanks,
Bill

[Little.auk]

This is an interesting thread guys, but all I am reading is that the simple task of selecting a preferred spouse in a query is not possible, and although a marriage can be set as "preferred" there is no simple way of using this in a query.

This means that, because Emma Smith is William Rollin's second wife, any "family" based facts in a query will relate to her first marriage (to William Taylor). I think this is a serious issue that needs to be addressed -

I have used several Family History programs over the years, and every one had the option to select a preferred spouse in the main data entry screen when there were multiple marriages. All queries and reports then used this spouse/family automatically, regardless of whether it was first, second or greater marriage. The FH help files say this is not an option - it should be.

Luckily most of my ancestors married young, so virtually all are first marriage families, in fact Emma is the only direct line ancestor where the line is through her second husband.

I am getting round the problem by inserting a second "Marriage Media" query column focussed on FAM[2]. Not what I really want, but at least I can see the data I want.

Regarding "Sources" - I am migrating from Family Tree Maker to FH, so I have lots of media to bring across. I am working on B, M and D data (mainly scanned certificates and parish records), linking these files to facts as a quick and easy way to get them into FH so that I can see them and work with them. It's a bit of a sorting process to get media linked to a key person and a fact, which, with the amount of media I have, will make it a lot easier to create the sources.

[Little.auk]

Hi

Apologies for resurrecting this thread, but I thought I should post a very simple solution I have found that solves my specific "preferred marriage" problem.

Attached is an image showing the Focus Window and Property Box for my great grandmother Elizabeth Smith. Her first marriage was to William Marriott Taylor and her second to my great grandfather William Rollin.

Highlighted in red in the Property Box is an "up - down" selection box which can be used to change the order of the spouses in the tabs in the Property Box - and which also automatically changes the order in the Focus Window, in this case showing William Rollin above William Taylor, even though he was her second husband.

The key thing is that the spouse displayed on top in the property box becomes SPOU[1] as far as FH is concerned.

My queries principally involve direct line ancestors and their families, so as long as I ensure that I arrange them to be top of the focus screen SPOU[1] works correctly for everyone.

I appreciate it is not a complete answer, as it simply moves the incorrect data to a different spouse, but for what I want it is does the job. If I need a query focussed on a different spouse It is easy to move them up to be SPOU[1].

[Gowermick]

Whilst that may solve your immediate problem (i.e re preferred spouse), it does means putting the marriages out of chronological order and their children.
Just a point to ponder

[Little.auk]

Having checked a few reports, I don't see that as an issue, in fact, it is a bonus having the higher relevance "Direct Line" family appearing first, and the "Step" family coming below it. The chronology for an individual's facts is not affected.

If I were to find an issue with a report, it is very easy to change the sequence.