FAMILY-HISTORIAN-USERS-L Archives

Archiver > FAMILY-HISTORIAN-USERS > 2007-01 > 1167687021


From: "Alan E. White" <>
Subject: [FHU] Functions in queries
Date: Mon, 1 Jan 2007 21:30:21 -0000


In v2 I had a census query similar to the "Census - The Missing Years" query
in the Query Store. One of the first things I wanted to do with v3 is to
modify my query to use the new INDI.CENS[year=1851] format. That works
great, and the result is similar to the "Census By Year (V3)" query in the
Query Store.

The next obvious progression is, for those censuses at which an individual
hasn't been found, rather than presenting a blank cell to present a search
aid such as the individual's estimated age at that census. What's really
required here is an if..else..endif construct but I can't find one of those.
It seems to me that CombineText should be able to perform the same function.
So, using 1851 as an example, the expression should look something like
this:

=CombineText("",%INDI.CENS[year=1851].DATE:YEAR%,"",EstimatedAgeAt(%INDI%,Da
te(1851,3,30),AVG,2))

This is accepted as a valid expression but it returns only one row: the
first individual which satisfies the rows condition. If I replace the above
with the original
INDI.CENS[year=1851].DATE:YEAR
or even
=ExistsText(%INDI.CENS[year=1851].DATE:YEAR%,"Yes")
then all individuals are returned.

I thought at first that it didn't like the nested function or the null
prefix and suffix so just for testing I changed it to:
=CombineText("a",%INDI.CENS[year=1851].DATE:YEAR%,"b","c")
This also only returns the first individual.

So, is this a bug, unsupported functionality, or am I doing something wrong?
Or is there another way to achieve what I'm seeking? Here's the full query
(without the above modifications), in case it helps.

[Family Historian Query]
VERSION=2.0
TYPE=INDI
TITLE=""
SUBTITLE=""
ORIENTATION=LANDSCAPE
TAG="Id","=RecordId()",,,21
TAG="Individual","INDI",,,0
TAG="Name","INDI.NAME:SURNAME_FIRST",,,109
TAG="Birth","INDI.BIRT.DATE:COMPACT",,ASC,54
TAG="1841","INDI.CENS[year=1841].DATE:YEAR",,,25
TAG="1851","INDI.CENS[year=1851].DATE:YEAR",,,25
TAG="1861","INDI.CENS[year=1861].DATE:YEAR",,,25
TAG="1871","INDI.CENS[year=1871].DATE:YEAR",,,25
TAG="1881","INDI.CENS[year=1881].DATE:YEAR",,,25
TAG="1891","INDI.CENS[year=1891].DATE:YEAR",,,25
TAG="1901","INDI.CENS[year=1901].DATE:YEAR",,,25
TAG="Death","INDI.DEAT.DATE:COMPACT",,,56
TAG="Spouse (1)","INDI.~SPOU>NAME:SURNAME",,,50
TAG="Marriage (1)","INDI.FAMS>MARR.DATE:COMPACT",,,70
TAG="Spouse (2)","INDI.~SPOU[2]>NAME:SURNAME",,,50
TAG="Marriage (2)","INDI.FAMS[2]>MARR.DATE:COMPACT",,,56
FILTER=REL,ADD,IF,Y,"",ANC,,

Thanks for any help.



This thread: