Sharing my Power Automate Flow: Daily Gift Digest Notification

Alex Wong

@Tracey McKone
the name of the action does exactly tells me what action you are doing. for example: Apply gift and donor content to our vaiable, is it set variable or append to array variable?

on that same step. I see you began with a double quote, which shouldn't be needed

please also screenshot the full action for create html table in edit mode so I can see what you are doing there

Tracey McKone

@Alex Wong
I missed seeing that double quote. That was it. It seems to work now.

Art Bryman

Thank you, everyone, for putting this flow together. I had it up and running in the default configuration. Now, I'm trying to add additional fields to the table, and the flow keeps erroring out. I could really use your help with this.

Error: Action 'Apply_to_each' failed
Error Details: The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@items('For_each_gift_that_qualifies')' is of type 'Object'. The result must be a valid array.

My “Apply to Each” code. I made sure to use the values from “Get_detailed_gift_info” list for the values I added.

{
"inputs": {
"name": "Email Content",
"value": {
"Name": "<a href=http://renxt.blackbaud.com/constituents/@{items('For_each_gift_that_qualifies')?['constituent_id']}>@{outputs('Get_detailed_donor_info')?['body/name']}</a>",
"Soft Credit(s)": "@{outputs('Get_detailed_gift_info')?['body/soft_credits']}",
"Gift": "<a href=http://renxt.blackbaud.com/gifts/@{items('For_each_gift_that_qualifies')?['id']}>@{body('Change_Amount_to_a_Currency')?['body']}</a> @{outputs('Get_detailed_gift_info')?['body/type']}",
"Fund": "TBD",
"Fundraiser(s)": "@{outputs('Get_detailed_gift_info')?['body/fundraisers']}",
"Reference": "@{outputs('Get_detailed_gift_info')?['body/reference']}"
}
},
"metadata": {
"operationMetadataId": "bc52b1dd-ae0a-49cc-a12c-50e047fb5168"
}
}

Error in flow:

Flow settings:

Thank you!! Art

Candy Reichert

I have this working great. It would be quite helpful to have this sent to a SharePoint folder and converted to excel. I'm wondering if someone could direct me how to add the conversion from a .csv file to an excel file and send it to a SharePoint folder.

Dan Snyder

@Art Bryman Do you have a fundraiser added to all gifts? If not that is likely causing the error because that information is returned as an array.

I handled this by adding variables for the fundraiser name depending on the length of the fundraiser array. I did this a couple years ago so there may be more efficient ways of doing this. The expression at the top is: length(outputs('Get_each_gift_details')?['body/fundraisers'])

I have a variable for the “No” side and then use that later to list the fundraiser or not: if(equals(variables('PM no side'), 'No'), '', variables('PM yes side'))

Dan Snyder

@Candy Reichert Check out @Alex Wong's post here.

Alex Wong

@Art Bryman
There is a couple of problem I see here

• The error is complaining about Apply to each action is using “Object” instead of “Array”
  • Apply to each is used for “looping” each element of an array, giving an Object is not correct usae of Apply to each
  • Looking at your screenshot, it looks like what you give Apply to each to loop is Current item, which looks to be the gift record itself, not the fundraiser array in the gift record.
  • The problem here is, you are also not using Apply to each properly, even if you did provide the fundraiser array to Apply to each loop. Basically even if you got the action done right, what you end up doing is you are adding a row into the table you are producing for EACH fundraiser in the gift, not 1 row for each gift.
• The root cause of the issue is you mis-use of the “Fundraisers” dynamic content (and others) in the Apply gift and donor content to our variable action.
  • Adding Soft credits dynamic content, which is an array, will look “bad” on the email as it will only show the JSON code-like string, which will make your column for Soft credits to look like this: [{"id":"12345","amount":{"value":55.55},"constituent_id","8888","gift_id":"99999"},{"id":"12346","amount":{"value":55.55},"constituent_id","9999","gift_id":"99999"}]
  • Same is going to be true for your Fundraisers dynamic content being added directly into Apply gift and donor content to our variable.
  • You also statically added “Fund”:"TBD", which isn't going to be true for all your gifts, and adding Gift_splits dynamic content would also result in the same JSON code-like string in your email.

I have answer this issue a few times now, let me see if I can find the original post with this explained and post here.

Alex Wong

Here's the post that handles “array” of values in a gift (gift split).

You can try it and apply the same knowledge into your flow. If you still have difficulty, post here again on where you got stuck.

Austen Brown

