You’re offline. This is a read only version of the page.
Announcements
17
Hi All,
I am trying to export a SharePoint list to a csv file however I have a column called "Assigned to" that is a People Picker column and returns a JSON Object.
[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|abc@abc.com","DisplayName":"John Smith","Email":"abc@abc.com","Picture":"https://abc.sharepoint.com/sites/abc/_layouts/15/UserPhoto.aspx?Size=L&AccountName=abc@abc.com","Department":"Finance","JobTitle":"Pencil Pusher"}]
Is there a way to create an expression that only takes the Display Name (EG: Split function)
Hello @grantjenkins,
It helped me address the same problem i faced last week. However, it seems the expression isn't functioning as expected for the Multiline Text field. Despite using the provided expression, the field appears empty in the CSV table. Could you lend a hand in resolving this issue?
Sharepoint list column name in question is 'BillingComment_x0028_Ifadd_x002e'.
Here's the expression I used: xpath(item(), 'string(//BillingComment_x0028_Ifadd_x002e)')
Thank you
Hello @grantjenkins,
Your assistance was fantastic and helped me address the problem. However, it seems the expression isn't functioning as expected for the Multiline Text field. Despite using the provided expression, the field appears empty in the CSV table. Could you lend a hand in resolving this issue?
Sharepoint list column name in question is 'BillingComment_x0028_Ifadd_x002e'.
Here's the expression I used: xpath(item(), 'string(//BillingComment_x0028_Ifadd_x002e)')
Thank you
@grantjenkins any idea why multi-select Person column with the code:
Share
Report
All responses (
Answers (
join(xpath(item(), '//Members/DisplayName/text()'), ', ') Seems to randomly give error:
InvalidTemplate. Unable to process template language expressions in action 'Select' inputs at line '0' and column '0': 'The template language function 'xpath' failed to parse the provided XML.'.
Error happens for SharePoint list with identical data then in other lists that is looped through in the flow. I did go through the xml, and couldn't find any anomalies. Structuce and field names are identical.
If you mean escaping the commas you add in the joins, then these should be all fine in the final CSV output. The multi-select values should all get enclosed within double quotes, so the CSV output won't split them again.
This is the output I get without doing any extra escaping.
@grantjenkins lol.... It's easy when you know how! I was trying all kind of split functions... 🙂
Really appreciate the help!
Now I just need to find out how to escape commas in the content so it doesn't mess up the CSV file. 🙂
Thanks again!
You would just need to wrap it within string(...) to get the actual Display Name value.
xpath(item(), 'string(//AssignedTo/DisplayName/text())')
@grantjenkins This is a really cool solution and just what I was looking for... It's also as you said why more efficient than Looping through items.
I have a quick question with regards to your Members (people lookup) field.
If you do this for a single user rather than mutliple users then I get ["Display Name"] returned (IE: With square brackets and a semicolon.)
Is there a simple way to return just the DisplayName?
xpath(item(), '//AssignedTo/DisplayName/text()')
There was a similar scenario yesterday in the forums where I used XML and XPath to get what they wanted. This sort of thing is perfect for XPath as it can avoid using loops making it much more efficient.
Below is the List I'm using for this example. Note that it has multi-select Choice (Countries) and Person (Members) columns.
See full flow below. I'll go into each of the actions.
Get items retrieves all the items from our list.
XML is a Compose that converts our JSON output from Get items to XML. Note that the expression also adds a root element to ensure it's valid XML. The expression used is below. Getting our data into XML means we can use XPath in the next step to collate values across rows.
xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))
Below is a sample of what the list data would look like in XML.
Create CSV table uses XPath expressions to build up our table.
The expression we use as the input (From) data is below. Effectively, it's getting the value property from our XML which is our array of items.
xpath(outputs('XML'), '//root/value')
The Title and Description values are Single line of text columns so we can use the following expressions to get these.
xpath(item(), 'string(//Title/text())')
xpath(item(), 'string(//Description/text())')
Status is a single-select Choice column, so we also need to add /Value to get the actual value.
xpath(item(), 'string(//Status/Value/text())')
Countries is a multi-select Choice column, so we first need to get the collection of Countries including the /Value, then join them with ', '.
join(xpath(item(), '//Countries/Value/text()'), ', ')
Members is a multi-select Person column, so we first need to get the collection of Members including /DisplayName to get the actual name of the person, then join them with ', '
join(xpath(item(), '//Members/DisplayName/text()'), ', ')
This gives us our CSV table with all our data.
Finally, we can add the CSV table to a Send an email action as an attachment.
And below, the CSV data in the attachment in the email.
Hi @alht,
Yes, unfortunately sometimes that happens in the interface. In that case I would remove that nested apply to each 2 and try and configure it again.
In the second attempt try and use expressions for the Select action fields instead of selecting the fields from the Dynamic content list. This should prevent the flow from creating a nested loop.
Use for the From:
items('Apply_to_each')?['AssignedTo']
Use for the Map:
item()?['DisplayName']
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
