Create an automated Email Notification based on a Date in SharePoint List

[pmasters]

Hi All,

I am new to Powered Automate and I'm attempting to create a scheduled/automated flow which sends an email notification to an email address maintained in a SharePoint List based on the date in SharePoint List. My SharePoint list contains the following columns and is a list of 30+ projects (one project per list row):

• Client Contact (email address of the individual who should receive the email notification)
• Indicative state date (date formatted as MM-dd-yyyy and all dates in the SharePoint list is populated and are not blank_ - See below for how the column is set up.

I would like an email to be automatically sent out to the 'Client Contact' where the current date is 20 days prior to the 'Indicative Start Date'.  Essentially a reminder to the 'Client contact' of the upcoming date.

I have tried a number of options to create the flow but continue to get 2 different types of errors at the "Apply to each" stage.

1) "InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'."

2) Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'In function 'addDays', the value provided for date time string 'IndicativeStartDate' was not valid. The datetime string must match ISO 8601 format.

My current flow condition is set up as follows:

utcNow() is eaual to addDays(formatDateTime(triggerBody()?['IndicativeStartDate'], 'dd-MM-yyyy'),-20,'dd-MM-yyyy')

I have also used the following:

formatDateTime(item()['IndicativeStartDate'],'yyyy-MM-dd') is equal to addDays(utcNow(),+20,'yyyy-MM-dd')

Please tell me how I can fix this issue? Thank you in advance.

[rzaneti]

Hi @pmasters ,

The error suggests that your column have an empty value. However, you mentioned that there are no empty values in the 'IndicativeStartDate' column. So, it is possible that your column logical name is different from 'IndicativeStartDate'.

To we confirm if this is the case, please share the raw outputs from your trigger. You can follow the steps below to find the raw outputs:

First, go to the 'flow details' page and click in one of the previous runs:

Once you do it, you will be in the 'flow run page', which may look like this:

Go to your trigger and click in it to expand the details. Then click in "Show raw outputs" in the OUTPUT section. In some cases, this button appears as 'Click to download':

It will display a sidebar on the right side of your screen containing this JSON notation (if you had the 'click to download' option, it will open the outputs in a new tab):

Find the "body" property. It is always after the "header" property. Inside the 'body' property, you will see another property called 'value', and this is what we need (a sample is fine, you don't need to share the whole body/value content). 

Make sure to remove any sensitive content from the image before sharing. You also can find more details about how to access the outputs in Power Automate in this article that I wrote recently: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/

[pmasters]

Thanks @rzaneti . Since posting this topic I have changed the configuration of the flow and have received successful test runs and runs. See screenshot below.  The flow condition is set up as follows:

formatDateTime(item()['IndicativeStartDate'],'yyyy-MM-dd') is equal to addDays(utcNow(),+1,'dd-MM-yyyy')

Note: "Indicative Start Date" in the SharePoint list is formatted as MM-dd-yyyy so not sure if this matters for the above condition.

Despite the run being successful for this condition, I didn't receive any email notifications on a test. To perform the test, I had amended one of the project's "Indicative Start Date" to (for example) 08/18/2023 (US format) and ran the flow on the 17th August (yesterday) and did not receive any email notification - looking at the results I can see that the result was "false". The condition should send an email notification where the "Indicative Start Date" is equal to +1 days from utcNow() (or today's date). In this test, the Indicative Start Date was 18/08 in the SharePoint list and thus +1 days after 17/08 and should have worked. Screenshots below.

The below screenshot is from the SharePoint list. The "Lead" is formatted as the "Person or Group" format type so it links to our company directory where we can search for an employee/colleague for input into the list. The "Indicative Start Date" shows 08/18/2023 as noted earlier.

What am I doing wrong or what needs to change?

Re your previous request, see below for the screenshot of the raw output. Not sure if I included sufficient information as requested.

[rzaneti]

Hi @pmasters ,

I'm happy to hear that you got some progress with the task!! 

About your current issue, I believe that the problem is in how you formatted the dates.

You used the following expression: formatDateTime(item()['IndicativeStartDate'],'yyyy-MM-dd') is equal to addDays(utcNow(),+1,'dd-MM-yyyy')

I just ran a test into a condition to compare if formatDateTime(utcNow(), 'yyyy-MM-dd') is equal to formatDateTime(utcNow(), 'yyyy-dd-MM'), and the result was false. So my suggestion is to you follow the same date format for both sides of your condition.

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/