RENXT: Linking a gift to a pledge in PowerBI

Lana Sain
Lana Sain
Second Anniversary Name Dropper Participant Facilitator 1
in SKY Developer General Discussions

I'm using the custom Blackbaud Power Bi connector, and I wondering how you guys are able to connect a Gift Id with gift type = pledge payment to the same Gift_Id with a gift type = pledge. In other words I need to a link from an original pledge to the payments made against that pledge, but I can't find a field like this in Gift or Gift Splits. What am I missing?

Tagged:

Best Answer

  • James Belich
    James Belich
    First Reply Kudos 1 First Anniversary Name Dropper
    edited March 30 Answer ✓

    @Lana Sain The field on the back-end is the GFLink field - this is that field that connects a pledge payment to the pledge. Unfortunately, this field is not available via Query, which severely limits our ability to work with pledges and their related payments.

    I started an idea to add the GFLink field to Query: https://renxt.ideas.aha.io/ideas/RENXT-I-8271

Answers

  • Nick Marchese
    Nick Marchese
    Kudos 1 Name Dropper Facilitator 1

    Hey @Lana Sain. I sent you a message. I may be able to help you out. Let me know!

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

    This data is available via SKY API:
    Get Pledge Installments
    Get Pledge Payments

    However, there are a few considerations:

    • both API is "single" pledge endpoint, meaning you need to get data per pledge, and for reporting purpose, you will need to somehow store the data somewhere that can be read by power bi (i.e. Azure SQL database; Fabric; SharePoint; etc)
    • normal "gift list" (from SKY API and Power BI connector) does not provide Write off gift type, so that makes a "missing" piece in an overall pledge report that requires use of Query API. Without the actual write off gift record row, you will not be able to do payment/write off report/column clearly

    Once data is all available, a lot of possibilities:

    Year over Year Pledge collection:

    image.png

    Pledge Invoicing (Reminder): based on a date selection calculate columns of amount due (overdue + amount due in installment date up to the selected date (i.e end of month, end of quarter, end of year)

    eInvoicing that auto send to emailable donor the invoice that has been merged and pdf-ed.

    If you are interested in any/all of these and would like to discuss, you can email me at alex.wong@ffxconsulting.co to discuss how you can potentially handle this yourselves or need a helping hand to get started.

  • James Belich
    James Belich
    First Reply Kudos 1 First Anniversary Name Dropper

    @Alex Wong Thanks for pointing out the API endpoint for Pledge Payments. I'm using your PA flow to pull data for Power BI reports, which of course can only pull the fields that are available in Query (unless I'm missing something). I'm hoping that Blackbaud will add the GFLink field to Query, as this one field would make reporting on pledges and payments so much easier. I'll have to take a look at the endpoint though - maybe there's a way to use that to bring that field into PBI.

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

    How many pledges do you have in your system?

  • James Belich
    James Belich
    First Reply Kudos 1 First Anniversary Name Dropper
    https://community.blackbaud.com/discussion/comment/312149#Comment_312149

    Alex, I'm not sure if this was meant for me or the original poster, but we have about 2,500 pledges total, although only about half a dozen or so active pledges at this time.

  • Alex Wong
    Alex Wong Community All-Star
    Tenth Anniversary Kudos 5 Facilitator 4 bbcon 2025 Attendee Badge
    https://community.blackbaud.com/discussion/comment/312312#Comment_312312

    it was for you James.

    2500 pledges, even though not all of them is "active" (writeen off, paid, mix of both). in order to do good report and dashboard for pledges, you will need installment details and payment (write off) details for all pledges.

    2500 pledges, 2 calls (1 for installment, 1 for payment) each = 5000 API calls.

    Assume ~2s per call, that will be 10K seconds, ~3 hours. If you run concurrency, say 2. That reduce time to get data to 1.5hr. Max I say would be 5 concurrency. so that's ~30 min.

    This will give you a "base line" then you can just get installment and payment of changed pledge, payment, write off.

    Very doable.

  • James Belich
    James Belich
    First Reply Kudos 1 First Anniversary Name Dropper

    Thanks, @Alex Wong. I'll have to look into this more. Right now all my Power BI reports are using your Power Automate flow to pull the data, so I'll have to play around with how to use the API endpoints to also get the pledge payment data. Most of what we're doing reporting-wise is focused on the past couple of years, so that should be very manageable in terms of API calls.

Categories