Querying constituents with certain gifts, one is missing that should be there

Kate Hudson · February 2017

I'm hoping someone can point out what I might be doing wrong. An organization constituent made a major gift to our Annual Fund. I queried all constituents with a total giving of $75 or more between the dates of 1/1/2016 and 12/31/2016, and exluded a specific gift reference. This constituent and their gift meets all the criteria, yet it does not appear. I don't understand what is going on. The gift was in September (so falls within the dates), was over the $75 amount, and the gift reference does not equal the specific reference typed in.

As you might be able to guess, this query was made for our Annual Statement Reports, which were already sent out. This constituent was missing from the query, and therefore did not appear in the report, so they did not receive their statement. Very embarrassing!

The reason we pull in total giving of $75 or more though the IRS requires written notification for individual gifts of $75 or more for any gift where the deduction amount is not the full gift amount is just to exclude those who purchase small silent auction items or raffle tickets. We are fine with issueing statements to those who do not meet th IRS minimum threshhold, but it someone spent $20 on raffle tickets with no tax deductible value, we don't want the statement included. Make sense? We also exclude a specific reference from a foundation Giving Day gift, which is hard credited on the foundation record and soft credited on individual's records - the Annual Statement Report always spits out the soft credits on donor statements even though we select for the soft credits go to donors, not the soft credit recipient (why do you do this, RE?? that's the point of that check box! stop it!).

If I run tthe report to include all records without the query, it appears, so the issue is apparently the query, not the report, but due to the report issues I need the query...

Joanne Felci 2 · February 2017

Is this the only organization that met your criteria?

Silly question but are you using an individual query instead of a constituent query by any chance?

Daniel Noga 2 · February 2017

Kate Hudson:

I'm hoping someone can point out what I might be doing wrong. An organization constituent made a major gift to our Annual Fund. I queried all constituents with a total giving of $75 or more between the dates of 1/1/2016 and 12/31/2016, and exluded a specific gift reference. This constituent and their gift meets all the criteria, yet it does not appear. I don't understand what is going on. The gift was in September (so falls within the dates), was over the $75 amount, and the gift reference does not equal the specific reference typed in.

As you might be able to guess, this query was made for our Annual Statement Reports, which were already sent out. This constituent was missing from the query, and therefore did not appear in the report, so they did not receive their statement. Very embarrassing!

The reason we pull in total giving of $75 or more though the IRS requires written notification for individual gifts of $75 or more for any gift where the deduction amount is not the full gift amount is just to exclude those who purchase small silent auction items or raffle tickets. We are fine with issueing statements to those who do not meet th IRS minimum threshhold, but it someone spent $20 on raffle tickets with no tax deductible value, we don't want the statement included. Make sense? We also exclude a specific reference from a foundation Giving Day gift, which is hard credited on the foundation record and soft credited on individual's records - the Annual Statement Report always spits out the soft credits on donor statements even though we select for the soft credits go to donors, not the soft credit recipient (why do you do this, RE?? that's the point of that check box! stop it!).

If I run tthe report to include all records without the query, it appears, so the issue is apparently the query, not the report, but due to the report issues I need the query...

I once had a similar problem and the issue for me was that the gift in question had been written off. I think I'd used Gift Types as a criterion, though, and that was why it wasn't working.

Kate Hudson · February 2017

Joanne Felci:

Is this the only organization that met your criteria?

Silly question but are you using an individual query instead of a constituent query by any chance?

It is a Constituent Query and other Organization constituents are appearing correctly in the Query.

Kate Hudson · February 2017

Daniel - I went back and checked the gift type and it's Cash, with a payment type of Business check. I do supress Pledges and Write offs in the Report, but not in the Query. If it were Pledge for some reason that would make sense, though. I'm wondering if maybe the soft credit has something to do with it?

Daniel Noga 2 · February 2017

Kate Hudson:

Daniel - I went back and checked the gift type and it's Cash, with a payment type of Business check. I do supress Pledges and Write offs in the Report, but not in the Query. If it were Pledge for some reason that would make sense, though. I'm wondering if maybe the soft credit has something to do with it?

That could well be it. You aren't using Gift Type as a criterion, so we know you're not suppressing them there. Soft Credits will getcha, because the Query Options will also alter how the query treats them. Go to Tools > Query Options > Gift Processing and check out how the "Credit Soft Credits to" section is set up. By default it will credit them to the actual donor, and maybe it's the case that the Constituent you are wanting to appear in the results was the Soft Credit Recipient? If so, change the setting to Recipient and they should appear.

I know how bad it is when some minute detail in a query turns out to be the reason someone's on the phone angry at your org. Very embarrassing indeed, but don't take it to heart. It happens sometimes. The sad thing is that nobody wants to hear "It was a data processing issue" as an explanation because to a great many donors, that means nothing and looks like an attempt at dodging accountability.

Melissa S Graves · February 2017

Please explain exactly HOW you are excluding gifts with a certain reference. Are you using a SUB query or "does not contain" or something else? This seems to me to be the sticky part of your query.

Kate Hudson · February 2017

Daniel Noga:

Kate Hudson:

Daniel - I went back and checked the gift type and it's Cash, with a payment type of Business check. I do supress Pledges and Write offs in the Report, but not in the Query. If it were Pledge for some reason that would make sense, though. I'm wondering if maybe the soft credit has something to do with it?

That could well be it. You aren't using Gift Type as a criterion, so we know you're not suppressing them there. Soft Credits will getcha, because the Query Options will also alter how the query treats them. Go to Tools > Query Options > Gift Processing and check out how the "Credit Soft Credits to" section is set up. By default it will credit them to the actual donor, and maybe it's the case that the Constituent you are wanting to appear in the results was the Soft Credit Recipient? If so, change the setting to Recipient and they should appear.

I know how bad it is when some minute detail in a query turns out to be the reason someone's on the phone angry at your org. Very embarrassing indeed, but don't take it to heart. It happens sometimes. The sad thing is that nobody wants to hear "It was a data processing issue" as an explanation because to a great many donors, that means nothing and looks like an attempt at dodging accountability.

You are spot on, Daniel! It was in the Gift Processing under Tools > Query options. And thank you for kind words. Hopefully apologizing and responding quckly (I was able to pull an Annual Statement for this constituent manually before trying to figure out what went wrong) will satisfy them. And now I know it will not happen again!

Kate Hudson · February 2017

Melissa Graves:

Please explain exactly HOW you are excluding gifts with a certain reference. Are you using a SUB query or "does not contain" or something else? This seems to me to be the sticky part of your query.

Under the filter tab for the summary information, I select Gift Reference, choose does not equal as my operator, and paste on the exact reference for the gift I do not want (the only gift in the system with that reference). But as Daniel pointed out, my issue causing that was the same as for the main problem - my User Option for gift processing. So I can take that Reference supression out and be fine, I think.

Daniel Noga 2 · February 2017

Kate Hudson:

You are spot on, Daniel! It was in the Gift Processing under Tools > Query options. And thank you for kind words. Hopefully apologizing and responding quckly (I was able to pull an Annual Statement for this constituent manually before trying to figure out what went wrong) will satisfy them. And now I know it will not happen again!

I'm glad I was able to help. Just be careful with that setting in queries, especially in things like Annual Reports; changing that setting to credit Recipients brought up the person you were looking for this time, but it could also have made others disappear. If your organization does not apply very strict guidelines to how, when and why Soft Credits are used, setting queries one way or the other could cause inconsistencies and cause you to credit the wrong entity in your recognition materials.

It would add a lot of manual work to sort through, but sometimes it's better to set the Soft Credit settings to credit both, and to cherry-pick which names you want to appear on the Annual Report.

Bob Wiebe · February 2017

Daniel Noga:

Kate Hudson:

You are spot on, Daniel! It was in the Gift Processing under Tools > Query options. And thank you for kind words. Hopefully apologizing and responding quckly (I was able to pull an Annual Statement for this constituent manually before trying to figure out what went wrong) will satisfy them. And now I know it will not happen again!

I'm glad I was able to help. Just be careful with that setting in queries, especially in things like Annual Reports; changing that setting to credit Recipients brought up the person you were looking for this time, but it could also have made others disappear. If your organization does not apply very strict guidelines to how, when and why Soft Credits are used, setting queries one way or the other could cause inconsistencies and cause you to credit the wrong entity in your recognition materials.

It would add a lot of manual work to sort through, but sometimes it's better to set the Soft Credit settings to credit both, and to cherry-pick which names you want to appear on the Annual Report.

My colleague introduced me to a safeguard when pulling lists of names of donors. She runs a report where the donations are set to Donor. This gives the accurate amount of gifts. The list of gifts you were pulling could then be totalled and compared with the donor amount. A discrepancy would occur, because the one gift missing was a soft credit issue, leading you to do more digging.

Daniel Noga 2 · February 2017

My colleague introduced me to a safeguard when pulling lists of names of donors. She runs a report where the donations are set to Donor. This gives the accurate amount of gifts. The list of gifts you were pulling could then be totalled and compared with the donor amount. A discrepancy would occur, because the one gift missing was a soft credit issue, leading you to do more digging.

I'm not sure I follow this. You mentioned two separate lists, but described both as being pulled at the donor amount--did you mean to say that one of them would be pulled at the recipient amount? Otherwise I would think they would be the same.

What happens if the soft credit amounts match the hard credit amounts? Would there still be discrepancies? In the orgs where I have worked, partial soft credits happened rarely if ever, so telling them apart this way wouldn't work--unless I'm totally misunderstanding what you mean.

Querying constituents with certain gifts, one is missing that should be there

Comments

Categories