Gift ID and Past Due Amount

Tom Klimchak · September 2016

I've tried searching the forums and didn't come up with anything solid...

I'm working a gift query (and maybe an export) that shows the Gift ID and the Past Due Amount as of a certain date.. Ideally I'd like to see GiftID and then the "past due" amount (so look at installments and see what hasn't been paid up to a certain date)....

I haven't seen any simple (or even complex) way to do this easily. I'm getting the vibe that payments have no direct connection to the installments scheduled for a pledge. It would even be helpful if I could query on "Installment Balance" but that doesn't seem possible....

Has anyone successfully tackled the pledge payment/installment problem without using the canned reports?

JoAnn Strommen · September 2016

Thomas, It's a canned report in a sense but have you tried the data generated by Mail > Reminders? It doesn't pull Gift ID but pulls about everything else related to a gift. For Reminder select 'Create Custom Data File'. Include the constituent ID in the fields list. I export this info regularily to see who we are to bill. While it doesn't show past due dates, it shows amount past due (based on the run date you select) and you have it show installment amount and frequency so you can see fairly easy if they are behind one payment or ten. I think it would get what you needed. You can export directly to excel or create an output query if you need a query - maybe you can add Gift ID in query output.

Just a thought - might spur another work-around.

Tom Klimchak · September 2016

JoAnn Strommen:

Thomas, It's a canned report in a sense but have you tried the data generated by Mail > Reminders? It doesn't pull Gift ID but pulls about everything else related to a gift. For Reminder select 'Create Custom Data File'. Include the constituent ID in the fields list. I export this info regularily to see who we are to bill. While it doesn't show past due dates, it shows amount past due (based on the run date you select) and you have it show installment amount and frequency so you can see fairly easy if they are behind one payment or ten. I think it would get what you needed. You can export directly to excel or create an output query if you need a query - maybe you can add Gift ID in query output.

Just a thought - might spur another work-around.

JoAnn, yeah that was my "fall back plan" and that's what sort of set me off on this quest anyway: There's clearly a way to do it because it's in that report, but Blackbaud doesn't seem to want to make it easy to access that total. I suspect that canned report is simply summing up installment balance amounts based on the dates entered on the periods tab.

Ideally I'd like to combine this past due data with more data from a pledge payment prediction and balance report I put together on a monthly basis (with GiftID being the key to each record).

I started poking around in the SQL tables of Raiser's Edge. I might eventually be able to build a crystal report based off what I find, but I was hoping for a little more straight-forward method... ahh well.... Thanks!

Marie Stark · September 2016

Hi Tom! I have a custom report but as you say, it is a cumbersome process of exporting each installment, payment, and balance due on each installment.

James Andrews · September 2016

Thomas Klimchak:

I started poking around in the SQL tables of Raiser's Edge. I might eventually be able to build a crystal report based off what I find, but I was hoping for a little more straight-forward method... ahh well.... Thanks!

How do you feel about crystal reports without having to go directly off the tables?

I have a report that does this -- it exports the pledges, all installments, all payments, and then asks you for an "as of" date. Then the report calculates the pledge balance as of the date you entered, based not on the installment balance and due date, but on the payment amount and date. It also calculates amount due per fiscal year for the next five, and then beyond. It does this by calculating the FY of the "as of" date, comparing it to the FY of the installment dates and payment dates, and then adding up paid amounts if they are prior to the "as of" date. There are formulas for what is due each FY, what's been paid each FY, and the balance, and then running totals sum up each of those things.

I started off creating this report from the installment balance, but we found that, for instance, if you're running the 6/30 report on 7/10, and someone has made a payment on say, 7/5, the balance for that installment will be 0, despite the fact that on 6/30 it had a balance -- because RE calculates those things on the fly during export. So I fixed it to ignore the balance and calculate everything by itself.

Tom Klimchak · September 2016

James Andrews:

Thomas Klimchak:

I started poking around in the SQL tables of Raiser's Edge. I might eventually be able to build a crystal report based off what I find, but I was hoping for a little more straight-forward method... ahh well.... Thanks!

How do you feel about crystal reports without having to go directly off the tables?

I have a report that does this -- it exports the pledges, all installments, all payments, and then asks you for an "as of" date. Then the report calculates the pledge balance as of the date you entered, based not on the installment balance and due date, but on the payment amount and date. It also calculates amount due per fiscal year for the next five, and then beyond. It does this by calculating the FY of the "as of" date, comparing it to the FY of the installment dates and payment dates, and then adding up paid amounts if they are prior to the "as of" date. There are formulas for what is due each FY, what's been paid each FY, and the balance, and then running totals sum up each of those things.

I started off creating this report from the installment balance, but we found that, for instance, if you're running the 6/30 report on 7/10, and someone has made a payment on say, 7/5, the balance for that installment will be 0, despite the fact that on 6/30 it had a balance -- because RE calculates those things on the fly during export. So I fixed it to ignore the balance and calculate everything by itself.

James,

Ooooo! I like that idea.... I hadn't thought of just dumping it all to Crystal, but that kind of makes sense. Maybe I'll give it a try and see how far I get. We have every installment plan imagineable (regular and irregular) and we have people overpaying and underpaying their installments. Thanks for pointing me in the right direction! I reserve the right to ask more questions if I get lost in the weeds...

-Tom

James Andrews · September 2016

Thomas Klimchak:

James,

Ooooo! I like that idea.... I hadn't thought of just dumping it all to Crystal, but that kind of makes sense. Maybe I'll give it a try and see how far I get. We have every installment plan imagineable (regular and irregular) and we have people overpaying and underpaying their installments. Thanks for pointing me in the right direction! I reserve the right to ask more questions if I get lost in the weeds...

-Tom

For sure. My main piece of advice is -- and I'm sure you probably know this already but it tripped me up big time -- test for nulls in every formula. This was giving me some missing payments, installments, etc, so I went back and added if isnull {field} then 0 else before all of my formulas.

I know you can set default values for nulls but I like having it in the code and not having to remember whether I set that or not.

John Heizer · September 2016

James Andrews:

Thomas Klimchak:

James,

Ooooo! I like that idea.... I hadn't thought of just dumping it all to Crystal, but that kind of makes sense. Maybe I'll give it a try and see how far I get. We have every installment plan imagineable (regular and irregular) and we have people overpaying and underpaying their installments. Thanks for pointing me in the right direction! I reserve the right to ask more questions if I get lost in the weeds...

-Tom

For sure. My main piece of advice is -- and I'm sure you probably know this already but it tripped me up big time -- test for nulls in every formula. This was giving me some missing payments, installments, etc, so I went back and added if isnull {field} then 0 else before all of my formulas.

I know you can set default values for nulls but I like having it in the code and not having to remember whether I set that or not.

Wouldn't selecting "Default Value For Nulls" in the formula take care of that issue for you?

James Andrews · September 2016

John Heizer:

For sure. My main piece of advice is -- and I'm sure you probably know this already but it tripped me up big time -- test for nulls in every formula. This was giving me some missing payments, installments, etc, so I went back and added if isnull {field} then 0 else before all of my formulas.

I know you can set default values for nulls but I like having it in the code and not having to remember whether I set that or not.

Wouldn't selecting "Default Value For Nulls" in the formula take care of that issue for you?

It would but to me it's just not that noticeable up there and an option that I always forget about, so I like to have it explicit in the code itself.

Brian Bates · February 2017

Tom Klimchak:

I've tried searching the forums and didn't come up with anything solid...

I'm working a gift query (and maybe an export) that shows the Gift ID and the Past Due Amount as of a certain date.. Ideally I'd like to see GiftID and then the "past due" amount (so look at installments and see what hasn't been paid up to a certain date)....

I haven't seen any simple (or even complex) way to do this easily. I'm getting the vibe that payments have no direct connection to the installments scheduled for a pledge. It would even be helpful if I could query on "Installment Balance" but that doesn't seem possible....

Has anyone successfully tackled the pledge payment/installment problem without using the canned reports?

Tom, agree 100%. There should an option to query and export showing past due amounts. The functionality is there within RE . . . we use volunteers to track down and remind folks of their pledge committment, so being to export out the past due along with contact info and be able to sort based upon zip code, solicitor, amount, time past due, etc. would be incredibly beneficial. So, as others have done, we created a custom report that shows each donor and their 30 - 60 - 90 - 90+ past due ranges.

Good luck!

Christine Cooke 2 · February 2017

Tom Klimchak:

I've tried searching the forums and didn't come up with anything solid...

I'm working a gift query (and maybe an export) that shows the Gift ID and the Past Due Amount as of a certain date.. Ideally I'd like to see GiftID and then the "past due" amount (so look at installments and see what hasn't been paid up to a certain date)....

I haven't seen any simple (or even complex) way to do this easily. I'm getting the vibe that payments have no direct connection to the installments scheduled for a pledge. It would even be helpful if I could query on "Installment Balance" but that doesn't seem possible....

Has anyone successfully tackled the pledge payment/installment problem without using the canned reports?

Besides the Reminders as JoAnn mentioned already -- which could be exported as a query that you could then choose which fields you want. There is also a canned Past Due report. And that too can be exported to query.

Gift ID and Past Due Amount

Comments

Categories