Getting a true pledge balance for each campaign not based on original pledge split

Jessica Ortega

Hopefully someone has had a similar situation and can offer some advice! We use different campaigns in Raiser's Edge, for instance, Endowment Restricted, Annual Unrestricted, etc. We also have separate pledge receivable GL accounts that go along with these campaigns. We are basically trying to run the pledge status report to match our pledge receivable GL. However, we have split pledges, and we also don't always split the payment based on the original pledge split. We run the pledge status report filtering by campaign but it will display any payments based on the original split of the pledge and not how we truly split it internally, causing the reports not to match the GL. I've tried a query based on campaign and also a query with the individual funds in that campaign and the reports pull funds from different campaigns (because of the split pledges)/ I was told that all RE reports will only appear based on the original split of the pledge and not how we split the payments ourselves. Has anyone gone through something similar to this and what did you do? Thank you!

JoAnn Strommen

Jessica Ortega‍, how I wish I had an answer for you. This is one of the shortcomings in RE reporting - split gifts.


We're heading into a multi-fund multi-year campaign. I had a meeting with development staff and tried to explain what can happen with reports for the split gifts. I'm guessing I'll be in similar situation soon.

Kathleen Eastman

Yes, I had to deal with this unfortunate issue. Although we do not post to the accounting software, I do provide Cash Flow reports showing the balance of our pledges by campaign, so in order for those to come out right, I created a pledge with the same date/appeal, but with the campaign the payments were applied to before adding the payment. Then, I go back into the original pledge and do a gift amendment and change the pledge amount there. The donor changed the designation for some payments but not all and it was years after it was booked, so there was no way to know this would happen. But, if a donor tells us to split the pledge between campaigns, I book two separate pledges.

Theresa Stufano

In my query, I output the Campaign Split Amount, the Fund Split Amount, and the Appeal Split Amount and also the Gift Amount. Then I can see how the gift was split. I hope this helps.

Karen Diener

Jessica,

What financial software is your organization using? That may help us guide you.

GL accounts are tied to the Fund in Raiser's Edge and not the Campaign. So if you need the Campaign to tie out to the general ledger, you will need to add something - probably an Attribute - to the Campaign record or potentially the gift record.

I think the other big pitfall is what people have already mentioned, which is the challenge of split gifts. There is a surprising disconnect between pledges and payments in RE when it comes to reporting, and you cannot easily export between the two.

Karen

Theresa Stufano

We do not link RE to our Financial software. Sorry for any confusion.

Rachel Kauer

@Jessica Ortega

We just ran into a similar situation and hoped the community might have insight because BB Technical Support came back at us with two opposite answers. For context, we use a Gift Detail and Summary Report, filtered by (Fund) for Pledge Type gifts, with the “use balance instead of amount" box checked off, and had been able to see split pledge balances accurately for months - until this month.

Suddenly our most recent pledge payment screwed the Pledge Balances on reports. I have read several articles that shed light on this issue (48155, 39480, 39715, and 40171) and much like the report we got from BB technical support - some explain how to do it as if the functionality is there and others seem to point out an unknown issue.

We had one tech review and confirmed we had everything right and was baffled that the report was still showing inaccuracy which conflicted with our GL-posting and detailed screenshare review.

Whereas the second outright insisted that although split pledges were functional, it was impossible to report on the pledge balances separately according to the level 2 support team regardless of what report you run, it simply doesn't calculate right. (Which I would say indicates a functional issue…)With no real explanation as to why this would be happening and why our data was reporting for months intact to suddenly experience an error with a recent payment.

My instinct is they are (in part) aware of an unknown error which they don't have a solution to. And that error does not follow a pattern and can happen at any time. Unfortunately the two methods of making split pledges are treated as preferences, not as differences in functionality.

To avoid this error, it seems the only viable, totally functional path at present is to create one pledge per fund code or campaign in your case, then apply split payments, to multiple pledges.

Otherwise reporting encounters data errors.

We are now looking at re-opening multiple months and a calendar year to possible write-off several pledges and re-do them. It's not worth it. Just today I provided feedback to BB on making sure to note the functional limits on any Knowledgebase articles mentioning the two options so users can be informed before proceeding into a mess. Best of luck!

