cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Email list contents weekly

We are trying to create an automation that emails the contents of a list's open items on  weekly basis.  We have this pretty close to working via create HTML table but we're trying to limit what's emailed as right now the entire list contents (including completed items) are included. We just want items with a status of notified or in progress (these are fields we've created). Also, the time is formatted to UTC time and are trying to change it to Eastern Standard Time.  I've tried the convertTimeZone option and that works but it applies to each item for all items at once like the following.

Annotation 2020-02-11 113510.png

I'm fairly certain it's a setting but am not sure where.  Any insight is appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

If you are storing the data within SharePoint you can use the Get items action and within the Filter Query field (advanced option) set it to:

 

[fieldname] eq 'Open'

 

this will ensure it only returns open items (if you do not have a column stating Open or Closed you can adjust the filter as needed.

 

Annotation 2020-02-11 171638.png

 

Alternatively, you can use filter array with the same principle - just remember to use the filter array values within the From field for Create HTML table

 

Annotation 2020-02-11 171649.png

 

Hope this helps!

 

Thanks

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more

View solution in original post

Highlighted
Regular Visitor

Re: Email list contents weekly

The Date Submitted value didn't work however looking closely at the error it's related to the field names.  Instead of Date Submitted, Flow wanted DateSubmitted as the field name.  For the Due Date, it was actually looking for Date_x0020_Submitted.  I'm not sure where it came up with that field but it's working great now.  Thanks so much for you help, persistence and pointing me the right direction.

 

convertTimeZone(item()?['Due_x0020_Date'],'UTC','Eastern Standard Time','m')

View solution in original post

11 REPLIES 11
Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

If you are storing the data within SharePoint you can use the Get items action and within the Filter Query field (advanced option) set it to:

 

[fieldname] eq 'Open'

 

this will ensure it only returns open items (if you do not have a column stating Open or Closed you can adjust the filter as needed.

 

Annotation 2020-02-11 171638.png

 

Alternatively, you can use filter array with the same principle - just remember to use the filter array values within the From field for Create HTML table

 

Annotation 2020-02-11 171649.png

 

Hope this helps!

 

Thanks

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more

View solution in original post

Highlighted
Community Support
Community Support

Re: Email list contents weekly

 

Hi @mauimike ,

 

What is the type of the Status field? If it is Choice, then you need to use Status Value as the filter.

And it cannot be configured in Filter Query.

 

Please consider sharing screenshots of the current configuration so that we can provide you with the appropriate workaround.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Email list contents weekly

That was exactly what I was looking for.  Any idea how to get the time zone converted properly?  The convert time zone option works but it's 'Applying to each' and results in the time zone for each added to all entries.

Annotation 2020-02-12 110915.jpg

Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

You can do the below expression

 

convertTimeZone(triggerBody()?['Due Date'],'UTC','Eastern Standard Time','HH:mm')

 

Hopefully this helps 🙂

 

Kind regards

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more
Highlighted
Regular Visitor

Re: Email list contents weekly

That seems to be the right expression but it gives the following error:

The execution of template action 'Create_HTML_table' failed. The column values could not be evaluated: 'The template language function 'convertTimeZone' expects its first parameter to be a string that contains the time. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#ConvertTimeZone for usage details.'.

 

Annotation 2020-02-12 114122.jpg

Due Date is a calculated field (it adds 7 days after the submission date) and is populated.

Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

Do all the items in the list have a Submission Date? If not, how is your calculated column processing that?

 

Thanks

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more
Highlighted
Regular Visitor

Re: Email list contents weekly

Yes, they all have a submission date.  The due date is a calculated field in the list with the following formula:

 

=[Date Submitted]+[Priority Level]

Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

I just spotted in your screenshot that you are referencing the trigger body instead of the get items - my mistake!

 

convertTimeZone(item()?['DueDate'],'UTC','Eastern Standard Time','HH:mm')

 

Hopefully this resolved it!

 

Thanks

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more
Highlighted
Regular Visitor

Re: Email list contents weekly

Hmmm.  Same problem...The template language function 'convertTimeZone' expects its first parameter to be a string that contains the time. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#ConvertTimeZone for usage details.'.

Highlighted
Super User
Super User

Re: Email list contents weekly

Hello @mauimike 

 

Could you reference the column Date Submitted, just to see if that works.

 

If that works, then it would be how the flow is interpreting the calculated column.

 

Should that be the case, you would amend the expression so that you are adding the date submission with the priority level, doing what your calculated column is doing, but within the convert time zone expression.

 

Thanks

 

Bradley

Best regards,

Bradley
If this post helps, then please consider Accept it as the solution to help the other members find it more
Highlighted
Regular Visitor

Re: Email list contents weekly

The Date Submitted value didn't work however looking closely at the error it's related to the field names.  Instead of Date Submitted, Flow wanted DateSubmitted as the field name.  For the Due Date, it was actually looking for Date_x0020_Submitted.  I'm not sure where it came up with that field but it's working great now.  Thanks so much for you help, persistence and pointing me the right direction.

 

convertTimeZone(item()?['Due_x0020_Date'],'UTC','Eastern Standard Time','m')

View solution in original post

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (8,536)