cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JenLavery
Helper I
Helper I

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

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.

JenLavery_0-1661765605840.png

 

 

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.

JenLavery_1-1661765605843.png

 

 

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.

 

JenLavery_2-1661765605845.png

 

 

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.

JenLavery_3-1661765605848.png

 

 

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

 

JenLavery_4-1661765605851.png

 

 

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'))

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

JenLavery_0-1661858018227.png

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.

View solution in original post

5 REPLIES 5
JimmyWork
Super User
Super User

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.

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?

 

JenLavery_0-1661795978202.png

 

 

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.

@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

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

 

JenLavery_0-1661858018227.png

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,417)