Finding Pay-Cash Gifts Applied to pledge, But Incorrect Fund

Dalton Funkhouser

I recently started at a hospital foundation that receives payroll deductions from employees. Each employee that has a pledge has the desired amount deducted from their paycheck. The sum of all of those deductions is given to us via a check every pay period. When we receive it, I cross check the lists of employees  to find out who should be added or removed from the batch. 



Recently, I have noticed that some of the pledge pays were being applied to the pledge, but they were being applied as the fund of an older pledge. I'm slightly mortified that this has been happening for years, and would like to begin fixing the pay-cash gifts as soon as possible. Does anyone know a way to find these? I know how to prevent this from happening again, but I need to check the gifts committed before I started here.

Nicole S

I wonder if you can just pull a list of the gift type is pay-cash and export the gift fund and the original pledge fund and use some Excel feature to compare the columns? 



One thing that could be happening, if you're using batch, is that the check box to automatically apply gifts to outstanding pledges could be checked.  When the batch is commited the gifts are then used to pay pledges.  It will be a different check box setting on each user so don' think because it isn't checked for you it isn't checked for everyone else.

JoAnn Strommen

This could be a bit labor intensive.  As to find them, are they annual funds where you could look for a payment made on a date in 2014 to at 2012 fund?  If payments for a calendar year or FY are all supposed to be for a specific fund that would give you some criteria to begin querying.  (Ex: Gift equals pledge and Fund equals ABC 2012, and export or output payments greater than 1/1/14.



I don't do a lot of gift imports but if you can find all the incorrect payments and export them, you may be able to do a gift import.  If not it would be manual gift adjustments to change the fund.



Going forward - Do you enter gifts by batch? If so, be sure correct fund is in the batch and use the check boxes when you commit it to only apply to the fund listed in batch.  RE has this strange default that it will apply payments to earliest pledge with balance which isn't always what we want.

Dalton Funkhouser

I'm currently working on exporting all pledges made after 1/1/14.  I'll set up a formula to check the application of the pay-cash to the pledge. I might need to do manual gift adjustments for all of them. So many things to fix, so little time haha.



I feel like there should be some way to query on payments applied to pledges, but I'll just do this in excel.



We do batch entry, but each of these checks represent over 550 individual gifts. They are set up in a recurring batch and yes, the "automatically apply to pledge" box is checked. But as old pledges roll over into new ones, it seems the amount stays the same while the individual may elect to change the fund. Our payroll department doesn't track the fund given to, so it is very difficult for me to know if a gift fund should change. Going forward, I'll make sure we use the "only apply to pledges with same fund" check box. That is probably at the root of all this.

John Heizer

Unfortunately finding such pledge payments isn't easy because you can't create a Query that compares the data in one field to the data in another field.



I've managed to create a Custom Report that we use to check our Gift entry that does this comparison, but it's very complex because you have to export the current payment not as the Gift but as the Payment on the Installment of the original Pledge Gift (Installments > Payments > Payment Gift) and this can't be done in Excel (because of the one-to-many-to-one relationships), only with Access tables and Subreports in Crystal.

JoAnn Strommen

Dalton, You could try gift query Gift Type equals pledge AND Fund ID equals XXX AND (gift type equals cash-pay AND FUND ID does not equal XXX).  Don't know if it works as it pulled no records for me but I think/believe all my gifts are applied to correct fund.

John Heizer

JoAnn Strommen:

Dalton, You could try gift query Gift Type equals pledge AND Fund ID equals XXX AND (gift type equals cash-pay AND FUND ID does not equal XXX).  Don't know if it works as it pulled no records for me but I think/believe all my gifts are applied to correct fund.

JoAnn, that wouldn't work with a Gift Query because the Gift Type can be Pledge or Pay-Cash, but not both which is what would have to happen using an AND operator.  RE can't compare the data in one Gift record to the Gift data in another record.

Jen Claudy

The shear volume of records you're talking about makes this a big project!



I will share that I repurposed the Finder Number field on the Gift Record as Pledge ID.  When I enter a Pledge in RE, I save the record to create the Gift ID, then copy that ID to the Pledge ID field (you could also use an attribute, but I like that they're on the same tab, and we have no other use for that field).  When payment gifts are recorded, that Pledge ID value copies over.  So I can now create a query of pledges and payments and sort on that field to get them in a proper order and know easily which payments belong to which pledge.



You could probably set this up retroactively, if you wanted, with a bit of exporting and importing.  (And if you repurpose a field, you can change the label via Config > Fields > Gift.)

Dalton Funkhouser

Jennifer, the repurposed field automatically copies over to pledge pays, or do you need to do that manually?



John, I think the solution to this problem is just the ability to query and compare data in one field to another. I know that is fairly complex, but that functionality would be much appreciated. I might look into creating the custom report since this is a thing we do on a regular basis.

Jen Claudy

Dalton Funkhouser:

Jennifer, the repurposed field automatically copies over to pledge pays, or do you need to do that manually?



John, I think the solution to this problem is just the ability to query and compare data in one field to another. I know that is fairly complex, but that functionality would be much appreciated. I might look into creating the custom report since this is a thing we do on a regular basis.

Yes, it copies over, as do Attributes.  I have it on all employee pledges, too...but we have 12, not 550!

James Andrews

John Heizer:

Unfortunately finding such pledge payments isn't easy because you can't create a Query that compares the data in one field to the data in another field.



I've managed to create a Custom Report that we use to check our Gift entry that does this comparison, but it's very complex because you have to export the current payment not as the Gift but as the Payment on the Installment of the original Pledge Gift (Installments > Payments > Payment Gift) and this can't be done in Excel (because of the one-to-many-to-one relationships), only with Access tables and Subreports in Crystal.

Agreed with John. If you know how to use Access or Crystal, this is not too difficult.



You could also export everything to excel and use conditional formatting to highlight the issues, assuming you wouldn't end up with more than 16,384 columns....



What a bummer! I recently had to do over 100 adjustments/corrections in RE, many of which were applying gifts to different pledges, and it was a really tedious process.  I don't understand the restrictions on what you can and can't correct via adjustment vs. having to re-enter and this is an example of that -- I'm not sure why applying a payment to the wrong pledge requires re-entering the gift rather than adjusting it. I can't imagine the problem is technical, and they did it for audit reasons, but I don't know what those are.

Christine Cooke 2

Sounds like everyone has some good suggestions for mining the info so you can correct it.  The other part of the correct it would appear is cleaning up outstanding unpaid pledges from previous years -- that would be why payments have been going to the wrong year, there were outstanding balances and the end of the FY that no one cleaned up/wrote off/adjusted down.  So just keep that in mind, or the payments will continue to automatically default to the older pledges unless you and your data entry folks remember to babysit them.