Sharing my Power Automate Flow: Anniversary Cards (Reply device and label creation)

Dan Snyder

These flows were largely created by @Ashley Moose as part of receiving the Microsoft Power Platform Community Award so a huge thanks to her for her work on this project.

Summary:

These flows (3) create a reply device and label to send donors a solicitation on the anniversary of their last gift. Some of these can simply be created without review (Flow 1), but for those prospects assigned to a fundraiser, there is a process for those fundraisers to provide approval before sending (Flow 2) and for any approved to be merged into their own files after time for review (Flow 3).

Required Connections:

• Blackbaud Raisers Edge NXT Connector
• Encodian
• SFTP-SSH – to access your hosted files
• Sharepoint
• Word Online (Business) Connection
• Mail Connection (optional: This can be replaced by Outlook or Gmail, I just added Mail so everyone could import the flow should they desire)

Skill Level:

Intermediate/Advanced

Flow Configuration Needs:

Before updating any flows, you will need to create the queries and exports you are using as part of this flow. Below is the output used for these flows. I also chose to create two CSV files, one for the flow of unassigned prospects and one for the flow with assigned prospects. We also have the Queue module, so we were able to schedule the export of the files we need, but it is NOT necessary to use Queue to use these flows.

Update Flow 1 – Reply device and label creation for unassigned prospects

1 - Update the Recurrence and schedule when the flow should occur. The Automated Flow will launch based on this schedule.

2 – Update the Recipient Email and FTP File Path variables for your organization.

3 – Update the SharePoint connection to point to your SharePoint in 3 separate steps

4 - Update the Word connection to point to the location of your label and reply card templates. Templates used for this flow are attached below, but for instructions on creating template letters see this guide.

5 – Update the email steps to reflect the messages you wish to have sent. You can also change to either Outlook or Gmail depending on your organization.

Update Flow 2 – Adding prospects to a SharePoint list for gift officer review

1 – Create a SharePoint List with the following columns. For Approved value, we created a choice column with the options of “Pending”, “Approved”, and “Do Not Send” with the default value being Pending. Please note that if you change any of these values, there will be steps towards the end of the flow that will need to be adjusted.

2 - Update the Recurrence and schedule when the flow should occur. The Automated Flow will launch based on this schedule.

3 – Update the Recipient Email and FTP File Path variables for your organization. The Recipient Email variable is used only if there is nothing to review in this flow.

4 – Update the SharePoint connection to point to your SharePoint in all applicable steps. If you have changed any of the options in step 1 for this flow, pay particular attention to the two “Get Items” actions following the delay.

5 – Update the email steps to reflect the messages you wish to have sent. For this flow, there is a step asking gift officers to review their prospects and one to remind them to review if they have not after 3 days. You can also change to either Outlook or Gmail depending on your organization.

Update Flow 3 – Creation of approved cards and deletion of items from SharePoint List

1 - Update the Recurrence and schedule when the flow should occur. The Automated Flow will launch based on this schedule.

2 – Update the Recipient Email variable for your organization.

3 – Update the SharePoint connection to point to your SharePoint in all relevant steps

4 - Update the Word connection to point to the location of your label and reply card templates.

5 – Update the email steps to reflect the messages you wish to have sent. You can also change to either Outlook or Gmail depending on your organization.

AnniversaryCardsNoReview(Flow1)_20220406132638.zip

AnniversaryCards-SpecialHandlingReview(Flow2)_20220406133918.zip

AnniversaryCardsSpecialHandlingMerge(Flow3)_20220406134947.zip

Anniversary Reply Card Template New.docx

Word Merge Labels Template.docx

Heather McLean

Dan and @Ashley Moose - This is a fantastic outcome from the Community Award handed out at Blackbaud Developers' Conference last June. I'm so excited to see how this type of personalized outreach will impact your donor retention. Please keep us posted as you track results.

There will be more grants available for projects this year. We hope to see you at #bbdevdays!

blackbaud.com/developersconference

Matthew Young

@Dan Snyder
I'm loving these flows. One question--I'm wanting to add some columns to the HTML table in flow 2 that is sent to the solicitor. How do I add another column to this HTML table? I can't figure out how you added the SolicitorSystemRecordID or Title from the array in the previous step, they don't seem to be available in the PowerAutomate field selection pane

Alex Wong

@Matthew Young
there are a few ways you can do this, some easier than others depending on how familiar you are with JSON data object in Power Automate.

basically in order to add more columns those column must first exist in the JSON Array you pass into the create HTML table action. You can either use the “addProperty” expression or create a new array that has all the properties you want.

Austen Brown

@Matthew Young - try using an expression “items()” with a key “?['x']” tacked onto the end. You'll need: the output name of the field you're looking to include (look at a recent run of the flow to locate this), and the name of the apply to each. On the right side of the able add an expression “items('Apply_to_each_-_Select_each_item_per_unique_solicitor')?['x']” - replace the "x" with the name of the field to include; on the left side, include the header you want in the table.

When you first put this in, it will appear as an expression, but will change appearance to resemble the other entries above.

Dan Snyder

@Matthew Young to add to what @Austen Brown mentioned, the “Title” option has the expression of item()?['Title'] entered and then will change to looking like it is a selection once you save your flow.

Also, the part of the flow that you will want to look at is the Filter array step where you can see the outputs with all the field names.

Matthew Young

Thanks @Dan Snyder @Austen Brown @Alex Wong .

I was able to use the item()?['x'] expression in my create HTML table action to reference the specific fields in the array from the previous step.

I am having trouble referencing the below “Value” item of the below “Actionneeded” object in my array field.

item()?['Actionneeded'] won't work since there's 3 items within Actionneeded. Any ideas? I think it created the 3 items in the object since this is a drop down field on my SharePoint list it's pulling from.

"Actionneeded": {

"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

"Id": 0,

"Value": "1 week thank you card send"

},

Alex Wong

@Matthew Young
item()?['Actionneeded/Value']

or

item()?['Actionneeded']?['Value']

Matthew Young

@Alex Wong that worked, thanks for helping a newbie get going!

Andrew Peterson

@Dan Snyder not sure if anyone is still looking at this thread, but I was curious if this could be modified to instead of prepping physical cards, send digital cards. The idea being, a digest is sent to a team member daily that contains the LYBUNT folks and a link that starts a new email using the mailto html properties. Is something like that possible?

I've basically been reading every post in this forum since DevDays and I really appreciate all the knowledge that is being shared.

Dan Snyder

@Andrew Peterson While I have not done anything like that, I suspect it could be possible. Come back and share with us when you get it working!

Erik Leaver

@Andrew Peterson Maybe a project for the hackathon?