* Query filter concatenation

[TMG_refugee]

I am writing a query but I want to have two filters. I created a fact(attribute) called ResearchNote. I wanted to list only those that a not actually exists and then restrict by location so when I go somewhere too do research I can get a list of research items just for that location.

I have tried the research note facility in FH but it does not work well with my workflow thus the creation of a research note that I can place with the individual at the correct location.

In the query Rows I have two statements:

Exclude if %INDI._ATTR-RESEARCHNOTE[1]% is null
Add if %INDI._ATTR-RESEARCHNOTE[1].PLAC>% contains 'Rowley'.

Both work individually but when both are enabled only the first seems to work.

[Gowermick]

I’m afraid your logic is wrong.

What you are actually doing in your 2nd expression is adding From All Recotds, any record whose note contains rowley, i.e add more records to the resultset to those already found by 1st exoression.

What you need to do is to exclude from the records found by 1st expression, all those records where Place does not contain rowley.
i.e 2nd expression needs to be an exclude statement, not an add


UPDATE
The expressions you need are

Exclude if %INDI._ATTR-RESEARCHNOTE[1]% is null
Exclude Unless %INDI._ATTR-RESEARCHNOTE[1].PLAC>% contains 'Rowley'.

i.e. Exclude all records where Note is Null, then from those, exclude any where PLACE does not contain Rowley

[TMG_refugee]

Thanks Mike

that worked perfectly.

Why does the addif get new records where the exclude only works on the previous statements exclude?

[LornaCraig]

Why does the addif get new records where the exclude only works on the previous statements exclude?

What else could they do? Each filter either adds (new records) to the existing results set or excludes some records which are already there.

The special case is the very first filter, where if the first filter is an 'Exclude' (as in your case) the query initially puts all the records in the result set and then applies the Exclude filter. If the very first filter is an 'Add' the query iniitally starts with an empty results set and then applies the Add filter.

After that the filters are applied sequentially. This is from the Help files:

filters.JPG (33.17 KiB) Viewed 597 times

[TMG_refugee]

Thank you Lorna.

Now it is clear to me. I should have seen that myself. Now I can continue with my queries.

[jelv]

Wouldn't swapping the two lines round have worked? I.e.

Add if %INDI._ATTR-RESEARCHNOTE[1].PLAC>% contains 'Rowley'.
Exclude if %INDI._ATTR-RESEARCHNOTE[1]% is null

[tatewise]

Yes, swapping the lines should work but actually it is sufficient to use just one filter:

Add if %INDI._ATTR-RESEARCHNOTE[1].PLAC>% contains 'Rowley'.

That works because if %INDI._ATTR-RESEARCHNOTE[1]% is null then it cannot have a Place field containing 'Rowley' so they are excluded automatically and won't be added.

[Gowermick]

At a quick glance, when you think about it, the 1st criteria selects only those whose researchnote.place contains Rowley, therefore none of their researchnotes will be Null, so 2nd test is redundant!
(I’m willing to be corrected )

Miketate beat me to it

[jelv]

I'm wondering why TMG_refugee said the two queries worked individually if his second expression was all that was needed? Wouldn't he have seen that it was giving the results he wanted? Is there some subtlety we are missing?

[tatewise]

The two filters will work independently.

Exclude if %INDI._ATTR-RESEARCHNOTE[1]% is null
That lists the 1st instance of a Research Note Fact for every person that has one.
BTW: Does not list 2nd or subsequent instances of that fact.

Add if %INDI._ATTR-RESEARCHNOTE[1].PLAC>% contains 'Rowley'.
As we have decided, that lists every 1st instance of a Research Note that has a Place mentioning Rowley.
That is all that is needed to create the subset of Research Notes.

When the two are run in sequence, the 2nd filter adds nothing since all 1st instance Research Notes are already included.
So adding those that mention Rowley does not add anything that is not already there.