Automating Non-profit Acknowledgement Letters With Blackbaud API (Work in Progress)

This is a walkthrough for automating the creation of acknowledgement letters for Non-profits with Blackbaud NXT, using Blackbaud SKY API, Power Automate and Sharepoint.

As we build the flow, I will break down each Power Automate action, look at the JSON returned by our API calls, and show how WDL expressions are used to access and extract the specific data we need at each step.

What this Accomplishes:

This workflow ensures that on a set schedule, every donor receives the right acknowledgment—addressed to the right person, at the right address, using the right letter template—without manual intervention.

1. Getting Started: SKY API and Power Automate setup

   1. To get Power Automate Premium $15/Month as of 2/14/26

   2. Go to Blackbaud Marketplace and Connect with Power Automate

   3. Create a scheduled cloud flow in Power Automate

   4. Click the + sign and Search for Blackbaud Raisers Edge NXT List Gifts

   5. Blackbaud connector handles authentication internally and you will be prompted to sign-in with your Blackbaud account

2. Retrieving list of all Unacknowledged gifts

   Our first SKY API call is List Gifts, which retrieves all unacknowledged gifts from Raiser’s Edge NXT in a Json Array.

   1. Click on List Gift and update the parameters:

   2. Start gift amount: 1 — We want gifts that are greater than

   3. Acknowledgement status: NotAcknowledged

   4. Added on or after: (Optional, if your database is messy, you can select the earliest date for gifts you want. But you need a specific date format.) SELECT Fx and enter:

     formatDateTime('2025-05-10', 'yyyy-MM-ddT00:00:00Z')
   

   5. Type: Enter the the gift types below to avoid Pledges:

   Donation,GiftInKind,MatchingGiftPayment,PledgePayment,RecurringGiftPayment,Stock,SoldStock
   

3. Looping through List Gifts

   We loop through the output of List Gifts because each subsequent Blackbaud API call requires one unique key.

   Get a Gift requires the unique Gift ID of each Gift. Get Constituent requires the Constituent ID of each constituent hard credited for that gift.

   So the full workflow of retrieving gift + donor details, selecting the right template, and generating the letter runs once per gift, inside each iteration of the loop.

   1. Add the action “apply to each”, and and set the input to body('List_Gifts')?['value'] Alternatively, you can select Dynamic content (lightning icon) to select body/value from List Gifts.

   Why we use body('List_Gifts')?['value'] as input in apply to each

   After running your Power Automate Flow and opening the Raw Output of List Gifts, We will See:

   • The output as one big JSON object, defined by the enclosed outside bracket “{}”.
   • Inside this object are two other objects. headers and body. You will notice that the body object has the relevant data we need inside the JSON array called value defined by the enclosed “[]”

   Thats why we loop through body('List_Gifts')?['value']

   This is our first use of a Power Automate expression to access and extract data.

   • We use the body() function because it selects the body object we want
   • To safely access the JSON array value to extract it, we use ? in ?['value']. This insures that body(‘List_Gifts’) returns NULL if value doesnt exist or is NULL so it doesnt break.

   (I initially used outputs('List_Gifts')?['body/value'] but later changed it to body('List_Gifts')?['value']. They point to equivelent paths but body is more direct, reducing the chance of referencing the wrong thing. Output returns the entire response while body returns the value portion.)

   Why we Loop instead of Parse through List Gifts

   While we could parse the List Gifts output to use any fields it already includes, it provides key IDs (Gift ID, Constituent ID) but not all letter-ready details we need. For that reason, we still would need a For each loop to make these individual calls per gift.
