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. Filter Constituent Codes for Foundation

   For organization records, a foundation can be identified by the Constituent Code “Foundation.” To support this, we need to retrieve constituent codes for each applicable record. To improve efficiency, we should only run List Constituent Codes when the Hard Credit constituent is an Organization.

   Because foundation gifts do not use soft credits, we cannot rely on that logic to determine who the acknowledgment should be addressed to. Instead, we use the organization’s relationships to identify the appropriate contact person for the header and salutation. For foundation records, the relationship data determines who the letter is written to and when organization relationship logic should be used.

   1. Create a condition called IfOrg

   2. Add the action “List Constituent Codes” and use Constituent ID as input

   3. Create a Filter Array action.

   Why we cant filter the output of List Constituent Codes with Foundation and need to use Filter Array Action to do so
7. Checking the Type of Constituent

   We use several Compose actions as flags to determine what information must be retrieved and how the letter header should be constructed.

   These flags identify:

   • Whether the hard credit is an organization
   • Whether the gift includes a soft credit
   • Whether the hard or soft credited constituent uses a home or business address
   • Whether the hard credit is a foundation

   8. Create a Compose to check if Gift has a Soft Credit and call it CheckIfSoftCredit. This Compose returns true when the gift includes at least one soft credit and false otherwise

   Pseudocode:

   If Soft Credit is not empty
     return true
   Else 
     return false
   

   Code for Determining if Gift has a Soft Credit

   not(empty(body('Get_a_gift')?['soft_credits']))

   2. Create compose to check if soft credit of gift has a prefered address of home or buisness. Call it IndividualHomeOrBusinessAddress

   Pseudocode:

   If Constituent CheckIfSoftCredit is true
     If address is type Home and preferred is true
       return 'home'
     Else If Preferred address is type business and preferred is true
       return 'business'
     Else return ''
   Else 
     return ''
   

   Code for Determining if a soft credits preferred address is home or business

   if(
     outputs('CheckIfSoftCredit'),
     if(
       and(
         equals(toLower(coalesce(outputs('Get_a_constituent')?['body/address']?['type'], '')), 'home'),
         equals(coalesce(outputs('Get_a_constituent')?['body/address']?['preferred'], false), true)
       ),
       'Home',
       if(
         and(
           equals(toLower(coalesce(outputs('Get_a_constituent')?['body/address']?['type'], '')), 'business'),
           equals(coalesce(outputs('Get_a_constituent')?['body/address']?['preferred'], false), true)
         ),
         'Business',
         ''
       )
     ),
     ''
   )
   

   3. Create compose to check if hard credit of gift has a prefered address of home or business. Call it IndividualHomeOrBusinessAddress

   Pseudocode:

   If Constituent is individual
     If address is type Home and preferred is true
       return 'home'
     Else If Preferred address is type business and preferred is true
       return 'business'
     Else return ''
   Else 
     return ''
   

   Code for Determining if a hard credits preferred address is home or business

   if(
     outputs('CheckIfSoftCredit'),
     if(
       and(
         equals(toLower(coalesce(outputs('Get_a_constituent')?['body/address']?['type'], '')), 'home'),
         equals(coalesce(outputs('Get_a_constituent')?['body/address']?['preferred'], false), true)
       ),
       'Home',
       if(
         and(
           equals(toLower(coalesce(outputs('Get_a_constituent')?['body/address']?['type'], '')), 'business'),
           equals(coalesce(outputs('Get_a_constituent')?['body/address']?['preferred'], false), true)
         ),
         'Business',
         ''
       )      
     ), 
     ''
   )   
   

   4. Add the Action “List constituent codes”, With Constituent ID as the input. This organization uses Constituent Codes to determine if an organization record is a Foundation, this is how we will retrieve that information. We then add a compose action to check if hard credit of gift is a Foundation. Call it CheckIfFoundation.
8. Retrieving Soft Credit Information

   If the Flag checks for Soft Credit returns True,

   We first check whether the gift includes a soft credit. If a soft credit exists, the acknowledgment letter should be addressed to the soft credit individual rather than the hard credit donor. We retrieve the title, first name, and last name of the soft credit individual so the correct addressee can be constructed in the letter header.

   1. Check if gift includes a soft credit We add a condition action that checks if Soft Credit is Present in
9. 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.

10. Resolving overlapping Appeal+Package Letter Codes with Template Part 1

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

    But in this use case, the relationship was many-to-one. Many different Appeal + Package combinations needed to map to the same letter template. For example, the “General” letter template had 5+ different Appeal + Package 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 + Package 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+Package 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 + Package combinations.

    And for each gift, i scan the appeal+Package 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+Package 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"
        ]
      }
    ]
    

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