Sharing my Power Automate Flow: Daily Gift Digest Notification

Trevor Kelly

Summary

This Flow is triggered every night. It runs a gift list from RE NXT and then builds out a consolidated table to be emailed to an inbox showing details about each gift. Additional HTML elements were added so that when the donor's name is clicked, it opens up the constituent record. If the gift amount is clicked, it opens the specific gift record. There is also a hyperlink above the table that can be updated to direct the notification recipient directly to the gift list in RE NXT. Additional fields can be added to the table as needed (note that additional variables may be necessary to output split information such as funds, appeals, etc).

Required Connections

Outlook (organizations using a different email provider can replace with the Mail connector)

RE NXT

Suggested Skill Level

Beginner

Configuration Needs

After importing the Flow and authorizing the Connections, you will need to update the email address in the 2nd step, update the RE NXT list in the 4th step, and update the link to the gift list the email body in the very last step. Feel free to add additional data into the table by manipulating the Append to Array Variable function that was renamed to "Apply gift and donor content to our variable".

GiftDigestNotificationEmail_20210601173537.zip

Trevor Kelly

Here is the recording that walks through how this was built: Gift Digest Notification.mp4

Eileen Wallwork

Hi Trevor, Thank you so much for sharing this. I recieved an error when adding the replace code to 'Clean up table'.


Error message: Correct to include a valid reference to 'Create_HTMLtable" for the input parameter(s) of action 'Cleaned_up_table'.


This is what I used for the replace code: replace(replace(body('Create_HTMLtable'),'&lt;','<'),'&gt;','>')


Can you tell me if there is an error with this code?


Thank you! Eileen

Trevor Kelly

Hi Eileen! It appears that formula to clean up the HTML code failed when attempting to reference the step above where the HTML table is created. The step where the HTML table was created may have been renamed, which could be causing the formula to not recognize the referenced step. If you want to send me the .zip file of your flow (trevor.kelly@blackbaud.com), I can look at it and try to figure out why it is failing.


Cheers,

Trevor

Carlene Johnson

Trevor, this tutorial is FANTASTIC!! Thanks for sharing! I'm wondering if you, or others, have suggestions on how I could add the Solicitor from the Constituent record (not the gift record) to this digest?

Almost everything we report on here will need to have the assigned solicitor on it so I better learn now how to do that!

Otherwise, my gift digest is totally beautiful and working perfectly. Couldn't be more pleased with myself or your instruction. ?

Trevor Kelly

For non-Outlook users, here's a version of this that uses a generic mail connector.GiftDigestNotification-GenericEmail_20211109190644.zip

Fran Tucker

Trevor, Are there additional training session available? Could you direct me towards either BB-sponsored Power Automate training or other resources? Thank you

Austen Brown

Hi Fran - There is an on-demand course available along with lots of helpful resources at https://docs.blackbaud.com/microsoft-connectors-docs/microsoft-power-platform.

Fran Tucker

Thanks Austen. The links seems to be for BB Employees only

Austen Brown

Hi Fran - Sorry about, you can use this link instead:

MaryKathryn Odean

Hi Trevor,

Thanks so much for sharing your flow and the really helpful video. I was able to recreate a similar flow using an NXT gift list, to later learn that our team is running a few weeks behind on gift entry and an NXT gift list that pulls on “gift date” won't work for us.

I tried creating a similar flow for a Weekly Gift Digest that filters on date added (on or after 1 week ago), campaign, and fund. When I test the flow I get two errors (see below) and the results eventually tell me that the filters I've applied (both the Get Past Date filter and the Campaign/Fund filters) aren't working. Would you mind taking a look at my flow to see where I am going wrong?

[{"message":"Invalid filter Id format.","error_name":"InvalidFilterIdFormat","error_code":15,"raw_message":"Invalid filter Id format."}]

Unable to process template language expressions for action 'Condition:_There_is_1_or_more_Gifts_who_meet_criteria' at line '0' and column '0': 'The template language function 'greaterOrEquals' expects two parameter of matching types. The function was invoked with values of type 'String' and 'Integer' that do not match.'.

WeeklyGiftDigest_20220120220106.zip

Dan Snyder

@MaryKathryn Odean I took a look at your flow and the issue is you need to use the system record ids for the Campaign id and fund ids. You can find that on the fund properties of the fund record in database view or from the fund record link in webview.

55 in this image is the fund system record id

File properties on fund record (access from file menu)

MaryKathryn Odean

Thank you so much! You just made my day. A rookie mistake.

Here's what else I was doing wrong:

In the Gift List action, I was trying to filter on “date added" using “Get Past Time" since NXT doesn't let you filter gift lists on “date added” and our gift entry is a few weeks behind. While I was troubleshooting my issues with the campaign and fund filter, I built an NXT List to filter on campaign and fund. I pulled the list in as the identifier. When you use an NXT list as the identifier, all other defined variables are ignored, including my “added on or after” filter. So the email digest included gifts from all time - yikes!

Moral of the story: either use an NXT list (easiest) or use the other fields available to filter your results. Since NXT doesn't let you filter lists on “date added” we're stuck doing things the complicated way.

Does anyone have any ideas for what I'm doing wrong with my HTML hyperlinks to the NXT donor profile and gift record? Screenshots below:

I have a Compose Clean Up HTML with the following expression:

replace(replace(body('Create_HTML_table'), '&lt;', '<'), '&gt;', '>')

MaryKathryn Odean

Hi all,

Just closing the loop (in case it is helpful to others) to share that I figured out what I did wrong. After adding the Compose action to clean up the HTML, you need to make sure that you are selecting this output in your email body (rather than the output from the Create HTML Table action). See screenshot below.