4. Retrieving gift information for each gift in List Gifts

   Our first call inside our For Each loop is Get a gift. This call returns the fields we need for each record: gift date, gift amount, constituent ID, and appeal ID.

   Get a gift requires a gift ID as an argument. As For Each is looping over the value array returned by List Gifts, we can pull the gift ID from each iteration of gifts inside the loop and pass it as an argument to Get a gift.

   1. Expand “Apply to each” and select the + icon

   2. Search for blackbaud NXT get a gift

   3. Add the action, then enter the expression items('Apply_to_each')?['id'] or select Dynamic content (lightning icon) and look for system record id of gift from List gifts

   Why we use items() instead of body() or output() as an argument in Get a Gift

   We use the function items() because it returns the current item in a loop.

   We dont use body() or output() because control containers like For Each dont have an Output or a Body. We are going to introduce more Control Containers like Conditions and Switch later in the Flow.

   In the next step, we are going to get Constituent information using the output of Get a Gift

5. Retrieving constituent information for each gift in List Gifts

   List_Gifts now calls Get a Gift for each id in the body of List Gifts. Inside the output we can find the Constituent ID key of the Hard Credit Constituent who was credited for the gift. Using this Constituent_ID with Get Constituent will provide Title, First Name, Last Name and address information.

   1. Inside the For Each Loop, lets add blackbaud NXT call Get a Constituent after Get a gift.

   2. Constituent ID is a required field and we need to retrieve it from the output of our previous call Get a Gift using body(‘Get_a_gift’)?[‘constituent_id’]

   Now in every iteration of For Each(), we are retrieving gift and constituent information for each gift.

6. Checking the Type of Constituent

   We need Gift and Constituent Information for every gift regardless of the constiuent type.
7. Get Appeal and Package

   We retrieve the Appeal + Package for each gift because this combination determines which letter template we use.To do this safely, we add a Condition that checks whether an Appeal ID exists in each gift. If it does, we pull the appeal_id from Get a Gift

   What is a Condition? And Why is it relevant?

   A Condition in Power Automate is similar to an if/then statement. It evaluates an expression as True or False, often using AND or OR.

   In this flow i have use Conditions to prevent running actions that depend on values that may be null (missing). Without these safeguards, Power Automate could attempt to call a connector with a blank ID (or reference missing data), which can cause the action—and sometimes the entire run—to fail.

8. Resolving overlapping Appeal+Batch Letter Codes with Template Part 1

   The Appeal + Batch Code from a gift determines which letter template to use.

   But in this use case, the relationship was many-to-one. Many different Appeal + Batch combinations needed to map to the same letter template. For example, the “General” letter template had 5+ different Appeal + Batch combinations associated with it.

   And this was a problem because later in the flow, I use a single code to determine which template is generated for each gift, so Appeal + Batch alone wasn’t a reliable “single selector.

   In order to have a single code to indicate what template to use for each gift, I introduced LetterCodes that are 1-to-1 with templates. With each LetterCode having an array of Appeal+Batch codes that share the same LetterCode and template.

   To store this mapping, I created a JSON lookup table (an array of objects) where each object contains a LetterCode and an array of ass ociated Appeal + Batch combinations.

   And for each gift, i scan the appeal+batch code and returned the matching LetterCode.

   Understanding the JSON table below

   This may not look like an Excel Table with the JSON syntax but the pattern represents the same columns and rows of Excel.

   Each {} represent each object or row. In this example, i have 2 objects/rows.

   And each Object has two name/value pairs, “LetterCode” is a name with “Gala” as a value. “AppealPackages” is a name with an array of appeal+batch codes as a value.

   And because we have more than one object, its a list of Objects, whichs needs an outside bracket []

     [
     {
       "LetterCode": "Gala",
       "AppealPackages": [
         "Gala - Gold",
         "Gala - Silver",
         "Gala - Bronze"
       ]
     },
     {
       "LetterCode": "General",
       "AppealPackages": [
         "General Donation",
         "Homepage Online",
         "Mailing End-of-Year",
         "Direct Mail Campaign"
       ]
     }
   ]
   

9. Resolving overlapping Appeal+Batch Letter Codes with Template Part 2
10. Identifying the type of Constituent to provide the correct Header and salutation later
11. Creating Letters, as Adobe PDF's or Word Documents, and Marking Gifts as Acknowledged
12. Emailing Acknowledgement Letters to Donors