Extract data from sharepoint multi choice column

(0) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (2)

Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Penfold623 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Penfold623 on at

Like (0)

Report
Copy link

Link copied!
Sure @Paulie78

{ "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", "Id": 13, "Value": "Lollypop" }
Not sure if this is what you needed

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Penfold623 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Penfold623 on at

Like (0)

Report
Copy link

Link copied!

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 🙂

Was this reply helpful? Yes No
Verified answer

Paulie78 8,422 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Penfold623 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No