@Candy Reichert - @Alex Wong shared a way to do this in his “Sharing my Power Automate template: RE:Queue CSV to SharePoint Excel Table” post. It uses an office script to change the file type and add the data into a table.

Candy Reichert

I am trying to add the Pay method for each gift in the list.

I have added an Initialize variable, and a set variable to Nullify the Pay method. This cause it to create an Apply to Each with in the ‘For each gift record in my list’ section.

After I tested this it gave me an error:
The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@items('For_each_gift_record_in_my_list')' is of type 'Object'. The result must be a valid array.

I also get this notification when editing the flow.

This is me just guessing on how this should be set up.

Any help getting this right would be much appreciated. Thank you.

Candy Reichert

This looks like the same problem as the previous post. I'll check out @Alex Wong link that he responded with to try to solve the problem.

Alex Wong

@Candy Reichert
a little more screenshot on where you placed your actions will be needed to help you diagnose.

however, I have a pretty good idea what your problem is: pay method is defined as an Array in the get a gift api endpoint, meaning Power Automate will automatically put a Apply to Each thinking there may be more than ONE pay method, which in fact RE gift never have more than 1 pay method. This is an API bug that probably existed forever. @Ben Wong any insight to this?

Anyway, the way to fix isn't as straightfoward (unlike the other post I did where gift split is truly an array, meaning there CAN be more than one split of gift on any single gift record). It will require the use of some power automate expression. I can provide that to you once you give me more screenshots

Candy Reichert

@Alex Wong
Here is the order of where the Pay Method is listed. I'm not sure I have everything set up correctly.

PayMethod opened up:

This is what is nullified after:

Also: this.

This is the error I get when I test run it:

I also have a couple other questions that I haven't figured out:
1. My Anonymous field shows blank on line one, but actually shows incorrectly on the second gift. For some reason it is showing on the wrong gift line.
2. the Gift ID: did not showed correctly when I just added the GIFT ID in and didn't use the Initialized Variable that i had set up. So I could probably just delete both the Initialized Variable and the Set Variable for the Gift ID.

Let me know if you need different information. Thank you for you help!

Alex Wong

@Candy Reichert
your error now is in the “Create_HTML_table” action, please screenshot that.

There is an issue you have in the “Add to our digest variable” action, which has many “characters” that shouldn't be there

Candy Reichert

@Alex Wong Thank you for pointing out those errors. I'm sure they were causing havock.
After fixing those and retesting the flow, it is now back to the PayMethod error.
This is the error I get after testing it.

Candy Reichert

@Alex Wong I just changed the Initialize Variable - Pay Method from Array, to String.

When retesting it, IT WORKED!! WAHOO!!

Erin Gyuleseryan

@Alex Wong or anyone who can lend a hand. This is my first flow. I've set it up by following Trevor's video and had success. However, I'm having two issues:

My gift amount is not formatted correctly - here's how it's showing on my email and the corresponding steps,

Also, my hyperlinks to the constituent record and gift record are not working. When I click on them they redirect to my NXT work page. See above for screenshots.

Alex Wong

@Erin McKenney
I can't see an immediate issue with your gift amount, however, it also depends on how you end up using the array variable Gift Digest into the email, are you using “HTML” email (by clicking on the </> icon). go to a run history, and the email sending action and copy out the in/output from that action here.

Your link is not working b/c you have the URL incorrect.

https://host.nxt.blackbaud.com/gift/page/@{outputs('Get_a_gift')?['body/id']}

Alex Wong

@Erin McKenney
OK, found the issue: NEW DESIGNER of flow is the issue.

I just tried it in the NEW DESIGNER and added the dynamic content for the output from the format currency step, I get the same problem you have.

When I go to OLD designer of flow, removed the dynamic content and add it again from the format step, it works.

so try that.

Additional note:

here's how it look:

Code View:

{

"type": "Compose",

"inputs": "NEW DESIGNER: @{outputs('Make_gift_amt_into_currency_format')}\\n\\nOLD DESIGNER: @{body('Make_gift_amt_into_currency_format')}\\n\\nEXPRESSION: @{formatNumber(outputs('Get_a_gift')?['body/amount/value'], 'C2')}",

"runAfter": {

"Make_gift_amt_into_currency_format": [

"Succeeded"

]

},

"metadata": {

"operationMetadataId": "3c3c74b4-c9c4-48b8-ac4a-f458144c1484"

}

}

