* Creating Surname sort in a fact query

[johnmorrisoniom]

I am trying to modify a fact query for burials to have a hidden "Surname" column to sort on.

I have the expression

Code: Select all

=TextIf(IsTrue(FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.SEX') = "Female"),FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.FAMS[last]>HUSB>NAME:SURNAME'),"")

but am at a loss as to haow to modify it to include Male surnames for male fact owners (FactOwner seems to be hardly documented in the help at all, and there is no expression builder for columns, just for rows)

I think the extra code goes in the last set of String quotes, but what should it be

Many thanks in advance
John

[jimlad68]

A lot of lua/query is a black art to me (I modify other examples + trial and error), but would a hidden/buddy column help?
see Surname first for %FACT%,1,MALES_FIRST (12262)
another example:

Code: Select all

[Family Historian Query]
VERSION=3.0
TYPE=FACT
TITLE="All Facts with 2 types of PLACe record for quick jump to place in record or PLACe record"
SUBTITLE="%#x"
ORIENTATION=LANDSCAPE
TAG="Fact date","FACT.DATE:COMPACT",,,64
TAG="Source","FACT.SOUR>",,,45
TAG="Fact","=FactLabel(%FACT%)",,,33
TAG="ID1","=RecordId(FactOwner(%FACT%,1,MALES_FIRST))",,,22
TAG="Owner1","=FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')",,,133
TAG="Owner1B","=Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')",BUDDY,,80
TAG="ID2","=RecordId(FactOwner(%FACT%,2,MALES_FIRST))",,,25
TAG="Owner2","=FieldText(FactOwner(%FACT%,2,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')",,,55
TAG="Owner2B","=Field(FactOwner(%FACT%,2,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')",BUDDY,,80
TAG="FACT-Place-click for related FACT","FACT.PLAC",,,27
TAG="RECORD-click for PLACe Record","FACT.PLAC>",,ASC,437
TAG="p1","=TextPart(%FACT.PLAC%,1,1,STD)",,,75
TAG="p2","=TextPart(%FACT.PLAC%,2,1,STD)"
TAG="p3","=TextPart(%FACT.PLAC%,3,1,STD)"
TAG="p4","=TextPart(%FACT.PLAC%,4,1,STD)",,,67
TAG="p5","=TextPart(%FACT.PLAC%,5,1,STD)",,,75
TAG="p6","=TextPart(%FACT.PLAC%,6,1,STD)",,,86
TAG="p7","=TextPart(%FACT.PLAC%,7,1,STD)",,,136
TAG="p8","=TextPart(%FACT.PLAC%,8,1,STD)",,,134
TAG="p9","=TextPart(%FACT.PLAC%,9,1,STD)",,,51
TAG="p10","=TextPart(%FACT.PLAC%,10,1,STD)",,,69
FILTER=GEN,ADD,IF,Y,"","FACT.PLAC>",,"contains",TEXT,""

[tatewise]

John, you have not made your requirement clear.

You say you want "a fact query for burials to have a hidden "Surname" column to sort on".
In which case =FactOwner(%FACT%,1,MALES_FIRST) with Sort: Ascending and Type: Hidden (or perhaps Buddy).
CORRECTIVE EDIT: That expression should be:
=FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST') to list with Surname first.
=Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST') to list with Surname last.

But your expression involves looking for female husband's surname and you don't mention that requirement.

However, the following expression does the job assuming a woman was married at time of death:

=TextIf( FieldText(GetRecord(%FACT%),'INDI.SEX') = "Female", FieldText(GetRecord(%FACT%),'INDI.FAMS[last]>HUSB>NAME:SURNAME'), FieldText(GetRecord(%FACT%),'INDI.NAME:SURNAME') )

But she might have been a spinster, so the following is better:

=TextIf( Exists(FieldText(GetRecord(%FACT%),'INDI.FAMS[last]>HUSB')), FieldText(GetRecord(%FACT%),'INDI.FAMS[last]>HUSB>NAME:SURNAME'), FieldText(GetRecord(%FACT%),'INDI.NAME:SURNAME') )

Note the use of the simpler GetRecord(...) instead of FactOwner(...), which only differ for Family facts such as MARRiage and DIVorce. Also in this case IsTrue(...) is not required.

GetRecord(...) and FactOwner(...) are documented with all the other functions in Help > Using Family Historian > Advanced Topics > Understanding Functions or a Help > Search for Functions or GetRecord or FactOwner.

The Columns and Rows tab both only provide a Data Reference Assistant and neither offer Expression builders involving Functions.

Yes, the Rows tab has Condition, Operator, Value options but they are filter conditions not Expressions.

There is some assistance in how_to:use_pspad_to_format_functions|> Use PSPad to Format Functions.

[johnmorrisoniom]

Thanks Mike,
You have got the idea of what I am after. (ie Surname at death)

The first expression produces a correct surname at death for all males and Married females, getting close, but as you pointed out, does not work for unmarried females.

The second expression always produces a surname, but gives the maiden name for married females.

What I am trying to achieve is a column showing the individuals Surname at death (It doesn't need to be hidden, but I can use a hidden copy to give the output sorted the way I want)

[tatewise]

Sorry, a bit too much haste, and not enough checking.
The Exists(FieldText(...)) function should be just Exists(Field(...)).

[johnmorrisoniom]

That's cracked it, Thank you for your help Mike.

[tatewise]

I have given this a little more thought and it is still not entirely satisfactory.

The expression assumes if a person has a male family partner INDI.FAMS[last]>HUSB, then their surname at death will be of that male partner INDI.FAMS[last]>HUSB>NAME:SURNAME. i.e. It assumes they were a married heterosexual couple, and have adopted popular western name conventions, none of which may be true.
e.g.
They may not have been married, so the expression needs to check for a MARRiage event, or the _STATus of Never Married or Unmarried Couple, etc. But in general, it is all still educated guesswork.

In fact this reveals an often discussed significant deficiency in the GEDCOM specification. Facts such as Occupation and Residence that change over time are supported, but Names that change over time are not supported without misusing the Title attribute or creating a custom attribute. The snag with both those is they do not offer all the NAME characteristics such as / separating surname, and all the sub-fields such as Prefix, Suffix, Nickname, etc.

[CathyC]

"You say you want "a fact query for burials to have a hidden "Surname" column to sort on".
In which case =FactOwner(%FACT%,1,MALES_FIRST) with Sort: Ascending and Type: Hidden (or perhaps Buddy)."

I tried this FactOwner function in a query but it displays the people in alphabetical order of their first name (ie starts with Ada & finishes with William, regardless of surname. Please can you tell me where I am going wrong?

[tatewise]

Yes, sorry Cathy, that is my fault, but in mitigation I was so focused on the other aspects that I forgot how that function does NOT show Surname first, so try one of the following:

• To list with Surname first, in Surname order:
  =FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')
• To list with Surname last, but still in Surname order:
  =Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST')

Just be aware that for Family facts such as Marriage and Divorce the name shown is that of the Husband and not the Family record.

[CathyC]

Many thanks Mike, that's just what I needed!
Cheers, Cathy