* Expressions syntax in queries

[Peter Collier]

I haven't used queries very often thus far in FH, but they are useful and I want to get to grips with FH's expressions so
I can better manipulate them. To that end, I have been playing around to see what I can achieve. I'm fully conversant with Excel, which is helpful in terms of giving me ideas about how I might nest various expressons to get the output I'm aiming for, but it alo hinders because of course the syntax is quite different.

With the release 1921 census imminent, I thought a good exercise would be using a query to take stock of the individuals in my database and to get a broad overview of who was where 1841-1939, and who might have missing census/register records for those years. (Yes, I am sure I could have found and cribbed from someone else's similar query, but that would defeat the purpose of my learning exercise).

I have successfully applied various inclusions and exclusions to call only the records of all the individuals who would have been alive between 1841 and 1939, and I can get a nice result set showing separately either where everyone was living in each of the census years (using %INDI.CENS[year=****].PLAC:SHORT%) or, alternatively, that they have no record where I would expect there to be one (using =NotExistsText(%INDI.CENS[year=****]%,"Missing"). What I can't figure out, though, is how I might combine those two expressions into one (assuming I can) so that I don't need two columns for each census year. In other words, what would the single expression be that would return either the value %INDI.CENS.PLAC:SHORT% or the text "Missing" depending on whether or not that individual had a census record for that year?

Your bonus question for 10 points: I have called the records for all of the individuals alive between 1841 and 1939, but of course most if not all of those people are not alive through the whole of that century. Consequently I get a lot of false negatives, i.e. the query overzealously returns "missing" for a census record that can't exist because for the given year the indvidual was either not yet born or had since died. I could workaround by running separate queries for each census year, but I like being able to see an overview for all the censuses in a single table. Therefore, is there a way the expression could be further expanded to first check if an individual would have been alive in that census year? Then, for each of the single census year columns, each row would display either "N/A", the value of %INDI.CENS.PLAC:SHORT%, or "Missing", depending on whether the indivial in question wasn't alive in that census year, was alive and has a census record in a certain place, or was alive but doesn't have a census record in the database.

[tatewise]

Yes, that is all feasible but a little complex.
Perhaps I should not give you a fully worked answer but clues to how to proceed.

The first question of how to choose between PLAC:SHORT and Missing needs a conditional function.
Since you have found the =NotExistsText(...) function I presume you know where to look for other functions.
One option is the =TextIf(...) function where its condition parameter would be Exists(%INDI.CENS[year=****]%)
A better option is the =CombineText(...) function because its condition is %INDI.CENS[year=****].PLAC:SHORT%

The bonus question answer is just an extension of the above options because their parameters can be functions.
So you can nest =TextIf(...) and =CombineText(...) inside themselves ad infinitum (well not quite, there are limits).

The conditional parameters would compare %INDI.BIRT.DATE% and %INDI.DEAT.DATE% with the exact Census Date using the =DateAt(...) function, the IsTrue(...) function, and standard < and > operators.

See FHUG Knowledge Base Understanding Expressions.

[Peter Collier]

Thanks for the pointers, Mike, they got me on my way. I came up with the following, which seems to work on the records I'm looking at (This is for the 1841 column; the other columns are the same save for the census dates). You don't see any potential pitfalls in it that I may have overlooked, do you?

=TextIf (Bool ((DateAt (1841,6,6) < EstimatedBirthDate (%INDI%,EARLIEST,2)) or (DateAt (1841,6,6) > EstimatedDeathDate (%INDI%,LATEST,2))), "Not living", CombineText(, TextPart (%INDI.CENS[year=1841].PLAC%,1,2,TIDY), , "<Missing>"))

[tatewise]

Yes Peter, that looks perfect. It even treats people with no estimated Birth/Death date as living by default.