* Fact based queries

[Little.auk]

I am trying to get to grips with FACT based queries, but am having problems working out how to insert "Individual" data,
in particular I am trying to include a Custom ID (i.e. equivalent of %INDI.REFN% in an individual query).

For Individual queries I have been able to build the expressions I need by drilling down the Fields List, but that doesn't seem possible for fields such as Custom ID in Fact based queries. I clearly need to build an expression that links the fact back to the Fact Owner, but I can't work out how to do that, and haven't been able to find a Fact Based Query Syntax reference to help me.

[tatewise]

You are correct that there is no Query syntax assistant in the same way that there is a Data Reference Assistant.
The trick is to discover the Fact owner record and then you can reference the Custom Id REFN field.
Study the standard FH Query > Facts and Events > All Facts and see how the FactOwner is listed.
See FHUG Knowledge Base An Overview of Queries and under Sorting a Fact Query on Owner Surname it shows how to access the owner's INDI.NAME field and the same technique works for the INDI.REFN field.

[Little.auk]

Thanks Mike,

A simple cut - paste - edit job and the query is working!

I am using the All Facts query as my custom template and I want to sort \ Filter by Individual. I couldn't use FactOwner name, as there are loads of duplicated names in my tree ( e.g. Twelve William Rollins). Custom ID was the obvious answer, I use most of my queries exported into Excel, and Custom ID is my cross reference link field.

I already have the Ahnentafel query that you helped me with a few weeks ago, but I couldn't work out how to strip that down and convert it to what I needed.

[tatewise]

Have you considered using the Record Id instead of the Custom Id?
It is guaranteed to be unique and stays with the same Individual record as long as you don't use the renumbering tool.
That would save you the inconvenience of having to add a Custom Id to each person.
Use =RecordId(FactOwner(%FACT%,1,MALES_FIRST))

However, perhaps you have a special format to your Custom Id that helps you identify related Individuals, in which case the Record Id won't be so useful.

[Little.auk]

Hi Mike,

I created custom IDs when I was using Legacy Family Tree many years ago, and have carried it on through Family Tree Maker and now into FH. They are as familiar to me as names are, so I also use them when naming Media files. It keeps file names short, but also identifies who they relate to.

I use FAM-nnn for relationships and IND-nnn-nn for Individuals. Children of family FAM-001 are numbered IND-001-01 etc. (the -01 relates to the sequence they were entered onto the tree not birth order).

I also export queries to work on in Excel, and use the Custom ID for LOOKUPS and Data links.

Going back to Queries, I have found a problem with the standard All Facts query, in that it doesn't actually return "ALL" facts. The "Fact Type" column only shows "Marriage" facts for Husbands, and not Wives.

The ability to show all fact types in a single column is somewhat negated when a vital fact is missing for half of the married individuals listed in the "Individual 1" column.

Is there any way to do it, or do I need to create two separate queries, one for MALES_FIRST, filtered to just show males and another changed to FEMALES_FIRST, filtered to only show females?

[tatewise]

You have fallen into the Family Fact trap, which perhaps needs some explanation.

When you view the Facts in the Facts tab of an Individual you will notice that the bullets on the left vary for different Facts.
Blue bullets signify Individual Facts that are held in the Individual record, such as Birth, Census, and Death.
Red bullets signify Family Facts that are held in their Family record(s), such as Marriage and Divorce.
Grey bullets signify Timeline Facts that are held in some other relative's record.
Blue arrows signify Fact Witnesses that are defined in the Principal Fact.
So you see there is a mixture of facts derived from a wide variety of records and not just the current Individual.

If you now switch to the All tab, you will only find the blue bullet Individual Facts and none of the others.

Are you familiar with the concept of Family records?
If not I may have to explain them further.
However, a Family Fact such as Marriage only exists once and is shared by the spouses.
If you change the Date or Place or Note in either spouse's Individual Facts tab then it changes the single Marriage fact and the change appears in the other spouse's Individual Facts tab.

So in a Fact Query, it only lists the Marriage fact once because it only exists once in the Family record.
There are NOT two Marriage facts, but there are two FactOwners:
That is why the All Facts query has Fact Owner 1 and Fact Owner 2, where that 2nd owner only applies to Family Facts.

Thus you need an extra Column for FactOwner(%FACT%,2,MALES_FIRST) to list the other person's Custom Id.

[Little.auk]

I am familiar with the concept of family records, however, my background is with true relational databases, where there is no differentiation between "Individual" and "Fact" based queries. In any query or report, there would need just one "Individual" column and the fact would be shown for both people.

If I simply change all of the "MALES_FIRST" entries to "FEMALES_FIRST" will this reverse the priority and show the marriage fact for all women? If so I can get what I want by running two filtered queries and combining them in Excel.

[tatewise]

Yes, "FEMALES_FIRST " will work as you expected, and the two queries between them will not only cope with heterosexual marriages but also same-sex partnerships.

[Little.auk]

Sorry to be a nuisance Mike, but I am now struggling with the Rows Filter syntax to exclude Males\Females.

I have added a column to the ALL FACTS query headed "Sex", with the expression -

=Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.SEX').

This is populating "Male" and "Female" OK, so I now want to filter on this column to exclude those records where the value equals "Female". I can then adapt a copy of the query for Females.

I know that the syntax I need is - Exclude if EXPRESSION matches "Female" -, but if I try to use the expression above the only operators I am offered are IS, ISNULL and EXISTS.

How do I need to edit the expression to make it work.

[tatewise]

This is one of those cases where FH is not sure what data type that expression is returning. I think it is actually returning a pointer to the Sex field rather than its textual value. So you need to use one of the Type-Specifier Functions, which in this case is =Text().
i.e.
=Text(Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.SEX'))
OR
You could use the text equivalent of the =Field() function:
=FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.SEX')

Also if you set the Value as a Parameter it will prompt whether you want Male or Female.