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

Creating Variables from multiselect column in SharePoint

I'm trying to create a daily shift summary email that will trigger at 5am based on a SharePoint list.  I want each shift to be made into a table, and then the tables combined in one email.  The issue I am having is that some of the columns within the list are "multi-select" (ex: if one tech relieves another tech, both names would appear in the tech name column for that shift).  When it came to creating email notifications for the individual shifts, I used the compose variable to concat the multi-select columns and turned them into variables which I used within the email.  However, this doesn't seem to work for the daily summary because when I create the "combine tech variable", it concats every tech from every shift instead (when I choose the "tech" dynamic field in the for each, another for each is automatically generated and I can't remove it).  In other words, I need to get all the shift reports from the previous day and put them in one email but the info for each shift needs to remain seperate.  I've had no issue accomplishing this for the columns with a single answer but when I add a multiselect dynamic field to the email, it automaticaly generates the for each which I don't want as I want that field combined (I don't want to send two emails for the same shift, the first saying Tech A did Shift X and then a second email saying Tech B did it. I need one email saying Tech A and Tech B worked Shift X.)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Creating Variables from multiselect column in SharePoint

I was able to resolve the issue by creating one big For Each that included all of the individual For Each with my variables.  The trick was Setting the variable to be blank before each run.  

 

So the Flow goes...

 

1. Get Items from Sharepoint (which grabs all records from last 24 hours)

2. Initilize variable CombineTables

3. Initailize variable CombineOperators

4. Add "Apply to Each" with SharePoint column for Operators (this automatically creates another "Apply to Each" that is populated by the dynamic variable for all the records from Step 1)

5. Set Variable (Name: CombineOperators  Value: hit space bar once).. This should be positioned as the first step in the outer Apply to Each.  The next step will be the inner "Apply to Each" for the SharePoint column.

6. Within the inner "Apply to Each", Compose a variable (Input is concat formula.. 

concat(item()?['Value'],';',variables('CombineOperators'))
7. Set Variable (Name: CombineOperators  Value: 'Output' of previous Compose)
 
Moving outside the inner "Apply to Each" but still within the outer "Apply to Each"...
8. Compose variable ComposeTable (The HTML for the individual shift reports)
9. Compose variable CombineTables (concat formula to combine shift report tables from prvious step)
10. Set variable (Name: CombineTables  Value: 'Output' of previous Compose)
 
Outside of outer Apply to Each
11. Send email

View solution in original post

4 REPLIES 4
Highlighted

Re: Creating Variables from multiselect column in SharePoint

I think this is a similar problem to multipeople fields and creating a single event with attendees as I described on my blog: https://veenstra.me.uk/2018/04/23/microsoft-flow-the-pains-of-sharepoints-multi-people-fields/

 

Highlighted
Community Support
Community Support

Re: Creating Variables from multiselect column in SharePoint

Hi @ michaele124,

 

 

Have you made a try refer to  @Pieter_Veenstra's blog?

 

I have mde a test on my side,and @Pieter_Veenstra‘s workaround is work fine on my side.

Please take a try on his blog to achieve your needs to get all values of a mutiple choice column.

 

Please let me know if issue could be solved.

 

Regards,
Alice Zhang

Highlighted
Helper I
Helper I

Re: Creating Variables from multiselect column in SharePoint

I checked out the blog but I believe what is being suggested is something I have already done for the individual **bleep** reports but doesn't work for the daily summary.  The issue I have is that when I "Get Items" from the Sharepoint list, it is getting multiple shifts across a 24 hour window (ex: Shift A, Shift B, Shift C).  Shift A was done with one tech (John), Shift B was done with two techs, (Sally & Steve), Shift C was done with two techs (Bill & Lisa).  At the end of each day an email summary is sent for all the shifts. 

 

The email should look like:

 

Daily Summary

Shift: A

Techs: John

------------

Shift: B

Techs: Sally;Steve

------------

Shift: C

Techs: Bill;Lisa

 

What is happening now, is that all the techs are combined into each shift report so the email looks like...

 

Daily Summary

Shift: A

Techs: John;Sally;Steve;Bill;Lisa

------------

Shift: B

Techs: John;Sally;Steve;Bill;Lisa

------------

Shift: C

Techs: John;Sally;Steve;Bill;Lisa

 

I need the techs for each shift/record combined but only with techs within that same shift/record.

Highlighted
Helper I
Helper I

Re: Creating Variables from multiselect column in SharePoint

I was able to resolve the issue by creating one big For Each that included all of the individual For Each with my variables.  The trick was Setting the variable to be blank before each run.  

 

So the Flow goes...

 

1. Get Items from Sharepoint (which grabs all records from last 24 hours)

2. Initilize variable CombineTables

3. Initailize variable CombineOperators

4. Add "Apply to Each" with SharePoint column for Operators (this automatically creates another "Apply to Each" that is populated by the dynamic variable for all the records from Step 1)

5. Set Variable (Name: CombineOperators  Value: hit space bar once).. This should be positioned as the first step in the outer Apply to Each.  The next step will be the inner "Apply to Each" for the SharePoint column.

6. Within the inner "Apply to Each", Compose a variable (Input is concat formula.. 

concat(item()?['Value'],';',variables('CombineOperators'))
7. Set Variable (Name: CombineOperators  Value: 'Output' of previous Compose)
 
Moving outside the inner "Apply to Each" but still within the outer "Apply to Each"...
8. Compose variable ComposeTable (The HTML for the individual shift reports)
9. Compose variable CombineTables (concat formula to combine shift report tables from prvious step)
10. Set variable (Name: CombineTables  Value: 'Output' of previous Compose)
 
Outside of outer Apply to Each
11. Send email

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,867)