Query for checking blank gift amount and blank email / address

Benard Okot · January 27

Hello there, I'm new in RE. I'm trying query records where gift amounts are blank and equals to $0.00 and blank email / address. It's only out the right records when I'm using one criteria for either blank gift amount or gift amount equals $.0.00.

Any help is highly appreciated!

Tatyana Leifman · January 27

The gift amount criteria are contradictory. You should put them in parentheses and link them with OR. That is you should have:

(Gift Amount equals $0.00

OR Gift Amount blank)

Also, if you have "Preferred Address Lines blank," you don't need the criteria for each Address Line separately. However, Address Lines includes City, State & Zip. So I would change it to

(Preferred Address Lines blank

Or Preferred Address Line 1 blank)

I would actually dispense with querying separately on Address Line 2 & 3 because they would be blank if Line 1 is blank (and they are blank in many cases even when you have Address Line 1 filled). Unless your db has extraneous blank first lines and an address on line 2.

JoAnn Strommen · January 27

Welcome to RE! Let's start with Gift amount. A gift amount can not be $0 and be blank at the same time. I don't think you can have a blank gift amount. It's a required field for me and it defaults to $0.00. So saying you want it to pull both of those will not find any records.

If you are trying to do address clean up with the different address lines there are other ways to do it. If preferred Address lines is blank it means all the lines are blank. It's asking the same thing but I don't think it will hurt the query.

I think gifts blank is the first change to make. See what you get then.

JoAnn Strommen · January 27

Is there other info to know that would be helpful to understand what you are trying to achieve? I see you have tags of Event, NetC, and more.

Faith Murray · January 27

Bernard, it is impossible to save a gift record in RE with a blank gift amount. There must be a value in the field of at least $0.00. I do not know if you can even Import gifts without a gift amount, so I suspect that is why your query is only working with an either/or criterion for those fields.

Forgetting specific Query criteria, in plainspeak what are you trying to find? Are you wanting a list of $0 gifts? Or do you actually have blank gift amounts in your database?

Tatyana Leifman · January 27

https://community.blackbaud.com/discussion/comment/310206#Comment_310206

I just checked and realized that Gift Amount blank will pull records that have no gifts on them at all. So, I'm not sure that is a good criterion for data clean up—unless you are looking for non-donors who have been added to your database and have no contact information and should be reviewed as to why they are there.

Benard Okot · January 28

Hello everyone, thank you very much for your input. I appreciate all of the feedback provided.

This is how we grow as a community!!

@Tatyana Leifman recommendation worked like a charm!
You should put them in parentheses and link them with OR. That is you should have:

(Gift Amount equals $0.00

OR Gift Amount blank)

I was getting about 10,643 records for blanks and 353 for $0.00 separately, but now the parentheses combined them as I wanted.

To clarify once again, my objective was to identify and extract all constituents who have no gift records, email addresses, or physical addresses prior to the last quarter. These records appear to be redundant within the database, and we plan to archive them to create additional capacity and improve overall database flexibility.

Regards to all!

JoAnn Strommen · January 28

Knowing a bit more about your goal you could also accomplish this using Summary Information > Summary for Gift > Total Amount of Gifts or Total Number of Gifts.

As you learn more, you'll find various ways to achieve the same result and can choose what works best for you. As a new RE user, I would strongly recommend taking BB training. I think you'll find them helpful.

Faith Murray · January 28

Agreeing with JoAnn. If you want constituents with no gifts, I would just do a Summary field:

Summary of Gifts, Number of Gifts = 0, AND
Address Line 1 = Blank (even if they have a city, you can't mail without a street address), AND
Phone Type Email = blank.

Alex Wong · January 29

@Benard Okot Many of the replies are great already for your need, just a few extra points of info that maybe helpful for you:

if your intent is to clean up constituent record with your specification, I suggest you do not use "gift amount = 0". There is usually a reason why a gift is left in the constituent record with amount = 0, such as: a refunded gift; gift in kind. That "drives" a meaning that the constituent had some interaction with your org and you likely wouldn't want to get rid of them.
- I agreed with Faith and JoAnn, specifically, use Summary of Gifts > Number of GIfts = 0 (using Total Amount of Gift = 0 would run into the same trouble I mentioned above)
As you dive into RE Querying more, it is important to understand how Query works and what causes duplicates of record: One-to-Many relationships. In the constituent query, filtering, outputing, or sorting by gift info will drive a lot of duplicates record rows that will make your life of cleanup harder than needs to be, this goes back to above bullet point that summary of gift info is a One-to-One relationship field to a constituent, so that's why it is better to use for your use-case

Query for checking blank gift amount and blank email / address

Best Answer

Answers

Categories