Query letting data in

Ryan Moore
Ryan Moore
Kudos 1 Name Dropper Participant First Anniversary
in Blackbaud Raiser's Edge NXT®: General Discussions

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:

f4e0816617916bb7f988e792700fbb85-huge-im

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.

Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Tenth Anniversary Kudos 5 PowerUp Challenge: Product Update Briefing Feedback Task 3 2025 bbcon Attendee Badge

    @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
    Marie Stark Community All-Star
    Tenth Anniversary Kudos 5 December 2025 Monthly Challenge PowerUp Challenge #3 Gift Management

    @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
    JoAnn Strommen Community All-Star
    Tenth Anniversary Kudos 5 PowerUp Challenge: Product Update Briefing Feedback Task 3 2025 bbcon Attendee Badge

    @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
    Cathy Spencer
    Tenth Anniversary Kudos 2 Name Dropper Participant

    @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
    Ryan Moore
    Kudos 1 Name Dropper Participant First Anniversary

    @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
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @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
    Ryan Moore
    Kudos 1 Name Dropper Participant First Anniversary

    @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
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @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
    Ryan Moore
    Kudos 1 Name Dropper Participant First Anniversary

    @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
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @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
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 Facilitator 3 bbcon 2025 Attendee Badge

    @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
    Ryan Moore
    Kudos 1 Name Dropper Participant First Anniversary

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

Categories