Sharing my Power Automate Flow: Department Stewardship with Soft Credits

Dan Snyder

Summary:

These flows will allow you to notify individuals of specific gifts to their department, create a template acknowledgement letter, create an open action, create a spreadsheet to track what has been sent which will also allow for the closure of the action. This flow attempts to account for soft credits.

Required Connections:

• Blackbaud Raisers Edge NXT Connector
• OneDrive
• Excel Online (Business) Connection
• 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

Flow Configuration Needs:

Update Flow 1 - Notification Email and Letter Creation.

Note SC stands for soft credit and HC for hard credit

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

2 - You may choose to update the Get Past Time action if this flow will be running at a different interval than daily.

3-8 - Initializing variables to be used later.

9- For the “Pull yesterday gifts entered” step (List gifts) - either use an NXT list or adjust the gifts types, fund system record ids, gift amount, or any other fields to customize to your needs.

10 - Condition to check for number of gifts. If 0 the flow ends, if 1 or more the flow continues.

11 - For each gift we get the gift details

12 - Get the Donor Name

13 - Get the Fund Name

14 - Check for the Presence of a soft credit

15 - Check for a fundraiser on the gift record

16 - Format the gift amount to include the $ sign and decimals

PARRALEL BRANCH - At this point the letter creation branches to the right and the notification email branches to the left.

Letter Creation

1 - Within the “for each gift returned from yesterdays gifts” loop, there is an “SC and Org” condition. This condition looks to see if there is a soft credit present on the gift and if the hard credit donor is an Organization record. The logic here is that if someone gives via a DAF or Family Foundation (organization record), the individual soft credited should get the TY letter, not the DAF or Family Foundation. These are not tax receipts, just a thank you not from a department head.

CREATE WORD TEMPLATE - Create template letter in Word Online and save to OneDrive (this could be SharePoint if you prefer). Again, since this is not a tax receipt, you can use the same letter and for instructions on creating a template letter see this guide.

2 - Update the SC: Populate Template and HC: Populate Template actions (Populate a Microsoft Word template) to point to the specific Word template files you created.

3 - Update the file location and file name information in the SC: Word Doc and HC: Word Doc actions. This is a Create a file action for OneDrive.

4 - Update the SC: Action Entry and HC: Action Entry (Create a constituent action) steps to meet your needs. IMPORTANT: To assign the action to a fundraiser, you will need to show advanced options and enter the fundraiser's system record id.

Create an Excel document with a table and the following headers (Action ID, Donor Name, Gift Date, Letter Sent, and Send Method) and save it to OneDrive (again, you could use SharePoint). For the Letter Sent and Send Method columns, I added data validation so only Yes or Do Not Send could be selected in the Letter Sent column and Email and Mailing are the only options in the Send Method Column.

5 - Update the SC: Add to Tracker Sheet and HC: Add to Tracker Sheet actions to point to the Excel file you have created. You will have to select the specific table and then map the first 3 fields. Leave Letter Sent and Send Method blank as they will be selected by the department.

Notification Email

1 - The Donor Name and Amount action (Append to array variable) includes Donor Name (links to donor in NXT), Gift Type, Amount (links to gift in NXT), Fund Description, Soft Credit, and Prospect Manager or Fundraiser. These are the fields that will be included in the notification email, so if you wish to have different fields they would need to be added here.

2-6 - Clearing out the variables for the next gift to run through the flow.

7 - Get the array from step 1 of the Notification email process into HTML.

8 - Clean up the HTML table so the links to the constituent and gift records work properly.

9 - In the “Send and email notification (V3)” step, you can update any of the fields or the text of the email which I have including links to the Word and Excel documents from the letter creation branch. You can also insert the Outlook or Gmail connectors here.

Update Flow 2 - Action Closure and Row Removal

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

2 - List the rows from the table created in Letter Creation step 5.

3 - There is a condition to ensure that the Letter Sent column is not blank, it is written as the Letter Sent field is either Yes or Do Not Send. These can be changed based on your needs.

3 - If one of those values is present in the Letter Sent field, there is a Switch Function based on the value.

a. If the value is Yes, the action created in Letter Creation step 4 is updated to be completed and the value from the Send Method column is the category. That row is also deleted from the Excel document.

b. If the value is Do Not Send, the action created in Letter Creation step 4 is updated to be completed and the notes are updated to reflect that the letter was not sent per departmental decision. That row is also deleted from the Excel document.

DepartmentalStewardship-NotificationandLetterCreation_20220109221037.zip

DepartmentStewardship-ActionClosureandRowDeletion_20220109221836.zip