REXNT How to pull a list with pledge write-offs?

Jackie Gerry

REXNT - How to pull a list with pledge write-offs?

Alex Wong

@Jackie Gerry
Are you asking about write off for a single pledge, or all write off?

If you are looking for write off for a single pledge. You can use the new Giftv2 API (beta) which has 2 new API endpoints: 1 for pulling all installment for a single pledge, 1 for pulling all payment (real payment and write off, as BB is considering write off as “payment” too). Using the get payment API will gives you something like this:

{
"pledge_id": "1635",
"pledge_payments": [{
"installment_id": "4",
"payment_gift_id": "1633",
"amount_applied": {
"value": 4000.0
}
}, {
"installment_id": "4",
"payment_gift_id": "1634",
"amount_applied": {
"value": 4000.0
}
}, {
"installment_id": "4",
"payment_gift_id": "1637",
"amount_applied": {
"value": 5000.0
}
}, {
"installment_id": "4",
"payment_gift_id": "676924",
"amount_applied": {
"value": 4000.0
}
}]
}

As you can see, it only gives you a payment_gift_id, you actually do not know if this is a Pay-Cash or Write-off yet. You will need to then use the regular Get Gift API on this payment_gift_id to get all info about the gift record, including the gift type to know.

If you are asking for all write off, then we are currently out of luck. List Gift API currently does not return write off even if you specify the write off gift type in the parameter. The way I am doing this currently is to use RE:Queue which runs a RE database query on Write off, and then process these write off gift record into my data warehouse.

Jackie Gerry

@Alex Wong
Thank you, this is helpful. I do need all write-offs and we do not have RE:Queue.

Alex Wong

@Jackie Gerry
Actually, I just recently thought of a way to handle this without the need to have RE:Queue.

You can initially have an export from RE database view with all the write off. add that to a database of you choosing.

then setup webhook for when gift is added, check the gift's gift type and if it is pledge write off, add the write off details to the database

then setup another webhook for when gift is deleted, delete from the database. (since a deleted gift you won't be able to SKY API on it to get if it's pledge write off, but it does not matter at this point as any gift delete, you should delete from your database anyway

finally, setup a webhook for when gift is changed, check the gift's gift type and if it is pledge write off, update the database.

I am only doing this “when gift is add/delete/change” for pledge write off since it is not coming from Gift List SKY API. All other gift is loaded/refreshed using another flow through SKY API Gift List, which is much more effiicent than through the “real time” add/delete/change of gift.

I will be implement this in the coming days and will report back how well it works

Alex Wong

I just implemented the method mentioned and it is working like a charm. I will be sharing about data warehousing of pledge, installment, payment and write off data in the next Power Automate User Group. If you want, you can sign up and attend to see it first hand. Otherwise, I will be making a community post on this in the next month or so.

Here's the post of the meeting: https://community.blackbaud.com/events/item/66/3881

Here's the link to sign up: https://forms.office.com/Pages/ResponsePage.aspx?id=yD_6MWcNAEuPWjqaacKBuKjDTmh6ORNGiqv926CnEShUMzdWTVhISDkyRVRRRkFOV0YxUFNPQ1ExUC4u