Exporting Gifts Without Soft Credits

Bernard Nemis

Hello, I have been trying to streamline and make this process more efficient for the longest time. I'm hoping someone have a better process either/both in RE and Excel, and maybe start a conversation with this.

Suppose I want to get a list of all Employee donors for a particular fiscal year with their total giving for that year (both hard credit and soft credit). Here are the following factors:

1. John Doe (employee) donated $5000 total for the year, and soft credited wife, Jane (employee)
2. We recorded a $2000 gift in Almanac's constituent record (organization) and soft credited Mike Meyers (employee)
3. There should not be any duplicate entry (Jane and Almanac should not be in the final list)
4. The Query/Export should be a Gift type

How can I get a final list in excel where it will only show John Doe ($5000) and Mike Meyers ($2000)?

I am trying to test all combination of Soft Credit processing in both the Query and Export. I think I can get part of the result if I use the Constituent type because the Constituent Export has an option to only pull Head of Household (which would solve the soft credit problem). But that won't solve the organization issue. I chose the Gift Export because I would prefer to pull all individual gift entries in my raw data when I export in Excel. I can then just Pivot the data to get the total gift donated.

My solution so far:

Gift Query: (criteria: Gift Date=fiscal year requested); SC Option = Both (to include Mike Meyers)
Export Query: SC Option = Both (if I use Donor only, Mike Meyers won't have the $2000 from Almanac, I use Both so that Mike Meyers will have the $2000, BUT, I have to delete Jane in the Excel because she is also an employee and her $ will be considered duplicates of John.)

Deleting the duplicates in excel can be a huge task if I have hundreds of gifts coming in from husbands/wives who are both employees.

JoAnn Strommen

You are correct - it's all or nothing with SC. Spousal SC and reports has been an issue since they created the function.

My suggestion to help clean up in excel is to include the gift ID in your export. You can highlight duplicate values. It at least helps if you're doing it in Excel. If you have a field showing the sc recipient it will help to have that sort on top/bottom after sort by gift id.

Just some thoughts.

Bernard Nemis

Hi, JoAnn.

Thanks for the response. Yup. That's what I do in excel. I highlight the duplicates (conditional formatting to change the color) and sort them by font color > system record ID. This would have been easier if the Gift Type export would have an option of pulling head of household. That way, I can identify the hoh in the excel also. With thousands of rows, I was hoping that I can still find a more efficient way of doing this. If I need the total number of gifts, I guess I can just use the Constituent Type export. At least it takes away the other spouse already. Then I can just use the Summary Gifts > Total Gifts.

Karen Diener 2

Does this HAVE to be a Gift query and export? If you are asking about your employees, maybe it could be a Constituent type and then you would be able to use Head of Household processing.

You can also use a constituent query in a gift export. And since you can query for Head of Household in a constituent query, it might work for you. You would need to make sure that your Export is set up to display soft credits, but I think this could be an option.

Karen

James Andrews

I'm several days late on this but what if you did a gift query based on hard dollar credit and include the soft credit info in the output?

Then you can export the query output and use a union query in Access or Power Query in Excel pretty quickly to combine the two lists – query for employees in the donor field, then pull the soft credit in…. basically the concept is something like this:

SELECT Constituent AS donor, Amount AS GiftAmount
FROM yourgiftexporttable
WHERE Constituent is an employee (however you have that noted)
UNION
SELECT SoftCredit AS donor, Amount AS GiftAmount
FROM yourgiftexporttable
WHERE Constituent is not an employee AND SoftCredit is an employee

Bernard Nemis

Thanks, James.

I've never tried Power Query before. I've been wanting to find out how I can implement that in my workflow, but I don't think it's available in the Mac version.

James Andrews

Ah, I didn't realize you were on Mac. They have implemented PQ in the Mac version of Excel, but it appears to be only if you're on office 365.

Bernard Nemis

Yes. I was bummed about it. Hopefully our organization get it soon. I am very interested in utilizing those power tools/apps.

Bernard Nemis

Thanks, Karen. To be honest I never have tried using a constituent query with gift export (or vice versa). I have to see if that will yield a better option for me. The constituent export has the head of household processing (to take out one of the spouses). The gift export, however, has the ability to list all the gifts by row. If you know a work around I would love to hear it. Thanks!