Trigger email flow when sharepoint list date is today

(0) Share

Report

Posted on by Kelley

I have a SharePoint list that has 3 different date fields and a Choice field. The date fields are

1. "Letter Sent Date" (Date and Time column - manually updated with each entry)

2. "3 months from 'Letter Sent Date' column" {Calculated column: =DATE(YEAR([Letter Sent Date]),MONTH([Letter Sent Date])+month,DAY([Letter Sent Date]))}

3. "Fortnight before 3 months from 'Letter Sent Date' column" {Calculated column: =[3 Month date]-(7*2)}

4. Choice field "Decision for Re-registering" (Choices are Yes, No)

I would like a flow to trigger if the choice field is Blank (null) AND the date within the "Fortnight before 3 months from 'Letter Sent Date' column" {Calculated column: =[3 Month date]-(7*2)}" is today's actual date.

I would like an email to be generated and sent at around 9am if both criteria is met. I am in Brisbane Australia time zone.

Any help would be appreciated. 🙂

All responses (15)

Answers (2)

v-yujincui-msft on at

Like (1)

Report

Re: Trigger email flow when sharepoint list date is today

Hi @Kelley ,

I am glad to see that you solved the problem, and I also thank you for your recognition and sincere reply. 🙂

Best Regards,
Charlie Choi
Kelley 19 on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Dear Charlie,
It has worked!!! Thank you
Thank you for your patience, thank you for your time and thank you for being attentive to solving my problem.
You have assisted me greatly and I am forever grateful.
Is there a way I can send your manager/leader an email to pass on my feedback? You truly deserve to be recognised for what you have done.
Thank you Charlie... really and truly thank you
Verified answer

v-yujincui-msft on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi @Kelley ,

You should use the internal name of your List's column.

Best Regards,
Charlie Choi
Kelley 19 on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi Charlie,
We seem to be getting closer...
Array Filter 4 now has data within the output. Hooray! However....

It failed at the HTML table.
Here are screen shots of the HTML table- showing each expression.

The error code talks about "addhours".

Please don't give up on me and this issue now. We are nearly there 🙂
thank you thank you thank you 🙂
v-yujincui-msft on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi @Kelley ,

Is that the right logic?
‘Fortnightbefore3monthsfromLetter’<=utcnow()<=’3monthsfromLetterSentDate’

It seems that there are no eligible records in your data source.
Try adding a new record with a 'LetterSentDate' column value of 28/10/2021 and run Flow again.

Best Regards,
Charlie Choi
Kelley 19 on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi Charlie,

Yes there is data within the SharePoint list.
thank you 🙂
v-yujincui-msft on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi @Kelley ,

Could you please double check your data source? It seems that the data source has no eligible records.

Best Regards,
Charlie Choi
Kelley 19 on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi Charlie,
I have added the 'Send an email(V2) Action into the If yes branch of the Condition'.
The Filter array 4 is still empty. []
What am I doing wrong?
v-yujincui-msft on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi @Kelley ,

Yes, you should select the Body of Filter array 4 in the 'Create HTML table' action.
Have you checked the outputs of Filter array 4? Is it empty?
Try putting the Send an email(V2) Action into the If yes branch of the Condition.

Best Regards,
Charlie Choi
Kelley 19 on at

Like (0)

Report

Re: Trigger email flow when sharepoint list date is today

Hi Charlie,
I am most grateful for your assistance, but the email is still coming through without the table. See below.
Here is the flow at a high level.
Here are the filter arrays
this is the full expression/condition in the Filter Array 4
@And(lessOrEquals(item()?['OData__x0031_4_x0020_days_x0020_before'], utcNow()),greaterOrEquals(item()?['OData__x0033__x0020_MONTH_x0020_DATE'], utcNow()),equals(item()?['Re_x002d_Registering_x0020_Compa/Value'], null))

This is the condition:

Which 'Body' do I select in the 'Create HTML table' - from which filter Array? Currently it is Filter array 4

May I please trouble you for your assistance. thank you very much 🙂