so new designer is putting the “creating” the wrong code for type of action's dynamic content: OUTPUTS vs BODY, not the same. This is a BUG of the new designer (among many other bug, i strongly suggest you do not use NEW DESIGNER yet).

Also, you don't need to use the action to format currency, you can just use expression, like the gift date is formatted using expression.

@{formatNumber(outputs('Get_a_gift')?['body/amount/value'], 'C2')}

Erin Gyuleseryan

@Alex Wong Thank you for jumping in to help! I really appreciate your insights! All the changes have worked.

Joe Chinni

Hi there, I'm fairly new to power automate but was able to get this flow working without too much fuss. I have a couple questions.

1. Does anyone have this setup to exclude anonymous gifts or include them but change the name to anonymous?
2. I have an issue where the fundraiser names and soft credit names have no spaces between them when it lists multiples. I tried using the concat function to add a space separator but that didn't work.

Thank you!

Alex Wong

@Joe Chinni
1. After the “list gift” action, use a “Filter array” action to filter the list of gift for is_anonymous=false

2. going to need to see some screenshot on how/where you placed the 2 names

Austen Brown

@Joe Chinni - If you want to include ‘anonymous’ but change the listed name… initialize a string variable at the top of your flow. Then prior to appending the array (eventual digest), within the apply to each that loops through the gifts, add a control condition ‘anonymous’ equals ‘true’; on the yes side set the above string variable type in “Anonymous”, on the no side set the variable with the dynamic content - “Name”. Then within your append array action, include the output of the variable instead of “Constituent Name”.

Erin Gyuleseryan

@Alex Wong can I bug you for help again?

Everything was running fine, and then I was asked to please add the gift subtype. I'm coming up with the same error on my gift digest step that Candy mentioned below: “The property columns must be specified unless the from property value is an array of objects.” But I'm not seeing anything in my digest that seems like an extra character, which seemed to solve her issue. I set up my variable as a string (since that's what I did with Pay Method. I also tested changing that to an array with no change.)

My guess is that the issue is because the gift subtype can be blank, unlike the pay method. Is there something special I need to do for a drop-down menu item that might not be used for every gift? Or is there something else I should be considering?

Susan Fioribello

@Dan Snyder

I'm not sure if this thread is still active. Hopefully someone will answer.

I tried using the Blackbaud Gifts Lists to sort the output. I want to sort of on Gift amount, descending.

I cannot figure out where to put it. If I put it in place of the Gift List in the template, Amount is not an option. If I put it after, the flow breaks. If I replace the Gift List Blackbaud NXT with Gift List Blackbaud Gifts, Amount is not an option.

I'm very new to Power Automate.

Alex Wong

@Susan Fioribello
you will need to use a “sort” expression to sort by the amount.

You can either sort the gift list before you did all the work in the flow, OR sort the array variable before using in the create html action step. The latter is easier to do

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

Dan Snyder

@Susan Fioribello So I am using the List Gifts action on the NXT Gifts connector. You will want to make sure your action has the gift present in the corner (I'll try to include that in a screen shot).

Where in the flow to place the action, I have it after initializing all of my variables in the beginning of the flow. If you are not seeing the Sorted by field, make sure you “Show all” in advanced parameters if using the new copilot view or “Show advanced options” in the classic view.

If this isn't making sense with what you are seeing, please share some screen shots of the steps in your flow and that should help us give you a better answer.

Location of action within flow, there are many steps below this, but wanted to show you the location after initializing the variables.

Copilot view

Classic view

Alex Wong

@Dan Snyder
Thanks Dan, forgot the sort option is available on the connector action

Susan Fioribello

@Dan Snyder
I'm using the template from the template showplace. This version seems to be working but even though the list in NXT is sorted by amount, the email shows the gifts in any order.

The screenshot below shows where the current Blackbaud Raiser's Edge NXT is placed. This version does not have the “sort” option. When I switch it out for Blackbaud Raiser's Edge Gift, the “sort” option is included but “amount” is not an option in either Function or Dynamic variables.

Dan Snyder

@Susan Fioribello Thanks for that screenshot. The list gift connector you are using is the general NXT connector which won't allow you to sort. You can tell it is the older connector because of the check mark in the icon. If you replace that step with the NXT Gifts connector (has a present icon) you should have the ability to sort.

The one thing I am not sure of though and I suspect could be an issue is if you are connecting that action to an NXT gift list, all other filters are ignored so even if you put the sort in the advanced options it is only looking at the list. I am on my phone right now but will try to give you some screen shots in the morning or if @Alex Wong gets to it first he will know how to guide you too.