Answered

Sending an email with only certain dates from a list

(2) Share

Report

Posted on by peps984

Hi guys,

I have a list with a unique ID and 3 dates for every item. I'd like to get an email every month with all the dates occurring the next month:

ID	Date 1	Date 2	Date 3
AA	2024-09-16	2025-01-01	2024-10-02
BB	2025-01-01	2025-03-03	2024-03-03
CC	2024-10-05	2025-01-01	2025-01-01

The result should be a table like this:

ID	Date 1	Date 2	Date 3
AA			2024-10-02
CC	2024-10-05

I've tried rephrasing the request to Copilot multiple times and tried myself from blank, but I'm not getting it right. There's always something wrong.

My idea is to filter the items, then put them on an HTML table, and then send it via email.

This is my last attempt, but I'm not really getting anywhere. I don't even really know if this is possible.

Could you please give me at least a hint of what should the right flow to follow be?

Categories:

Business Process Flows

All responses (4)

Answers (1)

peps984 8 on at

Like (0)

Report

Send an email extrating only certain dates from a list

Hi Willpage, thanks for your answer.

Actually, I've already solved it with this method: https://community.powerplatform.com/forums/thread/details/?threadid=d29fc37a-a472-ef11-a671-000d3a8ad43b

I've created 2 identical threads, my bad!!

By the way, I'll try your solution, maybe it could be an improvement!
peps984 8 on at

Like (0)

Report

Sending an email with only certain dates from a list

Wow, it worked perfectly! I've just modified a bit the query to be sure to get only the next month of that year ('yyyy-MM') and added some CSS to format the HTML table.

Thank you for your support!
Suggested answer

WillPage 1,815 on at

Like (0)

Report

Send an email extrating only certain dates from a list

I'm going to assume the internal column names of your date columns are Date1, Date2 and Date3 and your time zone in New Zealand Standard Time (that's mine, use your local time zone).

First add two compose actions. One for the start of next month and one for the end of next month, with the following expressions:

Start of next month:

convertToUTC(startOfMonth(addToTime(convertFromUtc(utcNow(),'New Zealand Standard Time'),1,'Month')),'New Zealand Standard Time')

End of next month

convertToUTC(startOfMonth(addToTime(convertFromUtc(utcNow(),'New Zealand Standard Time'),2,'Month')),'New Zealand Standard Time')

Now put the following filter query in the Get items action:

(Date1 ge 'outputs('Start_of_next_month')' and Date1 lt 'outputs('End_of_next_month')') or (Date2 ge 'outputs('Start_of_next_month')' and Date2 lt 'outputs('End_of_next_month')') or (Date3 ge 'outputs('Start_of_next_month')' and Date3 lt 'outputs('End_of_next_month')')

I hope this helps. If you live in a place where midnight UTC doesn't occur during the business day you might get away without doing the conversion from UTC and back to get the UTC time of the start of the month in your location.
Verified answer

EKarim 9,945 on at

Like (0)

Report
Sending an email with only certain dates from a list

Here is something to try:

getFutureTime(1, 'Month', 'MM')

"Date1": if(equals(formatDateTime(item()?['Date1'], 'MM'), outputs('Compose_Month')), item()?['Date1'], '') "Date2": if(equals(formatDateTime(item()?['Date2'], 'MM'), outputs('Compose_Month')), item()?['Date2'], '') "Date3": if(equals(formatDateTime(item()?['Date3'], 'MM'), outputs('Compose_Month')), item()?['Date3'], '')

When editing the expressions be sure to use the internal column names for your SharePoint columns. See

🔍How to Find the Internal Name of a SharePoint Column – Ellis Karim's Blog for more details.

This is the sample runtime output:

You will probably want to add an OData Filter query to the Get items action and/or use a Filter array action to filter out any blank rows (e.g. BB contains now valid dates).

Ellis

Ellis Karim's Blog – IT doesn't have to be complicated!