cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Impactful Individual
Impactful Individual

Using "apply to each" to compose a single HTML table and email?

Hello,

 

I found a past topic with a similar question that was solved using an OData filter query in the get items action. Unfortunately in my case this isn't relevant.

 

What I'm doing is a SPO Get Files (properties only) action to grab a list of files created since a given time frame, and I want to use the Created time as a coulmn in an HTML table of the list of files. Obvioulsy Created is in UTC timezone and in some horrible machine readable format so you need to perform Convert time zone on it to make it local and human readable.

 

The problem is there's no way perform an action on any dynamic content from the previous Get files action without an automatic Apply to each loop, and this makes a single HTML table and subsequent email for each file, rather than one HTML table and one email with every file in the list.

 

The best I can do is initialize a variable early on, put converted time into the variable during the Apply to each then use that variable outside the Apply to each in an HTML table column. However, this just gives me the converted time of the last document throughout the whole column of the HTML table (which is kind of what you'd expect).

 

I have a sense the workaround will be to add another column to the document library and populate it with converted time using a flow that's triggered on creation of the document, but before I go to such lengths, is there an easier way?

4 REPLIES 4
v-yamao-msft
Community Support
Community Support

Hi @WillPage,

 

Do you want to Get files first, then send all the files at a time in an email?

The Created is in UTC time zone, do you want to format it to a friendly format?

Have you tried to use a function in the action Create HTML table to format the Created date time to a readable format?

I have tried the following flow, please check it for a reference.

For testing, it is triggered by a Button, then Get files (properties only).

In the Create HTML table, customize the columns. For the Created column, enter the following function in the Value field to format the date time:

formatDateTime(item()?['Created'],'yyyy-MM-dd hh:mm:ss')

1.PNG

Please take a try with it on your side.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WillPage
Impactful Individual
Impactful Individual

Hi Mabel,

 

Thanks for replying. 

 

You are correct, I want to send a single HTML table with a list of the files returned by the Get Files (Properties Only) action.

 

I like your suggestion of using the formatDateTime function, but unfortunately it doesn't convert to my local time zone. I appreciate I can probably do an addHours, but daylight savings time changes will make it inaccurate by 1 hour for half the year.

 

I might go with the new column and populate it with Convert Time Zone on creation.

Hi @WillPage,

 

Thanks for updating.

I assume that the function convertTimeZone would be more convenient for your scenario. It will convert a timestamp from the source time zone to the target time zone. You could convert the UTC to your local time zone using this function.

More details for your reference:

ConvertTimeZone

Time Zone IDs

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GabrielStJohn
Microsoft
Microsoft

Hello, @WillPage!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply @v-yamao-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager

- Gabriel
Community Manager
Power Automate | Power Virtual Agents
Super User Program Manager



Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Users online (2,798)