Negative Criteria in Multiple 1-Many Queries

Matt Page · October 2015

I'm not sure I'm going to be able even to explain this, let alone solve it, but hopefully someone can get my drift and have an answer.

I'm trying to create a dynamic constituent query but I want to exclude people who had Action A done to them during 2015 (amongst other criteria).

At first I tried adding in

...AND (Action Type does not equal Action A

AND Action Date equals This calendar yr)

But this doesn't work. Neither does reversing the terms, or getting rid of the brackets or putting does not equal This Calendar Year

Trying to find a work around I resorted to

AND

(First Action Import ID blank

OR (Action Date equals This calendar yr

AND Action Type does not equal Action A)

OR Action Date between 01/01/1985 AND 31/12/2014)

In other words including all constituents without actions, or an action before this year, but still no joy. Also using a one of on all the other action types doesn't work as each constituent can have multiple actions

Any ideas? A good NOT operator would be really useful.

[Some of it I think is linked to another problem I have is the difference between a person with an Action Type A in April 2013 and someone who has an Action A from previous year but just so happens to have an entirely different action (say, an email) that is dated during April 2013. They meet both the first criterion AND the second, so how do you distinguish between the two?]

Matt

Alan French · October 2015

Have you tried creating a separate query for those who do have action A during 2015, then merging the two queries using the SUB operator?

Matt Page · October 2015

Alan French:

Have you tried creating a separate query for those who do have action A during 2015, then merging the two queries using the SUB operator?

Thanks Alan, it may come to that, but I think then I'd lose the dynamic functionality as the merged queries automatically become static(?).

Alan French · October 2015

Good point about the static results.

How about this then:

First Action Import ID blank

OR Action Type does not equal Action A

OR Action Type equals Action A

AND Action Date not between [year_start] AND [end_date]

The only downside is that you would have to keep amending the dates manually, or set the end date a long way in the future perhaps.

John Heizer · October 2015

On the Action Type criteria, try "one of" or "not one of" instead of "equals" or "not equals." I've noticed that when you've got multiple one-to-many fields in situations like this that RE can be seemingly inconsistent about whether or not it keeps those fields "properly connected" when doing the selection (that it looks at the Action Date specific to that particular Action with Action Type of Action A, or whether it looks at ALL Actions that might meet that date criteria). Sometimes bracketing the two helps, sometimes it doesn't. I've seen other posts hinting that where in the Criteria list that bracketing occurs might change the results, so try moving that bracketted set to the top or bottom of the Criteria and see if that changes anything. Viewing the actual SQL that RE generates (CTRL+Q) has helped me diagnose strange results before but it's not for the faint of heart.

While it can get really tricky, particularly when you're excluding people based on certain criteria, you could also try using another Query as the source for your final Query (Tools > Query Options > Record Processing > Select from query). That will let you combine multiple Queries and still keep the results dynamic.

Debra Holcomb · October 2015

A merged query is the best way to go in your example. While the merge is a static query, you can simply refresh the merge any time you need by pressing F5 on the results tab. Or, if you use REQueue you can set the merge query to refresh automatically for you.

Matt Page · October 2015

John Heizer:

On the Action Type criteria, try "one of" or "not one of" instead of "equals" or "not equals." I've noticed that when you've got multiple one-to-many fields in situations like this that RE can be seemingly inconsistent about whether or not it keeps those fields "properly connected" when doing the selection (that it looks at the Action Date specific to that particular Action with Action Type of Action A, or whether it looks at ALL Actions that might meet that date criteria).

I think this gets to the heart of the problem. I can never quite work this out eiter. I asked one of the RE trainers this once, but at the end of the discussion I still don't think they had quite understood the question/scenarios I was citing. In this cases the "(not) one of" options didn't seem to change much.

John Heizer:

While it can get really tricky, particularly when you're excluding people based on certain criteria, you could also try using another Query as the source for your final Query (Tools > Query Options > Record Processing > Select from query). That will let you combine multiple Queries and still keep the results dynamic.

I think I'll try this in future. I have been a bit unsure about this (had maybe assumed that it was static even though it seemed like it shouldn't be). I'm not quite ready for SQL yet. Yet.

Thank you

Matt Page · October 2015

Debra Holcomb:

A merged query is the best way to go in your example. While the merge is a static query, you can simply refresh the merge any time you need by pressing F5 on the results tab. Or, if you use REQueue you can set the merge query to refresh automatically for you.

Ah, OK, thanks. I was aware of the refresh a static query thing, but I wasn't quite sure what happened once you had saved it. If I save it for this month and then refresh it next month, will that still work?

James Andrews · October 2015

Matthew Page:

Debra Holcomb:

A merged query is the best way to go in your example. While the merge is a static query, you can simply refresh the merge any time you need by pressing F5 on the results tab. Or, if you use REQueue you can set the merge query to refresh automatically for you.

Ah, OK, thanks. I was aware of the refresh a static query thing, but I wasn't quite sure what happened once you had saved it. If I save it for this month and then refresh it next month, will that still work?

Not sure what you mean by "will that still work". If you refresh a merged query, either manually or via queue, it'll run itself again based on the criteria of the two input queries.

If you need to change the criteria each month, it won't be automatic -- but there are all the this month, last month, last week, etc. criteria on dates, so you might be able to finagle something there.

James Andrews · October 2015

P.S. I totally agree with you about RE not having a good NOT function. I can't tell you how many times I've wished for the ability to do X AND NOT (Y OR Z)

John Heizer · October 2015

Matthew Page:

John Heizer:

On the Action Type criteria, try "one of" or "not one of" instead of "equals" or "not equals." I've noticed that when you've got multiple one-to-many fields in situations like this that RE can be seemingly inconsistent about whether or not it keeps those fields "properly connected" when doing the selection (that it looks at the Action Date specific to that particular Action with Action Type of Action A, or whether it looks at ALL Actions that might meet that date criteria).

I think this gets to the heart of the problem. I can never quite work this out eiter. I asked one of the RE trainers this once, but at the end of the discussion I still don't think they had quite understood the question/scenarios I was citing. In this cases the "(not) one of" options didn't seem to change much.

John Heizer:

While it can get really tricky, particularly when you're excluding people based on certain criteria, you could also try using another Query as the source for your final Query (Tools > Query Options > Record Processing > Select from query). That will let you combine multiple Queries and still keep the results dynamic.

I think I'll try this in future. I have been a bit unsure about this (had maybe assumed that it was static even though it seemed like it shouldn't be). I'm not quite ready for SQL yet. Yet.

Thank you

Being able to base one dynamic query on the results of another dynamic query can be a very powerful tool (yes, the results stay dynamic, not static). And you can keep going down an almost infinite number of "layers" (though with each layer you take a hit in performance, so you wouldn't want a really complex set used in a self-refreshing Dashboard).

Negative Criteria in Multiple 1-Many Queries

Comments

Categories