Send a mail based on a filtered list item date value

[Lino]

Hello, i am trying to do this exact same thing than here with a column that is called "Due Date" in my sharepoint list. But i cannot seem to make it work. 

I am looking to send an email 16 days before Due Date.

The date format in my sharepoint list is: 'dd/MM/yyyy'

I find it wierd that my Due Date isnt found as Dynamic content. Can the mistake be there?
Otherwise i have also seen that in picture p2, in the parameter tab on the left, its written "$filter": "Due Date eq '03/27/2024'.

This is 03/27/2024 is basicly todays date minus the 16 days.
But this is not what i am looking for. I am looking that "if today date is 16 days before due date, then send an email".

Thank you in advance

[DJ_Jamba]

Hi @Lino 

1. Could you try your tests in the classic designer first? The new designer is buggy.

2. You need the SP internal name for the "Due Date" column. "Due Date" is the display name. Go to list settings in SP, click on the "Due Date" column link. The internal name for that column is at the end of the URL e.g.

3. Try changing the Filter Query field to:

addDays(utcNow(),-16,'yyyy-MM-dd')

 Additionally - double check that the "Due Date" column is not a calculated column

[Lino]

Hello @DJ_Jamba 

I tried everything you said.
But before I start, I just want to correct two minor things.
- My column is not Due Date but is called Date

- The date format of this column in SharePoint List is m/d/yyyy  (see p1)

- The list in which the column is found is not called "A" but "AA"
I want to clarify this so that the screenshots i attach are not confusing.
So back to what you suggested:
1. I switched to classic mode

2. I looked for the internal name of the column and not the display name:

        ....879675%7D&Field=Date        --> Follow up question:  is the correct name then "Date"?

3. I tried to change the format inside the addDays function from 
         --> in the function I do not understand if the format I need to insert as an argument is the format I want or the format of the input (in this case, the format of my Date column in my SharePoint list). If the Date column is format as in p1 how should I write it in the function?

Thank you in advance for your help and patience!

[DJ_Jamba · Accepted solution]

@Lino 

 - Yes the correct name to use is Date
 - It doesn't matter what format SharePoint is displaying, it is inheriting the Display settings for dates. Behind the scenes it is always stored e.g. 2024-04-16T16:10:27.000Z 

   So you should always pass the date in yyyy-MM-dd format. Why? If you pass a date like this 04/01/2024 there is ambiguity, meaning it could either be 1st April 2024 or 4th Jan 2024. Whereas there is no mistake ever with 2024-04-01.

In your second screenshot, the query filter is incorrect. You can't use mathematical operators >, >= < etc
You have to use oData operators e.g 
le = less than or equal to

lt = less than

ge = greater than or equal to

and so on...

So the filter query for your 2nd screenshot in Get items should be:

Date lt '2024-04-11'

[DJ_Jamba]

Glad it's sorted!

d(-_-)b

[Lino]

Thank you @DJ_Jamba !   😁