A Monster Has Appeared - Installment Balances???

Zane Magnuson 2

Just as a disclaimer, I absolutely love and admire all things Raiser's Edge. But every so often, you find that one simple question for which there is not a simple answer. The first one I found was querying based on Summary Volunteer Hours.Today, I found out about Installment Balances.


I'm trying to build a query that groups people (or gifts) based on the following:

• Has a pledge that was made in any year
• The pledge has an installment due within the past fiscal year
• The installment that was due within the past fiscal year has not been paid off fully

The problem is it seems that you cannot actually hone in on Installment Balances - you can only really do Pledge Balance in queries. Here are some things I've tried and why they're problematic:

• "Last Payment Made" - This will exclude someone that made a partial payment within the appropriate date range
• "Sum of Gifts = 0 with sub-criteria (Gift type is Pay-Cash, Pay-Stock, etc...)" - Some constituents have multiple pledges. If they paid off an installment on one pledge but not on another in the same year, they will not be pulled in with this criteria.
• RE's Canned Pledge Reports - If I manage to get these to display what I need (already a tall order), there's not much I can do with it. A query would allow me to build our own exports and more customized reports.
• "Installment Amount Due" - I can't quite tell, but it seems like this focuses on Amount Due rather than Balance Due. 

I've tried tackling this from both Constituent Queries and Gift Queries, but I cannot seem to pin it down. Has anyone else run into this? Any suggestions or workarounds?

Elaine Tucker

are those "and" criteria or "or" criteria?

Ryan Hyde

This is fascinating. You're absolutely right that there is a problem.


Playing with this for just a few minutes, here's the best solution I could come up with


Query type = Gift Query

Criteria:

• Pledge Balance > $0
• Installment Amoutn Due > $0
• Installment Date Due between [range]

So this gives you all gifts that are not paid off that had an installment due within whatever range you want. However, the "Installmetn Amount Due" is NOT the balance. The balance could be zero for these installments. There is indeed no Installment Balance field to query on, even though it clearly exists in the payment schedule within a gift. That's just silly.


But by including the "pledge balance >$0," you at least don't find gifts that are already paid off. The only way I can think of to clean it up is to exclude gift IDs that are currently on time. 


The only other thing I can think of is to use the Past Due Report (under Pledge & Recurring Gift Reports). In fact, this might be the best solution. If you dial in the "gifts made after" and "past due as of" fields to relate to your fiscal year, you should see everything with missed payments from the previous fiscal year. 

Jen Claudy

I have a similar issue with a report I run for our Finance Department.  Pledge Status Report that needs to include balances, amts paid, etc. as of a particular date, which may not include the most recent payments.  Can't use the Canned Report because we also need to include credit card gifts as they're considered Receivables by our Finance Dept.


I ended up repurposing a Field on Gift > Misc to be the Pledge ID Field.  When entering a Pledge, the Gift ID gets copied to this field.  Then, when payments are entered, that ID carries thru to the pmt Gift Record and now I can export raw data from RE and use Access (or Excel) to match it all up and create the report that I need.  If a payment was applied to multiple pledges, I think I'd need to adjust my report process to accommodate two Pledge IDs in the field...and possibly need to enter the payment as two separate Gifts, but I haven't encountered this problem as yet (almost 5 years in).  And I do have a Gift Attribute that can be added if there might be something about the Pledge or Payments requiring manual review and/or edits, which shows up in the report process.


Takes a bit to set up the process, but maybe this gives you some ideas on how you can set something up in your database to help meet your needs more efficiently.


Also, you could use a Canned Pledge Report and check the "Create a Query" checkbox on the General Tab which should then create a Query of the Records from that Report...a process you can run a few times and then merge those Queries together or Globally Add an Attribute to be able to group them together.  Still thinking you won't be able to actually get the Amount data you really want, though.

Zane Magnuson 2

Also, you could use a Canned Pledge Report and check the "Create a Query" checkbox on the General Tab which should then create a Query of the Records from that Report...a process you can run a few times and then merge those Queries together or Globally Add an Attribute to be able to group them together.  Still thinking you won't be able to actually get the Amount data you really want, though.

I wonder if I could just create the output queries, then build out an Export and/or Crystal Report that is based off the output query. It's clunky, but it's starting to look like there isn't a streamlined option no matter how you approach this.

Christine Cooke 2

Zane Magnuson:

Also, you could use a Canned Pledge Report and check the "Create a Query" checkbox on the General Tab which should then create a Query of the Records from that Report...a process you can run a few times and then merge those Queries together or Globally Add an Attribute to be able to group them together.  Still thinking you won't be able to actually get the Amount data you really want, though.

I wonder if I could just create the output queries, then build out an Export and/or Crystal Report that is based off the output query. It's clunky, but it's starting to look like there isn't a streamlined option no matter how you approach this.

 

This is what I usually end up doing, is checking the Query output box in the canned report and then pulling that query through export to get the extra fields I need.  Sorry there is not a prettier and/or easier way, but it is relatively direct.  I find myself doing this with a lot of canned reports that happen to be missing one or two key fields required for analysis or reporting.

Christopher Barona

I usually create a Cash Flow or Pledges Past Due report, export that and either an Export or exported Query results to Excel, and use a simple sum formula to combine them. If you need similar results on a regular basis, and know how to work with data tables, you can even make a third excel file that can refresh and recalculate the results by overwriting the original exports whenever you need to refresh the data. A bit jury-rigged, but I find it's helpful if you know you're going to be doing this sort of thing a lot.


However, you're absolutely correct, this is a monster that Blackbaud needs to slay. I can't claim to know much of the background programming, but I'd think a "Total Amount of Installments" summary Query and Export field would solve a lot of headaches.

Patty Driscoll 3

@Ryan Hyde I use an export from Mail>Reminders to do this and it works perfectly in database view. My problem is that I am trying to replicate everything in NXT query and this doesn't work. You can never see the amount of the pledge installment that is past due, because that field for installment balance is missing from NXT query

Christine Robertson 4

@Patty Driscoll While I am all for using the features in the webview, if it doesn't work and the database view does, I would recommend reporting this via the Idea Bank and continuing to use the database view until the webview addresses this issue. As rapidly as the webview is changing, that may not be very long!

Patty Driscoll 3

@Christine Robertson. I would like to think that they would fix this basic functionality issue quickly, but there is already a post in the idea bank dating back to 2014 and 2021, so its not getting addressed ? If you want this too, please vote and get this bumped up.

Elizabeth Johnson

@Patty Driscoll May I suggest you share the Idea Bank link here - might get some attention now that this old thread has been revived.