Gifts incorrectly applied to Pledge

Laura Easterly · January 2021

I'm trying to set up a quality control process/check to identify where a gift to one fund is incorrectly applied to a pledge to a different fund. For example, if the pledge is for fund A, but someone applies a gift to fund B to the pledge, I want to be able to see that the gift should not have been applied to that pledge.

We've had a few of these situations come up over the last couple months and we're trying to be proactive and catch and fix them before finance discovers them in the end of month reconciliation. I thought I could set up a query to find them, but I can't seem to find a query field to look at the fund each pledge installment payment.

Has anyone else created a query or other QC process for this situation? What did you do?

Karen Diener 2 · January 2021

Unfortunately, you won't be able to zero in on exactly what you're looking for and pull only those gifts. Query cannot pull information on pledges and related payments, nor can it compare in all instances.

I haven't tried this, but you could do a gift query of all pledges, and then use that in a a gift export. You can export whatever pledge information you need, and then export payment information. That is found under Installments | Payments | Payment Gift. Depending on how many pledges you have and how many installments might exist on each, you could either manually review or use the Excel formula EXACT to compare values in the spreadsheet. Compare the fund on each payment to the fund on the pledge.

Hopefully this is a cleanup issue and not an ongoing problem. If it is an ongoing issue, staff training and policy reminders are definitely in order!

Good luck!

Karen

Judy Spigarelli · January 2021

I've done something similar, but it takes a few steps. If you create an Gift Pledge Payment import file based on the pay-cash gifts, you can export the gift information along with the Gift Link field. That field is the Gift ID of the gift that the pledge paid. If you then export the Pledge Gift ID (and it's fund), you can use VLOOKUP on the pledge gift ids to compare the fund from the pledge to the fund in the payment. It's complicated the first time, but once set-up, it's pretty quick.

Laura Easterly · January 2021

Karen Diener‍ Thanks for your response! It mostly happened because of a misunderstanding with a new employee so shouldn't be an ongoing issue, but my thought process is that if an issue happens once, it's likely to happen again so I always try to check for these kinds of things moving forward.. Our finance folks will catch them when they complete their EOM reconciliations, but we should be catching these in the checks we run-through our checks before we hand things over to finance.

I think the process you mentioned would be simple enough to implement if we do a weekly review of pledges with a payment that was applied in the last week (or whatever date range since you last ran it).

Laura Easterly · January 2021

Thank you, Judy Spigarelli‍ Our DBA doesn't allow many people to access imports, but I think I can create an export to pull the data you mention and then I should be able to follow the process that you outlined.

Appreciate your response!

Judy Spigarelli · January 2021

I believe you'll have to ask your DBA to create the import file and send it to you. This GiftLinkID isn't available in export. Good luck!

Gifts incorrectly applied to Pledge

Comments

Categories