Creating a gift summary in a query that also excludes certain appeals

Beth Seale

I am trying to create a dynamic constituent query that contains a gift summary (total gifts that are equal to or greater than $1,000 in a calendar year) yet I need to exclude a bunch of appeal IDs and any in-kind gifts, yet each time I add the appeals I don't want into the gift summary filter under “not one of” the query still keeps pulling those gifts under the unwanted appeals into my total gift result. What am I doing wrong?

Alex Wong

@Beth Seale
Mostly likely because of the way you are doing and/ or condition.

Post a screenshot of your total gift amount subquery so we can better help.

Joe Moretti 3

@Beth Seale The “not one of” is not a great idea. Because you are doing a Total Giving of a constituent, even though you are not including some appeals, anyone that has the appeals you want will include the appeals you do not want. Any reason you do not want those appeals? I would try using one of, but this will become an issue if you have gifts with no appeals. Once you creat the query, you should run that query thru RE EXPORT.

Beth Seale

@Joe Moretti Hi Joe - I switched using “not one of” to “one of” and I am still getting the same inaccurate results. We don't want to include in-kind gifts in our totals and each gift we enter into RE has an appeal attached to it (cash, in-kind, stock, etc) so I am not worried about any gifts not having an appeal attached to it. We also don't want to include gifts that were ticket purchases or auction item purchases since those are not truly considered donations but my query is still pulling those in as well. I tried using brackets to hopefully fix the issue but those don't help either.

Alex Wong

@Beth Seale
I think the root of your issue is still in the way you are doing your and/or condition of the various “inclusion” and “exclusion”. It also sounds like you have a lot more condition too. Provide a screenshot of your query to get further help.

Joe Moretti 3

@Beth Seale You may in your query do One of AND not one of, but when it is time to do your total giving for the constituents, use RE EXPORT and then do the gift summary of the appeals you want and leave out the appeals you do not want, that way you get the accurate gift totals. Do not use the Total Giving results in query, since query is not to be used for reporting purposed, but gathering information. I have done that same as you but with no issue.

Beth Seale

@Joe Moretti Hi Joe, I think I might have solved the problem! I believe my error was in my output tab. I believe that my gift summary filters for my output tab was not matching my gift summary filters in my criteria tab and that was messing my totals up. I cherry-picked a few of my problem constituents in the output tab that I knew my previous totals were not correct and now their totals match what I have for their total amount of actual donations!

Beth Seale

@Alex Wong Hi Alex, I think I might have solved the problem! I believe my error was in my output tab. I believe that my gift summary filters for my output tab were not matching my gift summary filters in my criteria tab and that was messing my totals up. I cherry-picked a few of my problem constituents in the output tab that I knew my previous totals were not correct and now their totals match what I have for their total amount of actual donations! I am going to pick a few more random records and compare the amount in the output tab with what I know their donation totals should actually be to ensure my new results are accurate.

Alex Wong

@Beth Seale
Great that you found the problem and solve it.

The only thing I will add is somewhat related to what Joe said.

Use of Summary of Gift amount in Query will automatically remove write off amount from pledge, while use of Summary of Gift amount in Export (never tried it myself, heard it from other post) will not automatically remove write off.