nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sharing my Power Automate template: Conditional Acknowledgments with pledge and primary contact information

Hallie Guiseppe

Description: automated conditional Word mail merge letters and envelopes saved to SharePoint folder which include pledge information and primary contacts for organizations. An email notification goes out when files are ready for review and printing.

Required connections: Blackbaud Raiser's Edge NXT, Microsoft Power Automate, Word, SharePoint, and Outlook.
Suggested skill level required to use this template: Intermediate
Configuration details:
Category: Acknowledgement
Instructions:

Set up the letter templates

Step 1: Download the templates
- Template Files: Acknowledgement templates.zip
Step 2: Open the templates in the desktop version of Word and "Save as" the Letter Code you use on the gift record. (The file's name must match exactly the letter code you use on your gift record).
Step 3: update the letter's header, body, and footer. Reference THIS article if you have questions about adding or removing fields.
Step 4: save the file to the SharePoint location you plan to use in your process. (Tip: It may be helpful for you to create your templates with your organization information so that you don't have to update that each time you create a new letter type.)

Download the flow:

Visit Getting Started in the Blackbaud community if you need support.
- Power Automate Flow: ConditionalAcknowledgementLetter&EnvelopeWordMerge_20230323152742.zip

Set up the Flow

Update the schedule to suit your organization's needs
- This flow is set to run every weekday at 4 pm.
Update the Operations Team emails with the email address for who reviews/prints acknowledgements at your organization.
List gifts
- Link the list to the dynamic gift list you created in RENXT.
  - Note: I do not account for recurring gifts or payments in this flow. Our organization doesn't print acknowledgments for recurring gift payments.
Compose Letter Variable
- update the formula in the "Compose Letter Variable" action to direct the flow to the file path where your templates are saved. My folder is called "Automated Acknowledgements."
  - concat('/Automated Acknowledgements/', outputs('Get_a_Gift')?['body']?['acknowledgements'][0]?['letter'],'.docx')
Populate a Microsoft Word template
- Update the location and document library paths
Create Letter Files
- Update the ShareSite Address and Folder Path where you want the files saved.
Populate a Microsoft Word template Envelope
- Update the location and document library paths
Create envelopes file
- Update the ShareSite Address and Folder Path where you want the files saved
Notify Ops team
- Update email copy and file path link in both notifications
- Disable static results for both email actions.

Note about the "If there is a Linked Gift" action: I do not account for recurring gifts or payments in this flow. Our organization doesn't print acknowledgments for recurring gift payments. If you need to account for recurring gift payments, you can add a control step here to look at gift type and output information related to recurring gifts.

Note about the "Encode Constituent Name to replace invalid characters" action: This allows the file to save if you have non-alphanumeric characters in your constituent names, such as Jr. or Ben & Jerry's. If the flow fails at this action step, you may have a character I have not accounted for.

Find more posts tagged with

Microsoft Power Platform

Comments

Mark Kosier

@Hallie Guiseppe

Hello Hallie, we finally got our campus to purchase the premium power automate licenses so we can now run premium flows. I ran into a small issue I was hoping someone could help me with. I imported your flow into power automate. I was able to connect all the connects but 1. The one connector I cannot locate is the Blackbaud Raisers Edge NXT Gifts connector.

Do you know if this connector still exists?

a7ad26f45228edc56ed7a911cb40cd9c-huge-im

Mark Kosier

@Mark Kosier
@Hallie Guiseppe
@Alex Wong

I found it! I was using the wrong search box. When on the connection screen you have to use the search box on the far right. The one at the top did not locate the Gifts connection.

19592bad2b601a4dba497f4257c1b8ac-huge-im

Hallie Guiseppe

@Mark Kosier
I do that almost every time! ?

Mark Kosier

@Alex Wong Thank you so much for everything you do for this group. I imported the flow and attempted to switch from SharePoint; however, our campus doesn’t have Central IT support for SharePoint, so we need to explore other options. I’ve been trying to modify the flow to use either Box or OneDrive, but I’ve encountered difficulties connecting them to the correct folder paths. It's likely a small detail I'm overlooking.

I've spent the last six weeks working on this and haven't been able to resolve the issue. I understand how valuable your time is, and I greatly appreciate any guidance or assistance you can provide.

Alex Wong

@Mark Kosier
when you say encountered difficulties, going to need more info on what difficulties you are talking about. are you not able to save b/c PA detected error in your flow? or are you able to save and get the flow to test/run, but got error in one of the action? Please provide screenshot of problems.

Kevin Fletcher

@Erik Leaver @Heather McLean Wondering if either of you have come up with a best-practices blog post about this (forgive me if I've missed it!)?

Mark Kosier

f274aa5380fffe9ca2d3ac5b40acedd9-huge-zo

@Alex Wong thank you for the help today. I have another question about editing the words templates. When editing ack letter word templates I know how to create the bounding boxes around each data field. But how do you create the larger Letter bounding box? Like in this example?

Here is our standard receipt template:

Standard.docx

Alex Wong

@Mark Kosier
not sure what you mean by “larger letter bounding box”.

your example (if you are referencing the screenshot) has the Content Control for GiftLookupID, but that is just 1 “short” piece of info to add to the letter.

by “larger” do you mean for more info to be added by power automate?

Mark Kosier

@Hallie Guiseppe and @Alex Wong thank you for all your help with this flow. I have one more issue that I have not been able to figure out. Forgive me for being a Power Automate noob.

In this flow there is a step called “Populate a Microsoft Word Template” I do not see in your flows step that you have a entity for “Payment method” I am trying to edit your flow to work with our receipt templates so we can pull in the payment method. I am not able to find the “payment method” entity so I can pull in the payment method of the gifts.

Do you know what I would need to modify to be able to pull in payment method into our receipt? If this is to complex to work out on this forum I can ask Erik to put it on the agenda for the next user group meeting..

Hallie Guiseppe

@Mark Kosier
You will need to do a couple of things to get the payment method in the letter. First you need to find the end point in the “get a gift” step. You don't need a compose action, but I find it helps me when I'm leaning to use them. I think the endpoint is called “payment method” and appears to be in the “payments” endpoint. See the API Reference HERE.

Additionally, you will need to add the field to the word template. You can learn how to do that HERE.

If you get stuck, the user group meeting is a great place to get help!

Alex Wong

@Mark Kosier
As mentinoed by Hallie:

Update word template first, then…

5ef88aab6994769acc1cc29b83d4ec96-huge-im

In the flow, if you search for dynamic content “payment method” you will find 2 of them, one from List gifts action and one from Get a gift action.
- this is generally why I do not like another Get a gift action after List gifts has been used already, as MOST of the time, List gifts action already have all the needed properties of a gift (there are a few exception only)
If you directly try to use either of these dynamic content, flow WILL put the action you try to put this dynamic content in into an Apply to Each. This is due to payments properties of a gift is an Array (this is the “oddity” of Blackbaud SKY API decision here as to why it is an array, just like acknowledgement, which we all know is a ONE-to-ONE relationship not a ONE-to-MANY relationship), but it is what it is, so you need to deal with this
Therefore, knowing there is really ONLY one “element/item” in the array of payments, you can use the “first()” expression to get the first “element/item” in the array of payments, and then get the properties of payment_method from it.
- first(outputs('Get_a_gift')?['body/payments'])?['payment_method']
  - this expression will use the payments from Get a gift action
- first(items('Apply_to_each')?['payments'])?['payment_method']
  - this expression will use the payments property in the List gifts action that is already in Apply to each (that loops through each gift).

Mark Kosier

@Alex Wong thank you soooo much for this explanation. I Will give this a shot and let you know how it goes on the next user group meeting.. Have a great week!!

Mark Kosier

@Alex Wong thank you so much for this info. With your knowledge I was able to get the payment info pulled into the receipt template. The only field I have left is the ‘Fund Type’. I need to be able to get to the fund type so I can pull that info into the receipt. I have been scouring the API documentation page to try and figure out how to get to the fund type but have not found an answer yet. The screenshot shows the fund type in NXT I am trying to get to. For fund type we only have two choices, Regents or UC Foundation

b22bab7ccfbf10d5420e8555632781a4-huge-fu

Do you or @Hallie Guiseppe know which endpoint/step to connect to get to the Fund Type field?

Again I want to thank you both for taking time out of your busy days to help me out. I really do appreciate yall!!! Thank you and keep on rockin!

Hallie Guiseppe

@Mark Kosier
You can get at the endpoint for fund type using the Fundraising/Fund list endpoints. I believe that we already call that endpoint in the flow to get the fund description. I'm headed to a meeting, but thought I'd drop this in so you can keep moving on it.

I'll check back later with more detail unless @Alex Wong beats me to it.

Alex Wong

@Mark Kosier
a gift can have 1 split of campaign,fund,appeal,package (CFAP) or multiple. So the question is, does your org allow split gift or not, if yes, then you have to deal with potential multiple fund.

THis is a template I shared that does the appeal info, the same idea can be used to get fund info, the get a fund endpoint has dynamic content for fund type.

Hallie Guiseppe

@Mark Kosier

Hi Mark,

I popped into the flow I have running and found that I was able to compose the fund type pretty easily in the action where I'm pulling in the fund description. See the image below:

f55a9c15bd7236b9502d4efa96908b3d-huge-im

You may want to add a new variable similar to the one for fund description and then add the fund type to the new variable you create.

Mark Kosier

@Hallie Guiseppe thank you for this info!!! I was able to get this step working.

Mark Kosier

@Alex Wong once again thank you so much for sharing your knowledge. I was able to get the payment method into the receipt using your expression you shared. The issue is on the receipt it shows it as ‘BusinessCheck’ without a space.

Do you think the expression that I entered is causing it to remove the space?

1b0b1bb6af9e8f7403c1138fd3736476-huge-im

This is what the receipt looks like:

2348ac3341adea4035699207003d22e2-huge-im

Alex Wong

@Mark Kosier
Nothing to do with the expression, this is just how SKY API returns the information:

Type	Description
Cash	Cash.
PersonalCheck	A personal check.
CreditCard	A credit card.
StandingOrder	A standing order.
DirectDebit	Direct debit.
Voucher	A voucher.
Other	Another method of payment.
PayPal	PayPal
Venmo	Venmo
BusinessCheck	A business check.

So if you really want to make it more human readable, you will need additional expression to replace the “combined” word into separated word.

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#replace

replace(replace(replace(replace(replace(first(outputs('Get_a_gift')?['body/payments'])?['payment_method'], 'PersonalCheck', 'Personal Check'), 'BusinessCheck', 'Business Check'), 'CreditCard', 'Credit Card'), 'DirectDebit', 'Direct Debit'), 'StandingOrder', 'Standing Order')

Mark Kosier

@Alex Wong thank you so much for sharing your knowledge. I Will give this and shot and let you know the outcome.

Eileen Wallwork

@Hallie Guiseppe Hi, I know you posted this a while ago so I hope you are still available to answer questions. My run keeps failing on the Populate a Microsoft Word template stage. It is saying the selected file does not contain template elements. I am using one of your acknowledgement letters that you provided but ‘saved as’ to match the letter code in our system. I verified that the template is using plain text and not rich text. Do you know if there is something else that could be causing this error message? Thank you! Eileen

Hallie Guiseppe

@Eileen Wallwork
Hi Eileen,

I would need to look at the flow error. Can you please post a screenshot? I will try to take a look tomorrow.

Eileen Wallwork

@Hallie Guiseppe

Hi Hallie, thank you so much for taking the time to look into this! Here is a copy of the error. For ease, I saved the templates in a document folder in Sharepoint called Automated Acknowledgements. Thank you, Eileen

272e2978f01377ba6baeecb30a855696-huge-im

Hallie Guiseppe

@Eileen Wallwork
It is difficult to tell why your getting this error. I'd try looking at the Compose Letter Variable first to be sure that is correct since that is what is being used in the input for the Populate a Microsoft Word template action. take a look at the expression:

concat('/Automated Acknowledgements/', outputs('Get_a_Gift')?['body']?['acknowledgements'][0]?['letter'],'.docx')

Your folder path may be different that what is in this expression. My folder name is highlighted in yellow.

If that is correct, make sure your location and document library in the Populate a Microsoft Word template is where your folder lives.

0346456750caa29c1a17088a2c10d067-huge-im — check the location & document library is pointing to where your template folder is located.

Try those steps and let me know if you are still encountering a problem.

Eileen Wallwork

@Hallie Guiseppe
Hi Hallie,

I was able to get it to work! I moved the letters to folders that already existed. So it seems PowerAutomate could not connect to the new folders I created. I'll have to work with IT about getting the connections established. Thank you for your help, I really appreciate it! Eileen

Kelly Hogan

@Hallie Guiseppe or anyone else who might have insight, I feel like this is the silliest thing to get stuck on. I am having a hard time updating the info in the Populate a Microsoft Word template Envelope.

Alex Wong

Document Library field looks wrong, "X" it and select from list instead.

Kelly Hogan

Thanks @Alex Wong, definitely figured that out after fiddling with it forever!

Hallie Guiseppe

@Kelly Hogan Did clearing out the path and selecting it from the drop down fix the issue you were having?

Kelly Hogan

@Hallie Guiseppe , it sure did!

Now I am stuck on the error about the file not having template elements. Fiddling my way through that now! 😊