Finding specific installments to Pledges/inconsistencies in reporting

James Parsons
James Parsons
Sixth Anniversary Kudos 1 Name Dropper Facilitator 1
in SKY Developer General Discussions

We have a PowerBI report — Gift Chart — that shows our created gift chart and the up to date fundraising progress for each fiscal year. We used to do a new report for each FY but have found a way to compile them into a single report using a FY slicer.

However this showed a discrepancy in some previous FYs we hadn't previously reported on. After investigation we found that it's due to pledge installments that have been written off, where a different installment in the same pledge went through.

To fix this in the report I've tired filtering out terminated pledges (this didn't work because it also removed the paid installment and caused a discrepancy in the other direction); filtering by specific gift number (not ideal, but it didn't work anyway for the same reason); and accessing the Installments API (in preview, but didn't provide the data I need). I can't just filter out write offs because that type is within the pledge and I can't seem to access it.

Has anyone had success filtering out only the written off portion? Or am I going about this the wrong way?

Answers

  • Bryce Howard
    Bryce Howard
    Fourth Anniversary First Reply Kudos 1 Name Dropper

    I use python but you can use this with whatever your tech stack is. I do this with two queries where both just output the system IDs.  I then use the system ID to make GET requests to get more info.

    • write_off query:
      • Gift Type one of Write Off, Matching Gift Write Off
      • AND ( Gift Date Added equals Today
      • OR Gift Date Last Changed equals Today )
    • I then take the IDs and get the gift info:
      • method="GET", endpoint=f"/gift/v1/gifts/{writeoff_id}"

    • pledge_install_payments query - We only ever write-off pledges:
      • Gift Type  Equals Pledge
      • AND ( Gift Date Added equals Today
      • OR Gift Date Last Changed equals Today )
    • For this, it takes two requests to get the full details I need:
      • installments_response = self.auth.make_request("GET", f"/v2/gifts/{pledge_id}/installments" )
      • payments_response = self.auth.make_request( "GET", f"/v2/gifts/{pledge_id}/pledgepayments")

    I put the write-offs, installments, and payments into separate tables from gifts.

  • James Parsons
    James Parsons
    Sixth Anniversary Kudos 1 Name Dropper Facilitator 1

    Thanks, @Bryce, I'll take a look at this. Very inexperienced with Python but this looks pretty straight forward.

    I've build these queries and am going through the results; looking at the endpoints as well. Which information is helpful for you in the Gift V2 endpoints? I'm not seeing what I need — the type of the installment. When I look at the gifts that are causing the discrepancies, it's Pledge gifts where one or more installment has been written off and one or more that went through. I'm not seeing the information I need here — do you hand;e write offs differently?

  • Alex Wong
    Alex Wong Community All-Star
    Tenth Anniversary Facilitator 5 Kudos 5 bbcon 2025 Attendee Badge

    the importance is knowing the relationship between all the record types, which can be confusing at first.

    1 pledge can have more than 1 installment
    1 installment can have more than 1 "payment" (this can be a payment gift OR a writeoff gift)
    1 payment (real payment gift) can be applied to more than one installment (this can be installment from different pledge).
    1 write off can be applied to more than one installment but not to different pledge.

    If you can't understand the underlying relationship between the above, you will have a hard time creating a good report around pledge.

    now for data comes with what endpoint:

    • SKY API gift list does not give you write off gift (write off gift is itself a "gift record")
    • SKY API get installments provides the installment details
      • main importance is id, amount, and date
      • not as useful is balance, as this does not tell you how much of the installment is paid vs written off
    • SKY API get payments provides the "payment" details
      • main important is installment_id, payment_gift_id, and amount_applied
      • payment_gift_id points to either a write off gift system record id or payment gift system record id, you will need the gift record's gift_type property to know if the amount_applied is a write off amount of payment amount

    So the "missing" piece is write off gift info, which you will need to use Query API to get.

Categories