I had the original HTML Table output in the flow I shared previously.

I also updated my Clean Up HTML code to the following after googling:

replace(replace(replace(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&quot;','"')

Sue Hellard

Soooo Close

I've run with two different lists and can view the constituent and gift records from the links….but I get the above error when I click on:

Dan Snyder

Hmm, not sure but it looks like your link is to a NXT environment your user account does not have access to lists. Maybe make sure the list is saved so others can view? Just a couple random guesses.

Mark Warner

@Trevor Kelly Thank you! The flow works perfectly. Now, one of our gift officer would like to include portfolio type in the notification email (he has several types of portfolios). However, I'm not sure how to make that happen. When I try it, the flow returns a list of all of his portfolios on each gift line. Any thoughts? Again, thank you.

Dan Snyder

@Mark Warner Hi Mark, not sure if Trevor is active anymore as he is no longer at BB, but could you clarify what you mean by portfolio type or what field you use to pull that information. That along with a screen shot and I am sure some of us in the community can help.

Ryan York

@Dan Snyder

Hey Dan,

Different question on this PowerAutomate Gift Digest. Is there a way to order/sort the results of the HTML table? Maybe by last name, first name, name?

-Ryan

Dan Snyder

@Ryan York Yes, though credit goes to @Carolyn Grant for showing me this. See her post here, all the way at the bottom and note that you will need to use the NXT gifts connector, not the general NXT connector.

Carol Grant

@Dan Snyder @Ryan York

Thanks for the credit Dan but I don't this will work for Alpha Sort. The field has to be in the List Gifts to be able to sort. I don't see any names in List Gifts.

You can do amount, dates, gift type, fund ID.

-Carolyn (Carol) Grant

MaryKathryn Odean

@Carolyn Grant @Dan Snyder

Any chance either of you have experience adding constituent codes to this digest? I know I can use the List Constituent Codes action, but I'm having a hard time with the formatting.

EDIT: I actually figured it out. Folks can message me for the info if needed.

Tracey McKone

I know that @Trevor Kelly is no longer at BB, but maybe someone else can assist me?

I am brand new to working with Power Automate and for my first flow I have imported this one and updated it to be applied as a weekly gift report. The problem I am having is when I try to update the columns in the table I get errors. I tried to remove the Constituent ID, but when I do that, I get an error when I test the flow.

This is my code:
{

"Name": "<a href=http://renxt.blackbaud.com/constituents/@{items('For_each_gift_that_qualifies')?['constituent_id']}>@{outputs('Get_detailed_donor_info')?['body/name']}</a&gt;",

"Gift Amount": "<a href=http://renxt.blackbaud.com/gifts/@{items('For_each_gift_that_qualifies')?['id']}>@{body('Change_Amount_to_a_Currency')?['body']}</a&gt;"

}

This is the error:
Unable to process template language expressions in action 'Apply_gift_and_donor_content_to_our_variable' inputs at line '0' and column '0': 'The template language expression 'body('Change_Amount_to_a_Currency')?['body']' cannot be evaluated because property 'body' cannot be selected. Property selection is not supported on values of type 'String'. Please see https://aka.ms/logicexpressions for usage details.'.

I am also interested in seeing if I could include new columns such as for the fund description and/or assigned solicitor (not to the gift but to the constituent).

Thanks to anyone who might be able to help me.

Alex Wong

@Tracey McKone
The error is telling you that the gift amount is incorrectly referenced.

I'm assuming you have an action call “Change Amount to a Currency” which is formatting the gift amount. you can delete the dynamic content inside the <a> tag, and re-add it.

Technically, the issue is with the expression of the dynamic content itself, it should have been

@{body('Change_Amount_to_a_Currency')}
NOT
@{body('Change_Amount_to_a_Currency')?['body']}

Tracey McKone

@Alex Wong Thank you! I am happy that this was such a simple solution. It worked.

Tracey McKone

@Alex Wong

Can anyone tell me how I might be able to set up this flow so that those with an anonymous gift will be suppressed? I can not filter anonymous gifts from the gift list….

Alex Wong

@Tracey McKone
the result that comes back from list gift have a dynamic content call Anonymous?, which you can use a “Filter” action to filter them out.

Though you will have to test to see if “is equal to” “false” (as an expression for true/false value) or if it should be a “false” as in string. I think it is false as expression b/c the SKY API documentation say it is boolean, not string.

after that, your Apply to each will be on the Filter array action's output, and you will have to make sure any dynamic content you were using is updated form list gifts's dynamic content to filter array's

Tracey McKone

after that, your Apply to each will be on the Filter array action's output, and you will have to make sure any dynamic content you were using is updated form list gifts's dynamic content to filter array's

Could you break this down a bit further for me, @Alex Wong? Do I need to change something here?

Alex Wong

@Tracey McKone
first you have to change the “value” in your “For each gift that qualifies” action from the list gift value dynamic field to the Filter output body

then in the loop, since you have a “Get detailed gift info” action there, and I'm going to assume all your dynamic content used afterward is from this action, then you only need to change the “ID” dynamic content from the list gift action to the filter action.

Tracey McKone

@Alex Wong Thank you. I haven't quite worked out the solution yet, but I am done work for the week so I might circle back with you next week. I appreciate your expertise.

Tracey McKone

@Alex Wong I have another question I hope you can help me with.

I am trying to add a column which includes the gift type, but I am getting an error. Screen shots of what I have built and the error are attached. Can you see what the problem is, or do you need to see other parts of the flow?