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

1. Update the schedule to suit your organization's needs
   • This flow is set to run every weekday at 4 pm.
2. Update the Operations Team emails with the email address for who reviews/prints acknowledgements at your organization.
3. 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.
4. 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')
5. Populate a Microsoft Word template
   • Update the location and document library paths
6. Create Letter Files
   • Update the ShareSite Address and Folder Path where you want the files saved.
7. Populate a Microsoft Word template Envelope
   • Update the location and document library paths
8. Create envelopes file
   • Update the ShareSite Address and Folder Path where you want the files saved
9. 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.

Dan Snyder

@Hallie Guiseppe Great job!

Hallie Guiseppe

@Dan Snyder Thank you for your help!!

Patricia Rataphan

@Hallie Guiseppe at step 4, when updating the “Compose Letter Variable”, where are you getting your exact file path to your letter templates? My acknowledgement letter templates are on Sharepoint.

(Please forgive me, I am new to using Power Automate!)

Hallie Guiseppe

@Patricia Rataphan

You will create an expression using the formula concat('/Automated Acknowledgements/', outputs('Get_a_Gift')?['body']?['acknowledgements'][0]?['letter'],'.docx')

and replace the highlighted text with the name of the folder where you have our templates saved.

Rebekah Singleton

@Hallie Guiseppe hi Hallie! I am trying to implement this flow but am getting stuck with the Word template. A few questions:

1. first, in your “dynamicFileScheme”, where is the number you have at the top coming from?

• second, what's the difference between the dynamic content/fields you include in the dynamicFileSchema and those you have in the following step, the EnvelopeArray?
  

Hallie Guiseppe

@Rebekah Singleton
Hi Rebekah,

Thanks for your questions! Full disclosure: this flow is a compilation of aspects from many other people's work that I have been exposed to in various different ways, showcase templates, BBDevDays, User Group meetings, Community boards, etc. Also, this is the very first flow I developed where I added my own content, so it is very possible I did things that are more complicated than needed because I didn't know any other way. ?

In this flow, I combined a couple of different templates from other acknowledgment flows available in the template showcase. Then I added the pieces I needed, like receipting, organization contacts (credit Dan Snyder), and so on.

I adopted the dynamicFileScheme from Matt Thacker's Conditional merge flow published in June 2022. Thanks to Heather and Erik for tracking down the recording, where he discusses the details of creating a dynamic file location; you can view that video at THIS LINK.

As for your question about the EnvelopeArray, I'm not sure I understand exactly what you are asking. This flow will create two files for each gift: one for the letter, based on the letter code on the gift, and another for the envelope. While it may be possible to generate the envelope file without using the variable array, that is how I learned to do it. I welcome any suggestions to improve the flow that you might have.

Thanks again for your questions!

Hallie

Rebekah Singleton

@Hallie Guiseppe Thank you SO MUCH Hallie! I spent the afternoon working through all of this and finally have my acknowledgment letters ?
Thank you to Heather and Erik for helping, too, and to Matt for sharing his initial flow. This community is great.

As for the second question, that is my bad for misunderstanding – I did not process that the EnvelopeArray was for… the envelopes LOL.

Thank you again!

Molly Glowacki

@Hallie Guiseppe thank you for sharing! I am attempting to use this flow, but I receive an import error. My word (online) business connector is connected…wondering if you've run into this error before?

Hallie Guiseppe

@Molly Glowacki
I have not had this exact error. However, I recently copied a flow I had in one environment over to use in a different environment and had issues with the import. However, when I closed out of the error, the import worked and I was able to go in and edit the flow. Maybe something similar happened for you? Do you see the import listed in your flows?

Molly Glowacki

@Hallie Guiseppe thanks for your quick reply! I think I'm overlooking or missing the option to continue to edit the import flow, despite receiving an error. If I figure it out, I'll report back here!

Alex Wong

@Molly Glowacki
After you import and failed, you should see

Click on “Save as a new flow” will open the flow in a new window for you to fix the various issue you may have (word template file missing, etc).

Molly Glowacki

@Alex Wong Hello! Thank you for your response. Unfortunately, I do not have that third bullet point with the option to Save as a new flow.

Ashley Moose

@Molly Glowacki Try importing using this file instead - PrintAcknowledgementMailMergeforBBCommunityv2_20240209141005.zip

I imported the original one and received a different, but similar error.

To get past the error, I setup a temporary compose action and used the output of that action as my file name.

