cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PMBA
New Member

how do I format data from SharePoint lookup fields?

I'm building a flow to report out of a SharePoint list on-demand and email an HTML Table to a set of recipients. Several of the fields I need to include in the Table are lookup fields to other lists within the SharePoint site.    i.e. "Process Name" which goes to the Process list and pulls the [Title] column.  

PMBA_1-1617900117035.png

 

When I pull in items and embed the HTML table into an email I get back code for the lookup column 

 

PMBA_2-1617900585114.png

how can I "clean this up" so it's only showing the "process name" ? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User II
Super User II

Hello @PMBA ,

since it's in the 'Create html table' action processing a multiple choice column, you'll need a fancy combination of expressions to take only 'Value' from all choices in the column and convert them into a string.

join(xpath(xml(json(concat('{"body":{"value":', item()?['Choice'], '}}'))), '/body/value/Value/text()'), ', ')

Note:
replace Choice with your column internal name

 I wrote a post on building the expression if you're interested, it's a bit complex to explain it directly in each post.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

1 REPLY 1
tom_riha
Super User II
Super User II

Hello @PMBA ,

since it's in the 'Create html table' action processing a multiple choice column, you'll need a fancy combination of expressions to take only 'Value' from all choices in the column and convert them into a string.

join(xpath(xml(json(concat('{"body":{"value":', item()?['Choice'], '}}'))), '/body/value/Value/text()'), ', ')

Note:
replace Choice with your column internal name

 I wrote a post on building the expression if you're interested, it's a bit complex to explain it directly in each post.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (21,875)