Answered

Get Specific Values from SharePoint List to use as Contact Details in Approvals

(0) Share

Report

Posted on by JenLavery

There’s a complex Travel Approvals process in our organisation that I’m trying to automate for our Lean Management project. When someone puts through a request on MS Forms, it (ideally) would trigger a 3-stage approval process.

We have a Cost Approvals Matrix set up as a SharePoint List controlled by our Finance Department. This determines the 1st through 4th approvals for any Cost Centre. Drawing from this data keeps the process as up to date as it possibly can be.

I need to retrieve the 1st and 2nd line approver emails from the SharePoint List, which are People columns based on the Department entered on the Form. I can’t seem to make this work. If I get an Output directly from the ‘Get Items’ function, it initialises a ‘for each’ and I can’t make that work for Approvals. I’m now using two ‘Select’ entries to get the email addresses, but I’m having trouble translating that back to email outputs.

The Department is a unique entry on the SharePoint list, so that is my filter parameters. I’ve made sure the Form has listed them exactly.

I’m trying to use the ‘Union’ function to bring these back together. This is the error I’m getting now.

Can anyone help? I’ve tried this so many different ways, and I’m at my wits end right now. I suspect I’m misunderstanding how to use the ‘union’ function. As I understand it, it can operate with a singular source, but my syntax is probably wrong.

union(body('Select_BH_1st_Line_Approver'))

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

JimmyW 2,563 on at

Like (1)

Report

@JenLavery Your filter query contains ex?

Should it not be eq?

https://diyd365.com/2019/11/20/every-power-automate-ms-flow-filter-query-you-ever-wanted-to-know-as-a-functional-consultant/

Was this reply helpful? Yes No
JenLavery 61 on at

Like (0)

Report

OMG, can't believe I made a typo on that branch. I'd been looking at it so hard I completely missed it. 😅 Thanks for that. First hurdle gone, now on to the second.

Well, that part is now working. Select has mapped the two entries. But the 'Union' function at the next stage isn't working, and I'm no longer sure how to separate the email address out to add to the Approval. Any ideas? If it applies 'for each' at any stage, I can't add those outputs to the same Approval.

Was this reply helpful? Yes No
JenLavery 61 on at

Like (0)

Report

If I have both 1st Line Approver and 2nd Line Approver added into one single 'Select' function, is it possible to extract the mapped email information into two separate 'Compose' outputs?

I figured out the union issue, but it doesn't do what I hoped it would do. It still has the map label in the output, which breaks the Approval function. This small part lives within a control branch, and under the control I need to use coalesce for all control functions for the Approval, as it minimises problems at an administrative level. If a 'Apply to Each' happens, the outputs can't be used. It's important that the 1st and 2nd line approver get the same approval, not separate approvals.

I've configured this several different ways today alone and I still can't get it to function as usable ID details.

Was this reply helpful? Yes No
JimmyW 2,563 on at

Like (0)

Report

@JenLavery Sorry for not having time to check this out right now.

But do I understand this correctly you can extract the email address of approver 1 and approver 2?

It looks to me that you have the email values of the selected approvers in the Map values?

If you do a compose and just add the values "1st Line Approver Email" ; "2nd Line Approver Email, this would add up to a string that can be used in the Start and wait for an approval the semicolon is the separator for a new email.

If you want to avoid creating an 'Apply to each' you can always use the expression first(
It will only get the first value. But I'm not sure how everything is setup

Was this reply helpful? Yes No
Verified answer

JenLavery 61 on at

Like (1)

Report
No worries, got to a solution in the end. Rather than trying to filter the array, used the Replace function on the Select Output.

replace(replace(replace(join(body('Select_-_1st_Line_Approver'),','),'{"1st Line":"',''),'"}',''),',',';')

It spat out the right information, no Apply to Each triggered.

It was strange, when I tried using the 'First' function, it was still triggering Apply to Each. With this set up, it seemed to work without modifying the Dynamic Content tags.

Was this reply helpful? Yes No