Extract data from sharepoint multi choice column

[Penfold623]

Hi All,

I'm running into some issues with this flow, I'm hoping someone can help.

I have a list in SharePoint, one of the columns is a choice field what allows multiple selections.

When I try to extract the data from, the column directly I get JSON.

I tried to add a variable, however, this displayed the full selection available in each record, rather than what was actually selected.

I've had a look at a few different posts, sadly, I'm no further forward with this.

any help greatly appreciated.

[Paulie78]

What you describe is correct and the choices are stored in an array which you can extract quite easily. However it should not return all possible choices in that array. For example I have a multi choice column called fruit and a populated field looks like this:

[{
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 1,
  "Value": "Pears"
}, {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 0,
  "Value": "Apples"
}, {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 2,
  "Value": "Melons"
}]

But there are more possible choices. So double check your output (from the get rows action). If it looks like it is returning the data properly, then building the right flow to extract that data is quite simple.

[Penfold623]

Hi @Paulie78 

I can get a list, with the JSON as you posted above, however, when I'm trying to extract the values. e.g. just show Melons; Apples/Pears/Apples; Pears; Melons (depending on what's selected) I'm running into trouble.

Thanks

[Paulie78]

Can you post a bit of JSON and I will make an example that exactly matches your column names, so you don't caught up.

Ideally I want the JSON from an entire row (which includes the multi choice column within it), or I can just show you how to do it with the fruit example above.

I presume you are only interested in the "Value" element from the row above? (i.e. Pears, Apples etc).

If it is sensitive you can send it in a PM or put it on pastebin and set it to burn after reading.

[Penfold623]

Sure @Paulie78 

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

Not sure if this is what you needed

[Paulie78]

Not really, but take a look at this and you should get the idea:

https://ibb.co/5sQ0Y8s 

I'm not sure what output you are hoping to produce, so left it open ended. But in this example you end up with an array which looks like:

[
  "Pears",
  "Apples",
  "Melons"
]

Which you can then do whatever you want with. Join it for example.

[Penfold623]

Thanks @Paulie78 

Thats seemed to work 🙂

How Can I get this data into a HTML Table please?

I'm looking to show:

Person who sold

Products sold

the table will be sent via email later in the flow

[Paulie78]

What’s your field name for the person who sold and what type of field is it? (This is why I wanted the outputs earlier!) 

[Penfold623]

Sorry, not sure how to get info...

Columns required are:

Seller_Name - Single line of text

Ref_Number - Number

Products_Sold - Multi choice

Thanks for you help with this 🙂

[Paulie78 · Accepted solution]

Here you go, the flow below produces the follow:

Which is I think what you are after. Flow can be seen here https://ibb.co/8Kz7Qgk :

I used a bit of table styling to make the output more legible, see this page on my blog for details:

Style HTML Table in Power Automate 

The code you need for append to array variable (SalesBySeller) step:

{
  "Seller_Name": @{items('Apply_to_each')['Seller_Name']},
  "Ref_Number": @{items('Apply_to_each')['Ref_Number']},
  "Products_Sold": "@{join(variables('salesItems'),',')}"
}

See how you get on. This should do what you need and isn't much different to the flow I gave you above, so you should be able to modify it pretty quickly.

Good luck.

[Penfold623]

Hi @Paulie78 .

I think I've gone wrong somewhere...

If I don't include the last Variable, I get the info (kinda) but the choices are added to each time.

(I get you need to set variable to [] to prevent this)

When I do set variable, no data is returned.

Will do images now

[Paulie78]

If the flow is quite long use this guide to take the screenshot:

https://www.tachytelic.net/2020/10/screenshot-entire-flow-power-automate/

I guess you have the append variable and the set [] the wrong way around.

[Penfold623]

[Paulie78 · Accepted solution]

[Penfold623]

ahhhh

Thanks @Paulie78 all sorted 🙂

[ClaireAllen]

Hi, @Penfold623 , @Paulie78 
I know this is an old post, but this is something I have been trying to resolve recently.
I posted this a while back Re: How to get just the "Current Item" in an "Appl... - Power Platform Community (microsoft.com)
I want to be able to pull data from a multi choice column that allows users to also enter their own values, when I do it I get repeated information as it's doing an apply to each, so I want to know if there is a way only to get the current item information out rather than everything repeating.

Thank you in advance
Claire