Query letting data in

Ryan Moore 5

Hey everybody, just when I hit that 9 month mark at work and feeling confident in what I do with DBV Queries…

…and I know, I keep hearing that Query isn't great at keeping things ‘out’ which is what I want to do, but I'm stymied.

I want to do a look at potentially some donors we can ‘move’ to monthly donations.

Built up a query:

So it's basically:

• Not a monthly already
• not one of our staff doing payroll deductions
• not an organization
• either
  • gave ≥3 gifts in each 2023, 2022, and2021
  • gave ≥$500 in each 2023, 2022, and 2021
• not a “do not solicit” code

So it's a really small list, but when I look at some of these on the list, some have those solicit codes.

I've tried moving it to the top of the query hoping it might do that bit first, or maybe i have some other bracketing going on…

Any suggestions?

And I know I could probably do THESE, and THOSE, then do a merge of THESE sub THOSE… but that just feels so… silly to have to do this in several steps when one algorithm should do it.

JoAnn Strommen

@Ryan Moore back to my query training…

When using a combination of AND and OR use ( ) . Outside of the ( ) applies to all records. Can use ( ) to create mini sub-groups..

Marie Stark

@Ryan Moore I agree with Joanne, I think the OR is tripping you up. You will probably need parenthesis before and after the gift statements.

JoAnn Strommen

@Ryan Moore Couple other tips from trainings. Avoid negative if you can make positive statement. Ex Key indicator = individual can pull individuals vs a constituency code which it looks like you used.

And you're correct, criteria order can make a difference.

Cathy Spencer

@Ryan Moore I like your thoughts here. Best wishes as you progress through query-land! You've got some great suggestions from JoAnn and Marie!

Ryan Moore 5

@JoAnn Strommen

Thanks folks

So here's what I've got (in case the pic was a little low res)

Constituency Code does not equal “monthly”

AND con code does not equal “payroll deduction”

AND con code does not equal “organization”

AND ( # of gifts ≥ 3 for 2023 AND # of gifts ≥ 3 for 2022 AND # of gifts ≥ 3 for 2021)

OR ( $ of gifts ≥ $500 for 2023 AND $ of gifts ≥ $500 for 2022 AND $ of gifts ≥ $500 for 2021)

AND solicit codes not one of (list of the do not solicits)

Now the rick might be to move the solicit code to the top, but it didn't make a difference. I'd see results in the query with donors who have the solicit code mentioned

the # of gifts or $ of gifts… how would I get those groupings into another parantheses as you can nest brackets, but you can't END with a double bracket

i.e. AND ( # OR ( $ ) )

Thanks everybody for the help

Alex Wong

@Ryan Moore
Gonna not comment on why you have Constituent Code for Monthly Donor and Organization… as you probably inherited that…

What you trying to do is:
CC & ( (N21 & N22 & N23) | (A21 & A22 & A23) ) & SC

CC is the constituent code condition

N21, N22, and N23 is number of gift condition for the 3 years

A21, A22, and A23 is the amount condition for the 3 years

SC is the solicit code condition

& = and

| = or

What your screenshot show is: (which is not the same as what you want)
CC & (N23 & N22 & N21) | (A23 & A22 & A21) & SC

You want (N21 and N22 and N23) to be true OR (A21 and A22 and A23) to be true

So without the consittuent code and solicit code, what you really want is ( (N21 & N22 & N23) | (A21 & A22 & A23) ). Since AND happens before OR, it can be reduced to
( N21 & N22 & N23 | A21 & A22 & A23 )

then adding the CC an SC in:
CC & (N21 & N22 & N23 | A21 & A22 & A23) & SC

Ryan Moore 5

@Alex Wong
Yeah, I was thinking I might have missed with the AND/OR

I want:

CC & ( (N21 & N22 & N23) | (A21 & A22 & A23) ) & SC

I'm struggling with the syntax within DBV to accomplish that

Alex Wong

@Ryan Moore
do this:
CC & (N21 & N22 & N23 | A21 & A22 & A23) & SC

You should be fine after removing the ) and ( that is around the OR condition

Ryan Moore 5

@Alex Wong
But I would want the string of N to be true or the string of A to be true…

i think laying it out like

• number of 2021 gifts ≥3
• AND number of 2022 gifts ≥ 3
• AND number of 2023 gifts ≥ 3
• OR total of 2021 gifts ≥ $500
• AND total of 2022 gifts ≥ $500
• AND total of 2023 gifts ≥ $500

i think the logic is missing those parentheses… if only DBV would allow doubles (if this were a coding language or even excel, it'd be simple).

Alex Wong

@Ryan Moore
conscode
AND (#2021≥3
AND #2022≥3
AND #2023≥3
OR $2021≥500
AND $2022≥500
AND $2023≥500)
AND solicit code

AND comes before OR

meaning between the paranthesis, you are saying 2021,2022,2023 all years must have 3 or more gift OR 2021,22,23 all years must have $500 or more.

Alex Wong

@Ryan Moore
Hopefully this gives you the understanding, using javascript in browser console:

var CC = false; var N21 = true; var N22 = false; var N23 = true; var A21 = true; var A22 = true; var A23 = true; var SC = true;

correct - what you would like to do with the double paranthesis
CC && ( (N21 && N22 && N23) || (A21 && A22 && A23) ) && SC
false

correct - same as above, without the need for double parathesis
CC && (N21 && N22 && N23 || A21 && A22 && A23) && SC
false

incorrect: (what you originally have in screenshot)
CC && (N21 && N22 && N23) || (A21 && A22 && A23) && SC
true

Ryan Moore 5

@Alex Wong
thanks man, it works… can't say i follow it myself, but super impressed! thank you!