Alex Wong

@Rachel Kauer
in order to do a good report on split pledge, it requires a lot:

• payment to pledge needs to have same campaign fund appeal package (CFAP) as the pledge being applied to, in order to “line up” the split of pledge that is being paid
• handling of payment applied to pledge where CFAP does not match to any split of pledge's CFAP and consider that as error that needs to be fixed (either on the pledge or payment)
• will require the use of custom report that reads from detail pledge/installment/payment/writeoff information that takes into consideration of all one-many relationship of the information, even then it is NOT an easy custom report to create

I have previously created such report using SQL directly accessing data from RE database tables, and still ultimately opt-ed to NOT use split pledge.

Rachel Kauer

@Alex Wong

Agreed, split pledges are not worth it due to limited report functionality. Both myself and our COO are inclined to abandon split pledges after we resolve our current data errors for this month. This, probably means we will end up writing off and removing all other open split pledges and reentering all that data to get it clean, which will involve reopening every month back to December.

Sadly, somehow my org has been doing split pledges and reports on split pledge balances with Detail and Summary Reports for years and the reports we were using showed accurate pledge balances historically and since I started, and we only just experienced issues this month. As I am brand new to my org and came from a different database, I have no insight into why we were entering split pledges to begin with, I would like to think if our previous processor was aware of the issue they would not have recorded them that way, but I suppose if they never encountered errors, there was no reason for them to know otherwise.

Our old gift processor did not leave notes, and our database admin had tried to cover her job for almost 6 months in addition to her own as best she could, and much knowledge was undoubtedly lost following the processor's departure. There was no protocol or procedures guide established for our org for Gift Processing or our Database.

I can't hammer home enough to folks how important it is to document everything because you always have to think about how if the team reads the data 10 years from now and the whole team has turned over how will the data look to them with no foreknowledge of why it was entered a certain way, or decisions from 10 years prior.

I have been writing my org a protocol and procedures manual since I started and will certainly be adding a detailed explanation of why split pledges are not to be entered per our updated protocol, and record procedures for entering what the donor intends as a split pledge as separate pledge records by fund/campaign/appeal, paid with split-payments.

That said there is an itch to know why split-pledge balance reports data was intact for years for us and has just gone wrong now and it troubles me that we don't know. I like to understand why things work or don't work. I am deeply dissatisfied with unknown errors. Specific, known, explainable, but unfixable errors are one thing, but unknown is frustrating.

Considering so many users seem to stumble into this issue, I only wish we knew why it was happening, even if it was programming-related and complicated. The mystery persists!

Alex Wong

@Rachel Kauer
I do not use and not aware of the specific report you talking about as we use custom report, previously with SQL when we were 3rd party hosted, now with Power BI on data warehouse that sync from SKY API since migrated to RE NXT hosted by Blackbaud.

My assumption that the report may be based on either (1) the pledge split CFAP matching the payment CFAP or (2) just simple percentage of split.

I have a strong feel it is #2, as #1 would be way too difficult for a canned report. Meaning if a pledge is split 30% to CFAP 1 and 70% to CFAP 2, when calcuating balance, it will do the same percentage for CFAP 1 and 2. If payment applied to the pledge was done in the same percentage, then the balance would be correct, if not, it would not be. Something you can look into.

JoAnn Strommen

@Jessica Ortega Welcome to BB and the fun and frustration of Raiser's Edge.

I do not know why the function of the report would have changed. That seems odd to me. Is it possible there is an option that is not exactly the same somewhere?

Just a comment in defense of split gifts. At my previous org we quit using but we only had a few split gifts that started causing us issues. Here, we have many, many split gifts. Some with 5-7 funds. The benefit of having them in one gift comes with analytics. The gift is counted as one gift at $XX,000 vs. multiple gifts at $X,XXX. When looking at largest gift data, you have a more accurate report. We are in the midst of a capital campaign with a multi-fund ask. While one can use summary data, there are advantages to having $ in one gift record and there are disadvantages.

