Get SharePoint Choice column values from Allow Multiple Selections into CSV Table Item

[rwittels]

I have spent hours reading and trying out other people's solutions and they just don't work.

I need a single expression for my CSV table Value field that gets all values from a SharePoint Choice column with Allow Multiple Selections on.

Multiple selections are displayed like this:

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

The closest I have got to the solution is an expression like this:

xpath(xml(json(concat('{"root":{"choices":', item()?['MyChoiceColumn'] , '}}'))), '/root/choices/Value/text()')

or this:

join(xpath(xml(json(concat('{ "r": { "entries" :', item()?['MyChoiceColumn'], '}}}'))), '//Value/text()'), ';')

All of them result in this type of error:

"The template language function 'json' parameter is not valid. The provided value '{"root":{"choices":}}' cannot be parsed: 'Unexpected character encountered while parsing value: }. Path 'root.choices', line 1, position 19.'. Please see https://aka.ms/logicexpressions#json for usage details."

How do I write an expression that gives me "Choice 6; Choice 2" or anything like this. Just the values?

[ManishSolanki]

Hi @rwittels 

There is an extra } in the expression due to which it failed.

Pls try this one:

join(xpath(xml(json(concat('{ "r": { "entries" :', item()?['MyChoiceColumn'], '}}'))), '//Value/text()'), ';')

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[rwittels]

Hi. Thank you for responding. I already tried this. It doesn't work.

Output is:
"The execution of template action 'Create_CSV_Table' failed. The column values could not be evaluated: 'The template language function 'json' parameter is not valid. The provided value '{ "r": { "entries" :}}' cannot be parsed: 'Unexpected character encountered while parsing value: }. Path 'r.entries', line 1, position 20.'. Please see https://aka.ms/logicexpressions#json for usage details.'."

Surely someone has done this before?

[ManishSolanki · Accepted solution]

Hi @rwittels 

Could you pls check if the input field 'MyChoiceColumn' is blank or empty for those failed cases? If that is true, then we should use empty() function to handle those cases.

if(empty(item()?['MyChoiceColumn']),'',join(xpath(xml(json(concat('{ "r": { "entries" :', item()?['MyChoiceColumn'], '}}'))), '//Value/text()'), ';'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[rwittels · Accepted solution]

I figured it out! Oh my goodness. I spent hours on this.

The dynamic block for my SharePoint column named "Domain", which is also called "Domain", actually refers to a column named "Category":

Even in list settings, the name is Domain:

After renaming the column in the expression the field values come through fine now. I will also mark your answer as the solution as you did help.

[rwittels]

Lessons learned from Export to CSV Table:

• Always mouse over your Dynamic content block. The field name might be different.
  
  
  
• If your SharePoint column is a choice column and the Dynamic content has a Value block, use it.
  
  
  
• If your SharePoint column is a choice column and the Dynamic content does not have a Value block, it is likely that the column has "Allow Multiple Selections" set to on. If this is the case, use this Expression instead:

if(empty(item()?['YourColumnName']),'',join(xpath(xml(json(concat('{ "root": { "choices" :',item()?['YourColumnName'],'}}'))), '/root/choices/Value/text()'), ';'))

The ';' at the end of the expression is the separator being used for the multiple values. You can set this to whatever works for you.

• If your SharePoint column is a People or Group Column there will be multiple values returned. If you want a specific value from the output use this expression. Let's say we are trying to get the DisplayName value from the AssignedTo column:

if(empty(item()?['AssignedTo']),'',join(xpath(xml(json(concat('{ "r": { "entries" :',item()?['AssignedTo'],'}}'))), '//DisplayName/text()'), ';'))​