Retrieve value so the output of the field is an array (multiple selection)

[kz88]

I have a simple flow to get data from SharePoint List, put it into a csv table format, and save it into a csv file. 

Everything works great except for one field, which is an array/multi-select field that allows the user to select more than 1 of the choices. 

When this field gets saved into csv file, I only want to have the value, but there's a string of information that comes attached to the value:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"ProductB"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"ProductZ"}]

Below is the flow I have set up. I see a similar question in another thread here, and the solution is to "add array index to get the element like item()?['Service']?['Value]" but I am having a hard time understanding how to accomplish this. 

Can you review my flow below and tell me if I need to add another action to my flow? Where should I add item()?['Service']?['Value]?

[FLMike · Accepted solution]

Hi @kz88 

I am a little confused at what you have there as your Get Items will possibly return more than 1 thing, so you should use an Apply to Each loop

But here is how you get what you want

Get Items

--Apply to Each on the value dynamic property

----Inside here add a Select

------For the FROM, select the Multi-select Choice Dynamic Property from your Get Items action

------Then for the Map, click the little button to switch to Map advanced mode (so you can type it in)

------Click in the box

------Click the expression tab

------type the expression item()['value'] (it will be value or Value)

------Now if you want them all joined together into a single string

------Add  a Join action

------the input is the Output of the Select and the Join with type a ,

And now you have a nice comma separated string  joined together of just the values.. nothing else

If you wanted an array of just values then in your Select DONT use advanced, just add the Key as Value and the value as item()['value'] or Value

Then skip the join

Option 2.

add a Parse json action

in the Input put the Dynamic property for the Multi-select

Since you already ran this before, you have the schema in the history, go grab the output (body etc)

Use that to create the Schema sample

Next add an Apply to each with the input being the Body of the Parse JSON

And inside add a compose and just put Value.. as it will be a dynamic property. 

Now you can loop through all of them.. And do whatever you want.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[v-yetonggu-msft · Accepted solution]

Hi @kz88 ,

Is your column a multiple-choice column?

If so, I did a test for your reference.

In my scenario:

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

My SharePoint List:

Best Regards,

Sunshine Gu

[FLMike]

While xpath does work its a hard hit and slower. Using a select is operationally a faster choice.

But either way hehe it all works.

[grantjenkins]

@FLMike Using XML/XPath is much quicker with this sort of scenario as it completely negates the need for an Apply to each. This is especially true if you have a lot of items to iterate over.

[FLMike]

@grantjenkins 

not from the back end its not, plus you dont need an apply to each as I showed in mine, i just showed different ways.

I could have just use a single compose with a select and join at the same time

But either or 🙂 for the user/

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[grantjenkins]

Hi @FLMike 

Doing it your way would indeed need to use an Apply to each as you need to iterate over each of the items from Get items.

Using the XML/XPath way allows you to just pass the values from Get items directly into the Create CSV table (no loop required).

If you had say 5000 items, your way would likely take minutes to complete, whilst the XML/XPath way would complete in just a few seconds.

[FLMike]

@grantjenkins 

If you do the Select right after the get items, you do not get an Apply to each, as I mentioned its not clear if they want 1 row or many rows, because he doesn't currently have it.

So for the sense of 1 row either or really.

But what he is talking about is the Value property of the Mutliselect, not the value of the entire thing. So again it appears he just wants it as one, but its still confusing.

So the answered doesn't change that XPATH is a bigger hit to the back end (which is what I said), because of the expansion and conversions it has to do.

And it isn't that i disagree with your statement of whether its 5,000 but in this case, it seems as if its one. /so shrug 🙂

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey