need to exclude write-offs in Query

Sherrel Battles · September 2015

Hello! I need to write a Query that will include only constituents with a GIFT to a particular Fund/Campaign, not a write-off. The Constituent may have other write-offs on their record, including to the Campaign in question, but must have a "good" gift to be included. I know I have done this in the past by defining Gift Type in the Query, but it's not working now. Has anyone else experienced this? How do you exclude Constituents with write-offs from lists and reports? I need to end up with a Constituent Query.

William Winkler · September 2015

Hi,

If you need to get a query of constituents based on appeal, etc. but want to exclude write-offs, under the "Gifts" options, pull over your intended appeal id and gift type = not one of: write-off or mg write-off. If you have additional criteria, you may want to use parenthesis to group that statement. See my image below for more clarity:

Hope this helps!

Eddye

Christine Cooke 2 · September 2015

Gift Fund ID is one of:

AND Gift Type is One Of: choose all except Write Off and probably InKind

or

Campaign ID is one of:

AND Gift Type is One Of: choose all except Write Off and probably InKind

Sherrel Battles · September 2015

Eddye, thanks for th idea but I don't have a common Appeal to use.

Christine, I'm trying what you suggest and it's not working. I still get Constiteunts who have only a write-off. I'm confused by this as I'm sure it has worked in the past.

John Heizer · September 2015

I'm a little confused about what you're looking for because, technically, a Constituent can't have a Write-off if they don't have a Gift.

One weakness I've found in RE is that Write-offs are only associated with the "entire Gift" and can't be selectively applied to Campaigns/Funds/Appeals, so that if the Pledge or any Payment is a Split Gift reporting can get very confusing.

Try using a Constituent Query where Summary Information > Summary for Gift > Total Amount of Gifts Greater than $0.00 and apply criteria that filters to the Campaign/Fund. I just double-checked and any amount written off will automatically get factored into the Gift Amount so that anyone whose Pledge was a 100% Write-off should then be excluded from the Query results.

Sherrel Battles · September 2015

Thanks John, using Summary worked! The same gift criteria NOT in Summary does not exclude the write-offs (with or withour parentheses), but Summary does. For some reason I find that confusing...

I have some Constituents who made a pledge to FundA/Campaign that was written off, but then made a pledge to FundB/Campaign that they paid. And some with only a write-off. Makes for fun reporting. Thanks again for your suggestion!

need to exclude write-offs in Query

Comments

Categories