How would one go about grabbing the approvers from a department list if they selected multiple departments? I have a form with multiple-choice options for affected departments and was wondering if it's a way to query the department list for multiple selections. I am following this video here, https://www.youtube.com/watch?v=GyoD0DD_fW0&list=PLTyFh-qDKAiFUGTtwSwFT79Y7_jq_Aipe
If the approval matrix has multiple emails to route, how do you craft the expression?
outputs('Get_items_-_match_department_to_matrix')?['body/value'][0]?['HODApprovers'][0]?['Email'][1]?['Email']
I have tried doing this and it didn't work.. or putting "[0]?['Email']" doesn't work either.
Hi Rhia,
Could you explain the code and expression that you did for the Variable departments?
split(replace(replace(replace(outputs('Get_response_details')?['body/r3cbfa311bdbc434190f4570ebd8b8f6c'],'","','|'), '["',''), '"]',''),'|')
Why do you have so many replace and what does each comma and pipe do? Sorry if i take a longer time to understand this.
Thank you!
That gave you the email you needed!? That's weird... but hey, whatever works 🙂
I was able to get it using the hardcoded (Single text) emails using this
outputs('Pull_PDT_Departments_List_(Get_items)')?['body/value']?[0]?['field_4'] instead of the people column! Not sure why I couldn't get it working the other way but thanks for your help!
I originally had it as department/approver, then I noticed you had just approver on yours so I changed it to that.
So based on what you have above, your Append to String Variable should beeeeee...
OMG! I would have never pieced that together! Thank you! I've almost got it working just stuck on the append to variable part. I keep getting this response.
I tried using the Department_x002f_Approver and changing my department list to match yours but still no luck. I know you said don't thank you just yet but THANK YOU FOR GETTING ME THIS FAR!!
Hmm, kind of, but not really. You need to retrieve the email that's for the chosen departments, then put all those emails together into a string, and plop that into your Approval task. Let's take a look...
First, I have my ApprovalMatrix where I identify which approver is associated with each department.
(Ignore that I'm the only person -- I am the only person that exists in my tenant haha.)
Next I'm going to create my MS Form and create a trigger on new MS Form response.
Here's what my form looks like:
This solution will work for any number of departments, whether it's 2 or 300.
Here's how I start the flow:
Now, to get into our department needs.
First, I'm initializing two variables. One holds our departments, the other holds our string of approver emails.
In the first one, notice I have an expression. That expression is splitting up the departments into an array by identifying the characters between them, replacing them with either a blank or a pipe, and then splitting where ever the pipe is.
Next, we're going to create an "Apply to Each" and point it at our "arrDepartments" array. We're going to loop through our departments!
The first action I drop in is the Get Items action, since we want to say "Hey SharePoint, please return the item where the Title is equal to this Department" (in my approval matrix, the title is the department.)
"Current Item" is a piece of dynamic content available in our Apply to Each, and it automatically picks up the next selected department per loop.
Next, still in our Apply to Each loop, we're going to use the "Append to string variable" action. A little bit of a formula here that, I'll be honest, I'm still learning the format of.. but it works! (This is all tested out btw.)
The expression shown here looks like this:
Here's the whole thing, top to bottom:
This should get you where you need to be - let me know if you have any issues.
Cheers,
Rhia
Would something similar to this be leading down the right path? https://powerusers.microsoft.com/t5/Building-Flows/MS-Form-to-SharePoint-using-Automate/m-p/1432633?lightbox-message-images-1436754=405252iFEDE785B4CE3C251
Hi Rhia,
The column is just setup as single text right now. Based upon the selection of eyed departments they would be sent an approval to respond regarding a participation request so it would need to be delivered all at once. Your list is accurate as far as department. Thanks for the assistance!
Jasmine