'How to extract value from Sharepoint multiple choice values column in Flow
I'm trying to write a Flow that will enable me to periodically email a Sharepoint list to an email address. All works well for most columns on the list except where I have a Choice type column that has multiple select values. When I export those to my email, I get a JSON object such as below:
[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Choice 1"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Choice 2"}]
I can find no way in the expression editor of Flow that will allow me to extract and concatenate the values, i.e. 'Choice 1, Choice 2'. It seems such a fundamentally simple thing to do! The built-in concatenation method will only take a string value and not an array of strings.
Can anyone help?
Solution 1:[1]
The way to do this in Flow:
- Add an 'Initialize Variable' step to initialize a string variable that will be used to store the concatenated version of the selected choices
- Add an 'Append to string variable' step and in the value, select 'YourChoiceColumnName Value' as the dynamic content, where 'YourChoiceColumnName' is the name you defined for your choice column in SharePoint. You should see this in the dynamic content list.
- Enter a deliminator character and space after the value. For example, a semicolon and a space. This will end up creating a string such as: Option1; Option3; where options 1 and 3 are selected.
You can then use your variable where you want the string representation of the selections to appear.
Should you wish to not have the trailing deliminator you can fix that too.
Add a 'Text Functions' step for 'Substring' step. In that you will set values as follows:
- Text: your variable
- Starting Position: 0
- Length: Use an expression. If you followed the above a did a delimiter and space, then you can use
sub(length(variables('yourVariableName')),2)
where 'yourVariableName' is the name of your variable defined in Flow for the string representation of the choices.
You cannot just use this in output such as in an email template or it'll give you smack. What you need to do is use an expression where you want to display this.
Use the following where you want the output to appear:
outputs('step_name_for_substring_step')?['body']
Where 'step_name_for_substring_step' is the name of your step with underscore where you have spaces. For example, if you named your step 'myChoices prepped string list' then your expression would be outputs('myChoices_prepped_string_list')?['body']
With that you should be good to go!
Solution 2:[2]
I just faced the same issue. You can get value by referring to it via item()?['Your column name']?['Value']
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | dunderwood |
Solution 2 | Oleksii Sergiienko |