Scheduled flow to send SP list as table by email

(0) Share

Report

Posted on by PhilGlew

Hi,

I have built a flow whereby, on a schedule, the entries in a SP list are sent via email.

The flow steps currently are:

Get Items (from the SP list)

Select (from the SP list, and Map each column's value to a key)

Create HTML table (using output from the Select action)

Send from a shared mailbox.

Some columns, however, don't return the value in the SP list, but return more - they are columns which are Choices and the person is selected from the O365 directory.

How can I change it so that the email received just displays those people's names in these columns? The column titles are called "Developer" and "Stakeholder(s)"

Many thanks

Phil

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

ScottShearer 25,268 Most Valuable Professional on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

@PhilGlew

Are the columns that you are referring to Person or Group columns or choice columns? What type of values are you seeing?

Was this reply helpful? Yes No
PhilGlew 93 on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

They are Person or Group columns.

The output in the email is as below (redacted)...

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|person's email","DisplayName":"Person's Name","Email":"Person's Email","Picture":"Image URL","Department":"Dept Name","JobTitle":"Person's Job Title"}]

I just want the DisplayName to be in the email output.

Thanks
Phil

Was this reply helpful? Yes No
ScottShearer 25,268 Most Valuable Professional on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

@PhilGlew

See my example below and let me know if you have any questions. In my example, PG is the name of a person or group column.

Also, you can eliminate the need for your select by creating a SharePoint view that contains only the columns that you want. Then, select that view in your Get items action - go to advanced options and then "Limit columns by view".

Was this reply helpful? Yes No
PhilGlew 93 on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

Thanks but what I think is needed is an OData expression in the Get Items action on just the Developer and Stakeholders columns to return just the DisplayName and not the long string. I need all columns to appear in the email.

Any ideas?

Thanks
Phil

Was this reply helpful? Yes No
ScottShearer 25,268 Most Valuable Professional on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

@PhilGlew

Using an OData filter will filter the rows returned but will not filter the columns.

Sorry, but it sounds like I am a bit confused on your requirements.

First, using my example for building the table will eliminate the issue with showing just the email.

If you want to filter the rows returned by Get items then yes, you want an OData filter.

If you can tell me the criteria you want to use for filtering the rows I can help you construct the Filter query.

If you want to limit the columns returned, then my suggestion about creating a view with just those columns will work for you.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Re: Scheduled flow to send SP list as table by email

In the select action you want something like:

item()['personColumnName']['DisplayName']

Was this reply helpful? Yes No
PhilGlew 93 on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

Sorry Scott, thank you for your help... hopefully this helps you.

All the below works and I get a table on an email with the values from Sharepoint, other than for the Developer and Stakeholder columns where the email output I get is as I've put... (see below). I want it so that the Developer and Stakeholder columns display just the DisplayName (there is no option in Dyanmic Content for "Developer DisplayName" though). 😪

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|person's email","DisplayName":"Person's Name","Email":"Person's Email","Picture":"Image URL","Department":"Dept Name","JobTitle":"Person's Job Title"}]

Was this reply helpful? Yes No
PhilGlew 93 on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

Thanks Paulie. When I put this in as an expression, however, the flow fails as the Person or Group is an array. This is the error I get...

'The template language expression 'item()['Developer']['DisplayName']' cannot be evaluated because property 'DisplayName' cannot be selected. Array elements can only be selected using an integer index.

Was this reply helpful? Yes No
Verified answer

Paulie78 8,422 Moderator on at

Like (1)

Report
Re: Scheduled flow to send SP list as table by email

Yes, my mistake. This expression in your select should work:

join(xpath(xml(json(concat('{ "Root": { "Stakeholders": ', item()['Stakeholders'], '}}'))), '//Root/*/DisplayName/text()'), ';')

As it is an array it needs the join expression applied. So I used ; as a separator. You will need to change item()['Stakeholders'] to match whatever your column name is (the internal column name).

Was this reply helpful? Yes No
PhilGlew 93 on at

Like (0)

Report

Re: Scheduled flow to send SP list as table by email

Thank you Paulie. This worked for the Developer column, but just won't work for the Stakeholder column... The flow fails as below... Any ideas?

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"ID": "@item()?['Title']",
"Title": "@item()?['Title0']",
"Owner": "@item()?['Owner']",
"Developer": "@join(xpath(xml(json(concat('{ \"Root\": { \"Developer\": ', item()['Developer'], '}}'))), '//Root/*/DisplayName/text()'), ';')",
"Description": "@item()?['Description']",
"Stakeholder": "@join(xpath(xml(json(concat('{ \"Root\": { \"Stakeholder\": ', item()['Stakeholder'], '}}'))), '//Root/*/DisplayName/text()'), ';')",
"Due Date": "@item()?['DueDate']",
"Status": "@item()?['Status/Value']",
"Phase": "@item()?['Phase/Value']",
"Priority": "@item()?['Priority/Value']",
"Module": "@item()?['Module/Value']"
}' failed: 'The template language expression 'join(xpath(xml(json(concat('{ "Root": { "Stakeholder": ', item()['Stakeholder'], '}}'))), '//Root/*/DisplayName/text()'), ';')' cannot be evaluated because property 'Stakeholder' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title, Title0, Owner, Developer, Developer@odata.type, Developer#Claims, Developer#Claims@odata.type, Stakeholder_x0028_s_x0029_, Stakeholder_x0028_s_x0029_@odata.type, Stakeholder_x0028_s_x0029_#Claims, Stakeholder_x0028_s_x0029_#Claims@odata.type, DueDate, Status, Status#Id, Phase, Phase#Id, Priority, Priority#Id, Module, Module#Id, {Identifier}, {IsFolder}, {Thumbnail}, {Link}, {Name}, {FilenameWithExtension}, {Path}, {FullPath}, {HasAttachments}, {VersionNumber}'. Please see https://aka.ms/logicexpressions for usage details.'.

Was this reply helpful? Yes No