* Queries - AND vs OR for multiple expressions

[StephensKS]

When writing a query it seems that the system assumes an "OR" function. For example, I want a list of all individuals with a given name that includes "Sarah" and were born in the year 1840.

So I wrote the query:
And if %INDI.NAME:GIVEN_ALL% contains "Sarah"
And if %INDI.BIRT.DATE:YEAR% equals 1840

I get all the Sarahs and all the individuals born in 1840. (Not what I'm looking for.)

I attempted to rewrite using the "Exclude if" but that is very counter intuitive. And there are no "does not contain" or "does not equal" functions that would be needed to make it work.

There was a long topic on this forum that seemed to dance around this subject but did not state explicitly how to require that multiple conditions must be true to include the individual.

Surely a query such as this can be created.... What am I missing?

[LornaCraig]

Your first line is correct, and selects all the individuals called Sarah.

Each row in the query operates on the set which was left after the previous operation. So working on that list, your second line then needs to remove any of those people who were not born in 1840, so you need

Exclude unless %INDI.BIRT.DATE:YEAR% equals 1840

[tatewise]

I suspect you have not worked through all the Understanding Functions sections of Help, but do appreciate it is a steep learning curve, which is to be expected for such Advanced Topics.

The Query Window, Rows tab behaviour is also described in detail but is somewhat unconventional.

You are correct that successive Add if and Add unless filters are effectively OR operations.

Successive Exclude if and Exclude unless filters are effectively AND operations.

The Add unless and Exclude unless filters are the NOT variants.

i.e.
Add unless %INDI.NAME:GIVEN_ALL% contains "Sarah" adds all whose Given Names do NOT contain "Sarah".

There are also and , or , and not(...) boolean operators to compose complex expressions, which often need to be enclosed in the =IsTrue(...) function.

There are many examples in the Help pages and the FHUG KB Understanding Expressions.

[Mark1834]

FH Queries didn't make much sense to me initially, as I had decades of experience working with more conventional databases, where a query would be framed along the lines of "SELECT foo FROM bar WHERE condition 1 AND condition2".

The recommended way of building FH Queries is to view each row selector as a completely independent statement that add or remove records from the result set, rather than getting too involved in converting them to more conventional logic relationships.

Viewed that way, "add individuals called Sarah", followed by "add individuals born in 1840", gives the result you observed. If you start with "add individuals called Sarah", you have an initial result set that is more than you need, so it is followed by "exclude unless born in 1840" to get rid of the extra rows.

Viewed that way, it is completely logical, but it is just different to conventional query logic. Each individual statement can be temporarily disabled for testing purposes as you develop the query.

[StephensKS]

Thanks all. I didn't consider "Exclude unless" which appears to be the equivalent of "and". I too have worked with databases for decades and haven't seen quite this approach. Live and learn I guess...