Pledge Installment Balance

Hannah White · February 27

Is there any way to pull installment balance using query in RE? I don't believe there is but I wanted to check with the community. For this project I am unable to use reports/exports since it involves Power Automate, and I need to pass query results to a csv file.

Would be happy to hear opinions from others on this! Thanks!

Beth Gallagher · February 27

@Hannah White query a gift list on pledge balance > 0 and output fields you need. Not sure if I am oversimplifying what you are trying to do?

Alex Wong · February 27

@Hannah White
there isn't a way to do this via query, you need to use gift v2 api for getting installment and payment details.

unfortunately, gift v2 api for getting installment/payment is “single” use, you have to issue per pledge. so depending on what you are trying to do, this may not work well.

Hannah White · February 27

@Alex Wong

Thanks for the response! For more context, I created a Power Automate flow that puts the query results into a csv, then it converts to an excel and runs scripts to format it so we have a shared excel file of pledge information. Then, I attached the csv to Power BI. It gets automatically updated every morning.

Having installment balance would just help my team understand who needs to be contacted or not. We have several multi-year pledges, so I wanted to make it more intuitive for them. But it seems like gift v2 api may not work for that.

Hannah White · February 27

@Beth Gallagher

Thanks for your response! I think it is a little different since we have multi-year pledges. So the entire pledge balance is for all years and I am just looking for the committed dollar amount for 2025. Thus, I have to use installment date instead. But with that, I can't determine which installments have been paid or unpaid unless I use a report. Which I can do, but I was trying to create an automated list that can be viewed daily.

Alex Wong · February 27

@Hannah White
There are 2 approaches I can think of, depending on how many pledges you have.

If you have ~1000 active (with balance) pledges, you can use the gift v2 api to get installments. Gift v2 get installment provides you with the balance of the installment too, so you don't really even need do the get payment api call. You just need to filter on installment date is current year and balance is > 0

If you really have a LOT, then you can do calcuation. Meaning, you query on pledge that has balance. Output installment info (available through gift query). Then for the same pledge, sum up the amount due by installment date after this year. This amount due after this year if = pledge balance means installment this year or before is paid off (or write off). otherwise, installment that is this year or before isn't paid yet.

Carol Grant · March 6

@Hannah White- Hi Hannah, I was just reading your post and I thought I would tell you how I pull in installment balances. I know you asked for a query but I do a gift export. We have multi year pledges and in the gift export I pull over up to 10 installment for each pledge to make sure I got them all. Then I export it as a CSV and import it as a CSV into Power BI. Unless you have Queue, this wouldn't be an automated process though.

Once it's in Power BI, I do some pivot/unpivot in the Power BI after that but then it's setup each time I refresh it.

Hannah White · March 6

@Carol Grant

@Alex Wong

Thank you! I appreciate everyone's response. I will definitely be looking into everyone's advice!

Liz Fay · March 7

@Hannah White Could you use an export from mail? I had to create a custom pledge report a couple years ago and using the export from reminders gave me almost all the information I needed. It breaks each installment onto it's own line.

Pledge Installment Balance

Comments

Categories