Capturing and Storing Responses in Power Automate & Logic Apps

I had a problem to solve. A Power App I’m developing pro-bono does, amongst other things, manage training events for my local search and rescue group and keep a tally of who has responded, and whether the response is attending or not attending.

To achieve this, each training event is an SP list item, and the list has two columns to track the responses; Attendees and Non Attendees. The columns are multiple line of text columns and they hold a comma separated list of attendees and non attendees full names.

Although I will update this article at some point to explain a lot of how this is done in the Power App, what I want to focus on here is how to process the response in Power Automate or Logic Apps.

A bit of background: A new training event is added to a SharePoint list via Power Apps. There is also another list; Members. In my case the members don’t have logins so I keep an SP list, but the “attendees” could just as well be from your Azure AD/Office 365 directory.

A Flow is triggered when a new item is added to the training events list and a condition matches (sendEmail = “Yes” in my case). The Flow sends an email to everyone in the list via a Get Items on the Membership list then an Apply to each around the resulting array with Send and Email inside. In the body of the email there are a couple of HTML buttons, Yes and No. Each button is a hyperlink, the URL being the trigger of another flow, and within each individual email the hyperlink target contains a query with the response (Y or N), the SP list ID of the current item in the loop, and also the SP List ID of the training event (from the trigger).

For example the Yes button has a hyperlink target something like: https://my.flow.trigger.url/blah-blah-blah?member=@{item()?[‘ID’]}&training=@{triggerBody()?[‘ID’]&response=y

The idea is the recipient clicks on one of the buttons to indicate whether they can attend the training or not, and that response is recorded in the appropriate column of the training event list item.

To achieve this, another Flow (or Logic App in my case) triggers on the URL above and collects those queries as dynamic content from the trigger, does the stuff I’m going to explain below and then responds with a 200 status code and some HTML back to the trigger to show the end-user that it worked.

That’s the background of mine, but your own Flow might be triggered in a totally different way, like when someone adds an item to an SP list and you capture the Modified By name along with some other pertinent data, or anything really. The point is, you’re triggering a Flow and you have the name of someone, the action they’re taking, i.e. attending or not attending, and the SP list item you want to update with that information.

So, the challenge is: What happens if someone is already in one of the columns? If they click the button twice, they’ll either be in both columns, or in one column twice. They could click the button several times and make a right mess of it. We therefore have to work some logic into the Flow to make it robust, and that’s what this blog post is all about.

The Flow

I’ll start at the point where we already have the name of the person we’re adding to the training event as dynamic content, and we have the whole record from the SP list (the training event) from a Get Item action.

Firstly, initialise two Array variables:

Next, set the value of the variables as follows:

The formulae take the existing comma separated list of names from each of the two string columns and turn them into arrays. If it wasn’t for the fact that a null input into the split() function sends it into a spin the expression would be much simpler.

If the contents of the Attendees columns is not null then split the Attendees column on the comma (split() outputs an array). If it is null then return null (the array remains empty).

We now have our existing Attendees and Non Attendees in two arrays.

Now we need to filter those arrays for the person who has responded using Filter array:

Here we’re filtering out the responder, but only including items, item(),  in the array that do not contain the name of the person. Title in my case, is the result of looking up the member from the Members SP list using the ID that came in via the HTTP query. In my list Title contains the person’s name. Your mileage may vary etc etc.

We now have the two arrays stripped of any mention of our loyal member, whether they were already in either of those comma separated text columns or not.

Now, overwrite the contents of those two array variables from earlier with the outputs of the Filter array actions above:

At this point in the Flow we need to know whether the person is attending or not attending. In my case I have the response (Y or N) in a compose action, so I run a Condition on that:

If the response is Y, then the condition matches True, and the name of the person is added to the attending array using the Append to array variable action.

If the response isn’t Y, then we Append to array variable, but to the non-attending array.

Now to finish off, we turn these two arrays back into comma separated strings with the Join action, which is one of the data operations:

Finally we update our SP list item with the two strings, the outputs from the two Join actions:

So there you have it. In Power Apps we can split these columns on the comma to generate two tables, and do whatever we want with them.

This is a relatively short blog post. I hope someone finds it useful. Please leave comments if you want to.