I wish you the best on pulling the data you need. I wish I know a solution for you.

Rachel Kauer

@Alex Wong
@Jessica Ortega

Thanks, Alex, we thought that #2 may have been what occurred to the pledge balances on each fund code with the recent error following the newest pledge payment. However previous payments had burned down the amounts split between funds correctly, even when the payments were at completely different percentages from the pledge split percentages. For more detail in our example:

Pledge Split: $1,250 to fund code A, and $250 to fund code B.

Payment 1 rec in DEC for #300 to fund code A.

(We enter in batch, click yes for apply to pledge, modify the split to include all $300 to one fund code only, commit and GL-post successfully)

End of the month we run Gift Detail & Summary (Detail) (RE NXT Database View, Reports> Financial >Gift Detail and Summary Report

We select Detail Report, Soft Credit to Donor, Matching Gift Credit to Matching Gift Donor, check of use balance amount instead of gift amount, and filter by Fund Code and do this for each code (In Jessica's case we would filter by campaign), the Filter Gift Type for Pledges only.

Then the detailed report shows the individual pledge balance by each fund code, in our example after the first payment it shows $1,050 for Fund A and $ for Fund B. This is correct, although the payment was to only one of the two fund codes and doesn't follow the original pledge split and it didn't default to a percentage distribution. Which it never has before March, so we continue on as usual

Then this month we get a second payment, this time $200 again to only Fund code A.

It also GL-posts correctly as usual.

We rerun the reports for March end of the month and suddenly the pledge balance for this pledge balance shows an error. $800 for fund code A and $200 for fund code B.

Instead of $750 for Fund Code A and $250 for Fund Code B.

Somehow a random $50 is showing as being paid towards the fund code on this specific pledge, a fund to which no funds have yet been paid. We triple-check the gift info in RE, the GL Posting, and BB tech 1 reviews, everything appears correct and yet for the first time, the pledge balances are miscalculating by fund.

And it's again not a percentage split or anything that you might expect to happen with this error. The $50 is so arbitrary. That's when BB tech two came in and said that our pledge balances to be calculated correctly up until this point should never have been possible in the first place.

Jessica if you are reading this I hope this sheds a little light on what I mean by the errors seemingly not having a pattern in reporting and why I would advise leaving split-pledges behind going forward. We did find a way around the issue you are having with just querying and were able to make this work using the Gift Detail and Summary Report for us for years, but ultimately it failed and created a huge data miscalculation on a report which our auditors will look at this summary, which is why the miscalculation is such a big deal for us operationally. We can't move forward until it gets fixed. That's why I recommend taking BB's second option for pledges split across multiple funds, campaigns, or appeals putting them as separate pledges, and making split payments to them.

Even if pledge balances are more of a desire vs. necessity or an advancement are only tracking for forecasting or team purposes now, long term if it ever becomes something you need to provide an audit on or other financial reporting on the business end, continuing to do split-pledges may continue to get you in trouble.

Unfortunately like us, I don't see a way out of the trouble your existing split-pledges are causing you. Luckily we only have 3 open currently that we would have to redo. It sounds like you are too far into a campaign to redo the ones you have. If you have completed pledges, the detail and summary report might report on their balances correctly, but anything active/unfinished is subject to miscalculation.

Alex Wong

@Rachel Kauer
i did a quick look through KB for the canned report you said

and this has some good info on what option use that may cause how the pledge balance will be calculated. You can see if it fits your situation of miscalculation.

Regardless, @JoAnn Strommen I understand what you said about “largest gift data” so having separate pledge will affect this. However, my org doesn't look at largest gift, we look at “annual total giving” as analytic for what “category” donor is at, we care less about if a donor pledged or made a one time gift of a certain high amount, so using separate pledge made more sense to us and avoided 1 less 1-to-many relationship in the already complicated situation with pledge

Jeannie Goings

@JoAnn Strommen largest gift amount is exactly the reason why we continued to use split gifts at a previous org where I worked.

Sherry Aldrich

@Jessica Ortega The reporting out on split gifts is nearly impossible to be accurate so we don't use the functionality. We will just enter two separate gifts into the record to make it clean data.