API Endpoint Transactional Data

Giovanni Franco

Hi everyone,

I looking to get some hints on which endpoint I can use to pull the transactional data for giving against pledges in a daily basis or yearly basis. Which endpoint I can use it?

How you guys link user id with constituent id, is there any endpoint that I can pull that data too.

Thank you for any help.

Alex Wong

Get User API Endpoint for user to constituent data

Get Payments in Gift v2 API Endpoint for payment to pledge (and installment) details

Giovanni Franco

Thanks Alex.

For the Get user API Endpoint, I am being requested to enter an id. Is there any point that I can bring everything without asking the id.

I am assuming this ID parameter is the user Id from Blackbaud that I need to entered, is that correct? Thank you.

Alex Wong

Unfortunately, no, there is no List User endpoint. You need to enter the system record id of the user. this is a system incrementing ID that starts from 1, which assuming you don't have thousands of users, you should be able to loop through the ID from 1 onward to gather all user data, put into a table (SQL, Excel, etc) and then read back/filter, sort, etc.

Giovanni Franco

Just to clarify my previous comment, User ID is the incremental user created by Raiser's Edge system, is this assumption is correct? If so the first raiser edge would be 1., right. I added a couple of screenshots and the data does not bring much information.

What I am trying to accomplish is to link the "core" users data from the Educational Management System to links parents data from students or parents data associated to an activities.
Once I registered for the first time with Blackbaud, the system generates a unique user ID 7….

The constituent ID is another user id system record. How this two user id can be linked together? is there any field or system report that I can use to get this association done. Thank you.

I have more than 24K constituents in my RE and most of them has user id in the core system in BB.

Screenshot 2.png

Screenshot 1.png

Alex Wong

Yes, user system record id 1 (user_id field) would be the first user, Supervisor

You need special permission to run NXT Integration type of API calls.

Your screenshot only shows me you were trying to use Try It Console to get User with system record id 100, which you receive a 404 error. 404 means user_id 100 is not found (potentially this is a deleted user if you had more than 100, or you didn't have 100 user and not up to that number yet).

First try running with user_id 1, that should return Supervisor user. to ensure you have no problem running the API first.

Giovanni Franco

Thank you, it worked. However, I am looking to pull all constituent id with the respective user id from core Blackbaud. I was digging a little bit more and found out this end point. I realized that they have a constituent id, which can be linked with the host_id from core data.

https://api.sky.blackbaud.com/constituent/v1/constituents

Thank you for your help. I will check the trx end point you also mentioned, I will get back to you shortly.

Giovanni Franco

Alex,

While running the other end point, I go the following result. I am assuming this is the pledge id and the payments associated to that pledge.

Is there any other endpoint where I can get the following information

constituent id, transaction date, type of transactions, pledged_id (data), transaction type (fundraising, pledge, gift, etc) payment amount

Thank you again for your help.

{
    "pledge_id": "1000",
    "pledge_payments": [{
        "installment_id": "19",
        "payment_gift_id": "116",
        "amount_applied": {
            "value": 1600.0
        }
    }, {
        "installment_id": "19",
        "payment_gift_id": "421",
        "amount_applied": {
            "value": 1500.0
        }
    }, {
        "installment_id": "19",
        "payment_gift_id": "675",
        "amount_applied": {
            "value": 1500.0
        }
    }]
}

Giovanni Franco

Alex Wong

you need to run different API endpoint to get different record level information.

• installment_id
  • Run Gift v2 Get Installment to get list of all installments on a pledge by providing the pledge_id
  • Installment details like due date and due amount per installment becomes available
• payment_gift_id
  • Run regular Get Gift to get information about the pledge payment gift (this can be pledge payment or pledge write off)
  • you will get info on the full gift amount of the pledge payment (MAY differs from amount_applied to the specific pledge installment), payment date, constituent system record id of donor that made the payment
• From Get Gift where you get the constituent system record id of the donor that made the payment, you can use the constituent_id field with Get Constituent to get info like name, address, phone, email, etc
  • NOTE: payment gift's constituent CAN be a different constituent than the pledge constituent

Giovanni Franco

Hi Alex,

This is what I got while running Get GIFT

HTTP/1.1 200 OK


cache-control: no-cache

content-type: application/json; charset=utf-8

expires: -1

pragma: no-cache


{

    "id": "100",

    "acknowledgements": [{

        "date": "2011-07-25T00:00:00",

        "status": "ACKNOWLEDGED"

    }],

    "amount": {

        "value": 486000.0000

    },

    "balance": {

        "value": 0.0000

    },

    "constituent_id": "99999",

    "date": "2000-12-01T00:00:00",

    "date_added": "2001-06-28T10:35:25.36-04:00",

    "date_modified": "2020-10-27T16:38:42.097-04:00",

    "fundraisers": [],

    "gift_aid_qualification_status": "NotQualified",

    "gift_splits": [{

        "id": "100",

        "amount": {

            "value": 486000.0000

        },

        "campaign_id": "1",

        "fund_id": "2"

    }],

    "gift_status": "Completed",

    "is_anonymous": false,

    "linked_gifts": [],

    "constituency": "Legacy",

    "payments": [{

        "payment_method": "Other"

    }],

    "post_status": "DoNotPost",

    "receipts": [{

        "amount": {

            "value": 0.000000

        },

        "status": "NEEDSRECEIPT"

    }],

    "soft_credits": [],

    "type": "Pledge"

}

GET Installments

HTTP response

HTTP/1.1 200 OK

cache-control: no-store, must-revalidate, no-cache
content-length: 332
content-type: application/json; charset=utf-8

{
    "pledge_id": "100",
    "start_date": "2001-06-28T00:00:00",
    "frequency": "Single",
    "installments": [{
        "id": "19",
        "amount": {
            "value": 486000.0
        },
        "date": "2001-06-28T00:00:00",
        "sequence": 1,
        "balance": 0.0,
        "remaining_pledge_balance": 0.0000,
        "can_apply_payment": false
    }],
    "concurrency_token": "\"4780DFFECCBEB8F16F7DA85CA19978CD\"",
    "payment_method": "Other"
}

My main goal is to create an internal reporting solutions (dashboards) after implementing a DWH. I need to get the transactions as raw not looping every single one.

In order words what is the process that should follow,

Constituent Creation

Pledges
Setting a pledge for constituent
Registering installment payments on a pledge in a given year.
Balance Updates

Fundraising
Setting a fundraising
Registering gift for a fundraising
Balance Updates

Gift
Registering gift by constituent

Giving
Registering a donation by constituent

There is not way that I can bring these transactions all together like a Journal Entry, what would you suggest, I do not think looping every gift and then passing id would be a creative solution. Is there any report that I can run as raw data?

Thank you again.! :)

