Query Conundrum - Looking at Constituents by their Actions... help?

Tom Miller

Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Helen Wieger

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Can you be a little more specific on who you want to include & exclude in the query? Is it

1. Everyone who does not have action 1, 2, or 3 on their record this year, AND has action 1, 2, or 3 on their record from a previous
2. Everyone who does not have action 1, 2, or 3 on their record this year, regardless of whether they have the action from a previous year

Those two outputs will require differing criteria.

Marie Stark

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Tom,

Can you clarify who you want to see in the query? 

Do you want individuals who did not have all of the action types this year?

 Or did you want to see those who did not have any of those action types this year?

Joshua Bekerman bCLO bCRE

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

Michael Sherman

Tom Miller:
Cutting to the chase - i need a dynamic query that includes constituents who do not have a particular Action type(s) this calendar year.



ie -



the Action types in question = 'Action 1', 'Action 2', 'Action 3'.



Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before - therefore he is included in the results of said query.



But - as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR he drops off the query.





After wracking my brain all afternoon I have deemed this an impossible task to ask of a Constituent (/Individual) query, as it looks at each line of Action criteria individually, rather than together.... although i would love to be proved wrong on that!



I have tried - (Dynamic Individual Query):



Action Type not one of 'Action 1', 'Action 2', 'Action 3'

OR (Action Type one of 'Action 1', 'Action 2', 'Action 3'

AND Action Date does not equal This Calendar Yr*)





...and all that happens here is that as soon as someone receives an Action 1, 2 or 3 they drop off - regardless of the date.



*have tried substituting for 'Action date LESS THAN This Calendar Yr' - same results.



I have also tried various combinations of parenthesis - no joy. It looks like it should do the trick but the query is just reading each line as a separate criteria regardless of how the brackets group them.



Can anyone suggest anything here? My heads due to explode soon if i keep looking at it.



Thanks.



Tom Miller

Hi Tom, I don't know why your query doesn't work. It works for me: Action Type one of 'Action 1', 'Action 2', 'Action 3' AND Action Date does not equal 'This Calendar Yr'---No parenthesis My Output is Auction Type, Auction Date, and Name. Mr. Bloggs shows up with Action 1 - 9/24/13 and Action 3 - 9/24/12. When I add Action 2 for 9/24/14 he still shows up but with only Actions 1 & 2.

Michael Sherman

Michael Sherman:
Hi Tom, I don't know why your query doesn't work. It works for me: Action Type one of 'Action 1', 'Action 2', 'Action 3' AND Action Date does not equal 'This Calendar Yr'---No parenthesis My Output is Auction Type, Auction Date, and Name. Mr. Bloggs shows up with Action 1 - 9/24/13 and Action 3 - 9/24/12. When I add Action 2 for 9/24/14 he still shows up but with only Actions 1 & 2.

Sorry, I misread your question. Josh's response is good. Queries can end up being complicated. But that's why they pay us the big bucks, right?

Tom Miller

Helen Wieger:

Can you be a little more specific on who you want to include & exclude in the query? Is it

1. Everyone who does not have action 1, 2, or 3 on their record this year, AND has action 1, 2, or 3 on their record from a previous
2. Everyone who does not have action 1, 2, or 3 on their record this year, regardless of whether they have the action from a previous year

Those two outputs will require differing criteria.

[quote user="Helen Wieger"]

Can you be a little more specific on who you want to include & exclude in the query? Is it

1. Everyone who does not have action 1, 2, or 3 on their record this year, AND has action 1, 2, or 3 on their record from a previous
2. Everyone who does not have action 1, 2, or 3 on their record this year, regardless of whether they have the action from a previous year

Those two outputs will require differing criteria.

[/quote] Hi Helen, thanks for your reply. I need your option 2 - people who do not have one of the Actions dated this calendar year, regardless of previous years. Thanks!!

Tom Miller

Marie Stark:

Tom,

Can you clarify who you want to see in the query? 

Do you want individuals who did not have all of the action types this year?

 Or did you want to see those who did not have any of those action types this year?

[quote user="Marie Stark"]

Tom,

Can you clarify who you want to see in the query? 

Do you want individuals who did not have all of the action types this year?

 Or did you want to see those who did not have any of those action types this year?

[/quote] Hi Marie, thank you for your reply. To be included in this list, constituents must not have any of the Actions dated this calendar year. As soon as a constituent has just one of the Actions dated this calendar year they must drop from the list.

Tom Miller

Josh Bekerman:

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[quote user="Josh Bekerman"]

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[/quote] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the basis for a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.

Tom Miller

Josh Bekerman:

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[quote user="Josh Bekerman"]

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[/quote] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the bas of a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.

Tom Miller

Josh Bekerman:

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[quote user="Josh Bekerman"]

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[/quote] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the bas of a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.

Helen Wieger

Tom Miller:
[quote user="Josh Bekerman"]

Use two separate queries:

- Query1 - One that includes everyone that meets your first criteria (Joe Bloggs has an instance of Action 1 dated last year, and an Action 3 dated the year before?)

- Query2 - One that includes everyone that you want to exclude from your final results (as soon as Joe Bloggs receives an Action 1, 2 or 3 dated THIS YEAR?)

Then merge the queries using the SUB operator (Query1 SUB Query2) so that anyone in Query2 will be removed from your final results.

 

[/quote] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the basis for a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.

[quote user="Tom Miller"] Hi Josh, This has crossed my mind, however I require the query to be dynamic - and i do believe merging queries makes them static? This query forms the basis for a dashboard panel, and therefore must be dynamic. I will post more detailed overview. Thanks all for your replies. Tom.[/quote] Tom, You're correct that merged queries must always be static (see BB65966 in the knowledgebase). And since that is the case, I don't know if you can get a dynamic query with the information you want. For whatever reason, adding parenthesis around the criteria doesn't actually narrow the criteria to actions of type 1, 2, or 3 within the stated time period. I would suggest chatting with a BB specialist/representative and seeing if they have a work around.