Return values of multi-choice Sharepoint field in an email

[ndeangelo1]

Hi, 

I have a list that contains a field called Product, which is a choice field, allows multiple selections.  I want to email users each time a new item is added to the list.  I've created a variable in my Flow/Automate that captures the Product selections and appends to get all the values selected - unfortunately it looks like this: 

Product: {"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"ChoiceA"}

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"ChoiceB"}

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"ChoiceC"}

How do I get just ChoiceA, ChoiceB, ChoiceC out of all of this?

Thank you.

[ScottShearer]

@ndeangelo1 

Please see my example below and let me know if you have any questions.  In my example, I have a choice column called MultiChoice that allows for multiple selections.  When referring to an individual choice value you refer to the column name followed by "Value" in Dynamic properties.

[ndeangelo1]

Hi Scott, 

Thanks - I'm following you down to the part where I do the Compose and Skip function - after that I"m not sure what to do?  

[ScottShearer]

@ndeangelo1 

My understanding is that you want to send an email.  Place your Send an email action immediately after the compose action.  Reference the output of the Compose action (along with the values of other columns if you want) in the email.

[ndeangelo1]

on my example, when I look at Dynamic Content for the email, I"m not seeing an "output" from the Compose function.  Did I miss something there?

[ScottShearer]

@ndeangelo1 

Please move the compose action outside of and below your loop and give it a try.  If you are still having issues, please post a screen shot of your Flow and include the expression you have in your Compose action.

[ndeangelo1]

It's still just giving me the same values - here's my Flow - 

[ScottShearer]

@ndeangelo1 

Can you please post the following

• Expand your initialize variable action and post a screen shot
• Expand you Append to string action and post a screen shot
• A screen shot of the expression used in your compose action

Also please verify that you are referencing the entry in the choice column followed by Value from Dynamic properties.

[ndeangelo1]

I believe everything you asked for is here:

Here's what the email returns:  

Product:

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Vendor Security Questionnaire (VSQ)"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"PreAssessment Report"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Initial Risk Profile (IRP)"}

Vendor: test vendor

[ScottShearer · Accepted solution]

@ndeangelo1 

If your choice column is called Product, you should refer to Product Value in Dynamic properties for your append to string action.

[ndeangelo1]

everything is working!  except for the "Skip" function seems to have zero effect on the items.  The comma still shows up - and I've tested changing the integer from 1 to 2 to 26 and it has no impact on the final result of the variable.  If I can't get that working, I can certainly live with this!!!  Thank you.

[Pulsar93230]

You have to take item "Outputs" of action compose2 instead of the variable you worked with ...

[Flowingpl]

Hi Scott,
Thank you for sharing the steps - this is super helpful for me to display values in a multi-choice field in email.  Really appreciate you sharing these steps.  

[Anonymous]

Hi @ScottShearer, Thank you for this solution! You really helped me.

[chunkeymonkey88]

Hi @ScottShearer thanks so much for this solution, this had me stuck for a while now!

Can you help me refine the skip function as i cannot get it to strip out the first comma when adding the variable to an email? I had tried playing with the count as others had mentioned on the thread but i cannot get it to work and it adds the ", multivalue1,multivalue2" in the subject line when adding that variable output

Here is my expression:  skip(variables('MultiCat'),1)

Thank you in advance

[mpsalian]

try this and let me know
so my Odata structure is like this 

[
{
"@odata.etag": "\"1\"",
"ItemInternalId": "2",
"ID": 2,
"Title": "Test2",
"Category0": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "Design"
}
],
"Category0@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"Category0#Id": [
1
],
"Category0#Id@odata.type": "#Collection(Int64)",
"Progress": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "In progress"
},
"Progress#Id": 1,
"Priority": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "High"
}

]

To get the value of category i would iterate like this 

join(xpath(xml(json(concat('{"body":{"value":', item()?['Category0'] , '}}'))), '/body/value/Value/text()'), ', ')

Try and let me know @ndeangelo1 

[gebhardd]

@mpsalian  - This is a great answer and works perfect for scenarios in which we do not want to loop through the choice column.

For example, I created a filtered collection of SharePoint items through the Get Items step. 

My goal is to export details of these items to a CSV file; however, if we attempt to export the data straight from the Get Items step, we receive the full object(s) as seen in the below screenshot of Cost Center 1 and 2.

Using the Select step, we can pick what fields we want to export, but we do not want to loop through all the SharePoint items and Cost Center choices.  Thankfully, using this solution, all we have to do is place this formula in map value.

When we export the data, we get exactly the values we want.

Thanks again!