I have created a flow using the following options:
Get Items (Sharepoint List)
Fiter Array
Select
Create CSV Table
Send Email.
For the select option - I have selected various fields from my sharepoint list including one which is a multiple selection field.
The flow works and exports to CSV - however the output for the multi-selct field is a long string which includes the value retrieved i.e
[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Main Hall"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Dining Room"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Kitchen"}]
What I would like is to receive the values seperated by a comma or semi colon to make this more user friendly. for example in the output above it should ideally read: Main Hall; Dining Room; Kitchen
Any suggestions as to how I can do this by either manipulating the output string as an additional step in the flow or changing the way I collect the data for this column.
Thanks in advance
Amit
Solved! Go to Solution.
okay, i had to rethink this csv export. so i do the csv export in two steps:
it's a little bit messi and the flow is now slow, but it works.
like:
step 1 for each choice-field:
load all items
loop through all this items
load per each item the multiselectvalue (choice-field) collection into an array variable
loop though the array-values
append each multiselectvalue to a string variable concatenated with your "separator"
update listitem with the string value (which contains all separated values)
step 2:
load all items
create csv
Hi @amitlathia,
Could you show me a screenshot of your flow configuration?
According to your description, I created a simple flow likes below. In the Select action, configure the Choice column (multiple selection enabled field) with the following expression:
last(item()?['Choice1'])?['Value']
In the email body, the choice value would be ideally read.
Please take this for a reference and try it on your side.
Best regards,
Mabel Mao
Hi Mabel ( @v-yamao-msft),
Please see attached my flow now including your suggested string. This works to the extent that only the value shows in the resulting CSV file, however what I would like is all the values showing not just the very last one. For example if 2 rooms are booked, it would show "Main Hall; Kitchen". At the moment only "Kitchen" would appear.
Is there any way of expanding the expression so as to collect the multiple values?
Thank you for your help with this.
Amit
Hi @v-yamao-msft Just wondering if you have had any further ideas as to how I can retrieve all the values out of a multi-select sharepoint column. I would imagine I am not the only one with a use case for this so any help would be great to me and others.
Thanks once again
Amit
I am also having this issue and cannot figure it out for the life of me!
do you found a solution for that?
Any solution ?
okay, i had to rethink this csv export. so i do the csv export in two steps:
it's a little bit messi and the flow is now slow, but it works.
Can you provide a screenshot please ? I didn't get how you export the multi values field into single value
Thank you
like:
step 1 for each choice-field:
load all items
loop through all this items
load per each item the multiselectvalue (choice-field) collection into an array variable
loop though the array-values
append each multiselectvalue to a string variable concatenated with your "separator"
update listitem with the string value (which contains all separated values)
step 2:
load all items
create csv
Any solution on this ?
Can you please share the screenshot of your approach
You can use the following expression for your multi-select column:
xpath(xml(json(concat('{"root":{"choices":', item()?['Officers'] , '}}'))), '/root/choices/Value/text()')
**My SP column (multi-select) field is called "Officers". Change yours accordingly.
Result:
User | Count |
---|---|
87 | |
73 | |
43 | |
26 | |
26 |
User | Count |
---|---|
42 | |
27 | |
25 | |
23 | |
18 |