* How to create a query using Boolean logic

[psinacio]

Dear All,

How do I create a Query using Boolean logic?

I need to list all my ancestors that do have a "surname" but also ancestors whose father has a "surname".

At the end I will "eliminate people without surnames, whose father also has no surname....

I manage to create the following query:
But the two last conditions should work together as an AND. I need both to be true together.

Capturar.JPG (17.01 KiB) Viewed 12293 times

Regards and Thanks a lot.

[tatewise]

They are actually working together as an AND, and both must be true.

But I suspect you have overlooked the cases where there is no Father or no Father's Name at all, in which case the test for %...:SURNAME% matches '' fails because it is null.

So you need another Row filter for:
Exclude if %INDI.~FATH[1]>NAME[1]% is null

[Jane]

You might find it easier to either reverse the logic or use the AND function, although I suspect you want to do
"Is Ancestor and (Indi:name:surname is not null or INDI.~FATH>NAME:SURNAME is not null

I think you want everyone with a Surname plus everyone whose Father has a Surname.

So I would

Add if INDI.NAME:SURNAME is not null
Add if INDI.~FATH>NAME:SURNAME is not null
exclude unless is Ancestor.

[psinacio]

Hi Tate,

Maybe I did not understood.
But when doing this the query it is eliminating more people than it should.

I tried this:

Capturar2.JPG (16.33 KiB) Viewed 12267 times

Doing like you said it is eliminating all people without surnames, but it should only eliminate people without surname, IF there is nos father...

Ex.

Marie Anne // has no father and should be eliminated.
Barbara // has a father named Karl /Langer/ and should not be eliminated.


Exclude if %INDI.~FATH[1]>NAME[1]% is null

This will eliminate all people with no father, even if they have a surname...

[psinacio]

Hi Jane,

Reverse order, may work. You are right...

How do I add this "is not null"

I could not find it on the list...

Sem título.png (6.09 KiB) Viewed 12263 times

[tatewise]

What I actually said was to use your existing filters, and ADD the is null filter.
Reversing the order of your two Exclude if ... matches '' filters makes NO difference.

You also misunderstand how filters work.
They operate on the current Result Set not the whole database.
So for your filters the first one fills the Result Set with all Ancestors.
Exclude if %INDI.NAME[1]:SURNAME% matches '' excludes everyone with no Surname from the Result Set of Ancestors.
So now the Result Set holds only Ancestors who do have a Surname.
Exclude if %INDI.~FATH[1]>NAME[1]:SURNAME% matches '' excludes those whose Father exists with a Name but no Surname.
So now the Result Set holds only Ancestors who do have a Surname AND either no Father's Name, or no Father's Surname.
Exclude if %INDI.~FATH[1]>NAME[1] is null excludes from the Result Set those whose Father's Name does not exist.
So the Result Set ends with Ancestors who have a Surname AND whose Father also has a Surname.

BTW: You also may need to consider initial Ancestors who have no Name at all.
i.e. Exclude if %INDI.NAME[1] is null

However, your latest example seems to suggest you want the filters to OR together.
i.e. List Ancestors whose Surname exists OR whose Father's Surname exists.
Is that correct?

Instead of using Add if use Exclude if
i.e. Add if ... is not null is the same as Exclude if ... is null.

[LornaCraig]

How do I add this "is not null"

You need to use Add Unless.... Is null
This is logically equivalent to Add if... not null.

(This is if you are using Jane's method, where you start with an empty set, and the last step is to Exclude unless an Ancestor.)

[psinacio]

Yes,

I am trying:

Ancestors whose Surname exists OR whose Father's Surname exists.

[LornaCraig]

Add unless INDI.NAME:SURNAME is null
Add unless INDI.~FATH>NAME:SURNAME is null
Exclude unless Ancestor of .....

[tatewise]

Sorry Lorna but that does not work, because it includes people that exist with Names but without Surnames.
So the filters always need tests for NAME is null and for SURNAME matches ''

[psinacio]

Hi

I am still not achieving the results.
I tried the following:

Capturar4.JPG (17.55 KiB) Viewed 12233 times

But I am getting many people as a result withou surnames, or whose father has no surname...

Am I missing something?

Pedro
PS. How do you copy the expressions? I am always using printscreen here...

[psinacio]

Almost there:

Capturar5.JPG (16.68 KiB) Viewed 12229 times

But when a person with no surnames has a father also with no surnames, they are listed...

Pedro

[tatewise]

To copy the Expression part use the standard right-click Copy and Paste or the standard shortcuts Ctrl+C and Ctrl+V

I have resorted to a full boolean logic expression using the =IsTrue(...) function :-
Add if
=IsTrue((Exists(%INDI.NAME%) and Not(%INDI.NAME:SURNAME% = "")) or (Exists(%INDI.~FATH>NAME%) and Not(%INDI.~FATH>NAME:SURNAME% = "")))
is true

That must be followed by the usual :-
Exclude unless an ancestor...

OR you can do it the other way round :-
Add if an ancestor...
Exclude unless
=IsTrue((Exists(%INDI.NAME%) and Not(%INDI.NAME:SURNAME% = "")) or (Exists(%INDI.~FATH>NAME%) and Not(%INDI.~FATH>NAME:SURNAME% = "")))
is true

[LornaCraig]

Sorry Lorna but that does not work, because it includes people that exist with Names but without Surnames.
So the filters always need tests for NAME is null and for SURNAME matches ''

But Pedro has repeatedly said it is the existence of the surname he is interested in. I took this to mean that if someone has a name but no surname they should be treated in the same way as someone with no name at all. Perhaps Pedro will clarify this.

Pedro: Mike may well correct me but I believe the method I gave above using
Add unless INDI.NAME:SURNAME is null
Add unless INDI.~FATH>NAME:SURNAME is null
Exclude unless Ancestor of .....
will work. It will indeed produce a list of people with no surnames or whose father has no surname. You can then add these people to a named list. If you want to see all the other Ancestors, use another query which Adds all Ancestors and Excludes if a member of the named list.

[psinacio]

Hi Lorna,

your attempt is returning people with no surname, but that have no father at all (end of lines...)

It looks like if there is no father it ignores the second test....

[psinacio]

Tate,

It looks lik it has worked!

Add if
=IsTrue((Exists(%INDI.NAME%) and Not(%INDI.NAME:SURNAME% = "")) or (Exists(%INDI.~FATH>NAME%) and Not(%INDI.~FATH>NAME:SURNAME% = "")))
is true
followed by the usual :-
Exclude unless an ancestor...

What I have not managed is to ctrl+c ctrl+v from the FH.

Thanks a lot!!!

[LornaCraig]

your attempt is returning people with no surname, but that have no father at all (end of lines...)

OK, just a misunderstanding. I had assumed that in cases where there was no father at all you would treat that the same as a father with no surname.

[tatewise]

Pedro, you cannot copy the entire filter, only the Expression component for each Row shown in the box at the bottom.

Lorna, try Add unless %INDI.NAME[1]:SURNAME% is null on its own and you will see the problem.
It simply omits records without a NAME field, but keeps those with a Given Name but no SURNAME.

The same problem affects Add unless %INDI.~FATH[1]>NAME[1]:SURNAME% is null
That omits non-existent Fathers and omits Fathers without a NAME field, but keeps Fathers without a SURNAME.

[psinacio]

Thanks Mike / Lorna and JAne,

This one was particular difficult to achieve!