If this import works for you, you can then delete this Compose file name action and update the File field to reference your document library and file location (as noted in Hallie's instructions).

Molly Glowacki

@Ashley Moose, thank you! The import you provided worked and I was able to update the File field. Appreciate the help from the community!

Mark Kosier

@Hallie Guiseppe
Thank you for all the work on this flow. I know it takes a team effort to get to this point so thank you to all that were involved.

I have a question. Our campus has decided to turn off Sharepoint. When would be able to use Onedrive or some other cloud provider (Box) to save the files?

Alex Wong

@Mark Kosier
power automate has support to save to OneDrive and Box, there are a lot of support for many well known cloud file platforms.

Hallie Guiseppe

@Mark Kosier
It is good to hear you found value in this flow, it truly is a collaborative effort!

I don't see why you couldn't set this up to use OneDrive rather than ShareSite. I chose that because it is easier to grant permissions. With OneDrive you may have to individually grant permissions for your team who is actually creating, editing, printing and signing the letters and envelopes.

Good luck!

Alex Wong

@Hallie Guiseppe
there's power automate action that allow you to create “share link” providing access to OneDrive of someone to someone else that do not have access.

Mark Kosier

@Hallie Guiseppe
@Alex Wong
Hello and thank you for the replies to my earlier questions. I have an additional question. I recently contacted our central IT team responsible for managing our Power Automate licensing, however, they were unable to provide a definitive answer.

I am seeking clarity on which team members necessitate a premium Power Automate license to execute premium flows. Specifically, I am inquiring whether solely the development and data teams involved in setting up the flows require licensing, or if frontline fundraisers and members of the executive team who will utilize the generated reports also require premium licenses.

Thank you for your attention to this

Alex Wong

@Mark Kosier
You only need the one that is creating the flow to have the “per flow” upgraded license.

Mark Kosier

@Alex Wong
thank you for the reply. Take care.

Hallie Guiseppe

Hi @Mark Kosier,
Alex is correct; you only need one premium license to build flows. I recommend assigning the license to an organization account instead of your personal email. Set up this organization account as a user in RENXT and use it to connect to your NXT environment. This ensures that the Power Automate flows remain intact for the organization if you ever decide to leave.

Erik Leaver

@Mark Kosier There is a session in bbdevdays tomorrow at 10am ET - “Nonprofit Licensing for Microsoft Power Platform” that might be of interest!

Dan Snyder

@Hallie Guiseppe:

Hi @Mark Kosier,
Alex is correct; you only need one premium license to build flows. I recommend assigning the license to an organization account instead of your personal email. Set up this organization account as a user in RENXT and use it to connect to your NXT environment. This ensures that the Power Automate flows remain intact for the organization if you ever decide to leave.

What @Hallie Guiseppe mentioned here is really important for longevity of your automations, make sure it is an organization account vs. your personal work account.

Mark Kosier

@Dan Snyder thank you for the reply. I spoke with the team about this. They had a question around 2FA. We have 3 developers who will be working on building the flows. In this situation do you still recommend using one organization account that we all share the login so we can build the flows under that org account? How do you handle 2FA if many users are sharing that same org account?

Dan Snyder

@Mark Kosier What you should be able to do is make your organization account the owner of the flows, then you can invite others as co-owners.

Mark Kosier

@Dan Snyder ohhhhhh. I gotcha now!! I am a noob at this so I apologize for knowing so little about power platform. Thank you ?

Kevin Fletcher

@Hallie Guiseppe:
Alex is correct; you only need one premium license to build flows. I recommend assigning the license to an organization account instead of your personal email. Set up this organization account as a user in RENXT and use it to connect to your NXT environment. This ensures that the Power Automate flows remain intact for the organization if you ever decide to leave.

For @Hallie Guiseppe and @Alex Wong (and anyone else who can chime in), I'm working to document the handful of flows I've already created so that they can outlive my time at my org, however long that is (I have no plans to leave!).

Currently, my personal work account (ie. MyName@MyOrg.org) is the owner of the flows, but I'm wanting to figure out how to get an Org account set up instead per Hallie's note above. This is where both I and my IT team are a little over our ski tips, since I don't know Microsoft account setup and they don't know MPA or RE. [Advancement Services has always been on a bit of an island at our org when it comes to local IT support; they just ask us to work with Blackbaud. Now that I'm bringing MPA into the fold and a lot of my issues are now Microsoft- rather than BB-based, I'm needing them to handle some of the support.]

So my question is, what sort of terminology do I need to use with my IT folks to request/set up an org account that could serve as the master owner of these flows? We have email addresses (such as OurDept@MyOrg.org) that serve as aliases that simply forward individual copies of messages to everyone that is in that distribution list, then replies come directly from those individuals (i.e. from MyName@MyOrg.org and NOT from OurDept@MyOrg.org). I'm presuming that an org account would be one where we are logging in AS OurDept@MyOrg.org and would be identified as that entity? And IT probably can't convert OurDept@MyOrg.org to an org account, and would instead need to create a new OurDeptMPAaccount@MyOrg.org account to serve as the owner of these flows?

Hallie Guiseppe

@Kevin Fletcher
Hi Kevin,

While not an expert and certainly don't have the terminology down I can tell you that the account must be able to be set up with the Power Automate Pro license and I also set up an RENXT account for the organization email account. This allows me to configure all the connections in my flows using the organization account rather than my own. It has also proven to be beneficial as I am starting to use add-ins and I set up my apps using the organization log in, not my personal email account. In my outlook or IT team has granted me permissions to view both my inbox and my org inbox so I can toggle between the two, but when I'm setting things up I do log in as the organization account.

I hope that answers at least some of your question. ?

Alex Wong

@Kevin Fletcher
have a “automator” account in developer.blackbaud.com with highest level of scope/access that is allowed

this account also should have rights to send on behalf of all users in your tenancy. (this can be abused and consider abusable by your IT team, so may need to control this better by either using a separate service account and limit login access or other security control deem needed by your IT team)

this account should be owner to all flows related to blackbaud automation and is given the flow per user license and potential addon for more capacity, flow can be “shared” to other team member that may do editing.

Not too much more suggestion and also doesn't know if it is best practice or not.

@Erik Leaver @Heather McLean maybe there should be a blog post from Blackbaud on recommendation specifically relating to this.