cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alht
Frequent Visitor

Expression to Get DisplayName from SharePoint People Picker

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","Dep...Finance","JobTitle":"Pencil Pusher"}]

 

Is there a way to create an expression that only takes the Display Name (EG: Split function)

 

alht_0-1670317181682.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
grantjenkins
Community Champion
Community Champion

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.

grantjenkins_0-1670371226813.png

 

See full flow below. I'll go into each of the actions.

grantjenkins_1-1670371298094.png

 

Get items retrieves all the items from our list.

grantjenkins_2-1670371325451.png

 

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'], '}}')))

grantjenkins_3-1670371325280.png

 

Below is a sample of what the list data would look like in XML.

grantjenkins_4-1670371325552.png

 

Create CSV table uses XPath expressions to build up our table.

grantjenkins_5-1670371369298.png

 

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.

grantjenkins_6-1670371467048.png

 

And below, the CSV data in the attachment in the email.

grantjenkins_7-1670371555228.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

11 REPLIES 11
alht
Frequent Visitor

I have tried parsing this with the JSON Parser but I keep getting an error:

 

Error: Expected Object but got an Array

 

alht_1-1670318901968.png

 

I have tried to change the data types but I cannot find the correct setup?

Expiscornovus
Super User
Super User

Hi @alht,

 

Can you try the below expression in the Select action for the Assigned To field value?

item()?['AssignedTo']['DisplayName']

 

Btw, does your Assigned To field allow multiple people to be assigned to one item?



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


alht
Frequent Visitor

 

@Expiscornovus I get the below error when I add the expression you suggested.

 

 

 

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "ProjectName": "@item()?['Title']", "Category": "@item()?['Category/Value']", "Status": "@item()?['Progress/Value']", "Start Date": "@item()?['StartDate0']", "Due Date": "@item()?['DueDate0']", "Assigned to": "@item()['AssignedTo']['DisplayName']", "Ownership": "@item()?['Ownership/Value']", "Purpose": "@item()?['Classification/Value']", "Assignment type": "@item()?['Assignmenttype/Value']", "Complexity": "@item()?['Priority']", "Partner": "@item()?['Partner/Value']", "Cost tDKK": "@item()?['CosttDKK']", "Modified": "@item()?['Modified']", "Created": "@item()?['Created']", "Duration": "@item()?['Duration1']" }' failed: 'The template language expression 'item()['AssignedTo']['DisplayName']' cannot be evaluated because property 'DisplayName' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

 

alht_0-1670324185612.png

 

I presume the error arises due to the fact that there are multiple items.

 

Yes, we do have allow multiple values set... 

 

Is there also another expression that can be used for multiple entries or does this have to go through a JSON Parser?

 

Thanks for the response! 🙂

Expiscornovus
Super User
Super User

Hi @alht,

 

In that case the expression will not work unless you add an index. But that is not the best approach

item()?['AssignedTo'][0]['DisplayName']

 

You could also use a temporary array and use a Select to get the list of assigned to user display names. With a join you can put it together as a comma separated string.

 

Below is an example

 

1. Add a temp array of type array with an Initialize variable action

 

2. Use a Select within the Apply to Each.

 

3. Use the Assigned To field in the From

 

4. Switch the map field to text mode and only use the Assigned To DisplayName field.

 

5. Add an append to array variable and use the json below

 

{
"Title": @{items('Apply_to_each')?['Title']},
"Assigned To": @{join(body('Select'), ',')}
}

 

temparray_selectbodyjoin.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Thanks again for the help... As soon as I add the "Assign to DisplayName" to the Map field It adds a new "Apply for each" loop.

 

alht_0-1670327828575.png

Then if I try to save the flow

 

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Create_CSV_table' at line '1 and column '2212' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Create_CSV_table' to be referenced by 'repeatItems' or 'items' functions.'.'.

Expiscornovus
Super User
Super User

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']

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


grantjenkins
Community Champion
Community Champion

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.

grantjenkins_0-1670371226813.png

 

See full flow below. I'll go into each of the actions.

grantjenkins_1-1670371298094.png

 

Get items retrieves all the items from our list.

grantjenkins_2-1670371325451.png

 

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'], '}}')))

grantjenkins_3-1670371325280.png

 

Below is a sample of what the list data would look like in XML.

grantjenkins_4-1670371325552.png

 

Create CSV table uses XPath expressions to build up our table.

grantjenkins_5-1670371369298.png

 

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.

grantjenkins_6-1670371467048.png

 

And below, the CSV data in the attachment in the email.

grantjenkins_7-1670371555228.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

@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()')

 

You would just need to wrap it within string(...) to get the actual Display Name value.

 

xpath(item(), 'string(//AssignedTo/DisplayName/text())')

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
alht
Frequent Visitor

@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!

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_0-1670416145612.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,499)