Alex Wong

you need different table of data that then be join to obtain all info:

• Constituent List - list of all your constituent using the List Constituent API, it returns 5000 max record per call, and you need to loop to get all
  • Query API can be used too, depending on how large your constituent table is
• Gift List - list of your all gift (excludes Write off gift), also return 5000 max record per call, and you need to loop
  • Query API can be used, but more complex when you start dealing with any one-to-many relationship: gift split, gift soft credit, gift fundraiser, custom field, etc
• Query API to get Write off gift
  • Required to use Query API as standard gift list api does not return write off gift record, and you will need this in order to do detail pledge reporting.
  • append these write off gift into the same gift table as all other gift records
• Get Installment - only available on per pledge gift basis, so you will need to loop each pledge to get all, then load everything into a single Installment table
  • Query API cannot be used as it does not provide installment system record id that is needed for detail reporting of installment payment
• Get Payment - only available on per pledge gift basis, so you will need to loop each pledge to get all, then load everything into a single payment table
  • Query API cannot be used

once you have all these tables, you can do what you want.

Giovanni Franco

Great thanks for the hint. I need to ask you something else. I was able to retrieve the gift list. However, once I am trying to run the installment end point I was not able to get the right response. See this simple JSON data set. Which field is the one that uses gift id based to pulls the pledged installment.?
"count": 171870,
"next_link": "https://api.sky.blackbaud.com/gift/v1/gifts?offset=500",
"value": [
{
"id": "99957",
"acknowledgements": [
{
"status": "NotAcknowledged"
}
],
"amount": {
"value": 0.0000
},
"batch_number": "2017-432",
"constituent_id": "99998",
"date": "2017-02-15T00:00:00",
"date_added": "2017-08-23T09:54:41.533-04:00",
"date_modified": "2020-09-29T14:25:46.128-04:00",
"gift_splits": [
{
"id": "99591",
"amount": {
"value": 0.0000
},
"campaign_id": "8",
"fund_id": "109"
}
],
"gift_status": "Active",
"is_anonymous": false,
"constituency": "Friend of ",
"lookup_id": "2017-4889",
"payments": [
{
"payment_method": "Other"
}
],
"post_status": "DoNotPost",
"receipts": [
{
"amount": {
"value": 0.0000
},
"status": "DoNotReceipt"
}
],
"reference": "2017 Kickoff Restaurant Participant",
"type": "GiftInKind"
},
{
"id": "9995372",
"acknowledgements": [
{
"date": "2018-02-22T00:00:00",
"status": "Acknowledged"
}
],
"amount": {
"value": 0.0000
},
"batch_number": "2018-163",
"constituent_id": "99998",
"date": "2018-02-09T00:00:00",
"date_added": "2018-02-22T09:30:10.277-05:00",
"date_modified": "2020-09-29T14:25:46.128-04:00",
"gift_splits": [
{
"id": "999700",
"amount": {
"value": 0.0000
},
"campaign_id": "8",
"fund_id": "109"
}
],
"gift_status": "Active",
"is_anonymous": false,
"lookup_id": "2018-2240",
"payments": [
{
"payment_method": "Other"
}
],
"post_status": "DoNotPost",
"receipts": [
{
"amount": {
"value": 0.0000
},
"status": "NotReceipted"
}
],
"reference": "Food Samplings for 600 Guests",
"type": "GiftInKind"
},

Also, would you please let me know how to work with the query API to pull the write off records?

Thank you again, I will check the get payments shortly.

Alex Wong

[
  {
    "id": "99957",
    "acknowledgements": [
      {
        "status": "NotAcknowledged"
      }
    ],
    "amount": {
      "value": 0
    },
    "batch_number": "2017-432",
    "constituent_id": "99998",
    "date": "2017-02-15T00:00:00",
    "date_added": "2017-08-23T09:54:41.533-04:00",
    "date_modified": "2020-09-29T14:25:46.128-04:00",
    "gift_splits": [
      {
        "id": "99591",
        "amount": {
          "value": 0
        },
        "campaign_id": "8",
        "fund_id": "109"
      }
    ],
    "gift_status": "Active",
    "is_anonymous": false,
    "constituency": "Friend of ",
    "lookup_id": "2017-4889",
    "payments": [
      {
        "payment_method": "Other"
      }
    ],
    "post_status": "DoNotPost",
    "receipts": [
      {
        "amount": {
          "value": 0
        },
        "status": "DoNotReceipt"
      }
    ],
    "reference": "2017 Kickoff Restaurant Participant",
    "type": "GiftInKind"
  },
  {
    "id": "9995372",
    "acknowledgements": [
      {
        "date": "2018-02-22T00:00:00",
        "status": "Acknowledged"
      }
    ],
    "amount": {
      "value": 0
    },
    "batch_number": "2018-163",
    "constituent_id": "99998",
    "date": "2018-02-09T00:00:00",
    "date_added": "2018-02-22T09:30:10.277-05:00",
    "date_modified": "2020-09-29T14:25:46.128-04:00",
    "gift_splits": [
      {
        "id": "999700",
        "amount": {
          "value": 0
        },
        "campaign_id": "8",
        "fund_id": "109"
      }
    ],
    "gift_status": "Active",
    "is_anonymous": false,
    "lookup_id": "2018-2240",
    "payments": [
      {
        "payment_method": "Other"
      }
    ],
    "post_status": "DoNotPost",
    "receipts": [
      {
        "amount": {
          "value": 0
        },
        "status": "NotReceipted"
      }
    ],
    "reference": "Food Samplings for 600 Guests",
    "type": "GiftInKind"
  }
]

Looks like you are not using the right ID, from your screenshot, you are using 99591, which is the gift split id, not the gift id. Gift ID is first level object property of "id" such as 99957, 9995372.

Also, installment and payment is for pledge gift type only, these 2 gifts are gift in kind, not pledge.

Giovanni Franco

Hi Alex,

Thank you for the clarification. How do i know which is the field is referring to pledge gift only? Also, which end point I need to use in order to get all pledges by ids with gift associated so that I can loop them through the installments endpoint. Than you.

Alex Wong

the "type" property tells you what type of gift it is. It will say "Pledge" if it's a pledge gift record. You can use Query API to filter on Pledge only. you can also use regular Gift List API to do the same.

Giovanni Franco

Thank you for your support Alex, I appreciated.

One more thing, how do I get the write off trx You mentioned that I have to query all of them. Would you mind to point me to the right direction?

Alex Wong

Use Query API, you can either save a Query in RE NXT and run that query by it's ID, or use Adhoc querying. There are templates available to do both, you can search template showcase for "query".

Gift Query:

At a minimum, you want the Gift System Record ID, Constituent System Record ID, Gift Date, Gift Amount. You can add other things as needed like GL Post Status, GL Post Date, etc.