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. 🙂

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (2)

Verified answer

v-yujincui-msft on at

Like (1)

Report
Hi@Kelley,

Do you want to check whether there are eligible records regularly and decide whether to send emails based on the results?
I have made a test for your reference.

1.Here is my SharePoint list.

2.Create a flow.

@and(lessOrEquals(item()?['Fortnightbefore3monthsfromLetter'], utcNow()),greaterOrEquals(item()?['OData__x0033_monthsfromLetterSentDate'], utcNow()),equals(item()?['DecisionforReregisterin/Value'], null))
item()?['Title']
formatDateTime(addHours(item()?['LetterSentDate'],10),'MM/dd/yyyy mm:ss')
formatDateTime(addHours(item()?['OData__x0033_monthsfromLetterSentDate'],10),'MM/dd/yyyy mm:ss')
formatDateTime(addHours(item()?['Fortnightbefore3monthsfromLetter'],10),'MM/dd/yyyy mm:ss')
item()?['DecisionforReregisterin/Value']

Combine the conditions in the previous three Filter Array Actions into one Action, you can click the Edit in advanced mode, and write expressions in it.

Result Screenshot:

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
Kelley 19 on at

Like (0)

Report

Hi Charlie,

thank you for providing a reply to me. I have been away on leave and only returned today. I have completed the actions as outlined below and an email has triggered however there were no items within the list in the email. (the sharepoint list has a few dummy items).
What are the reasons an item would be in the HTML list?

Was this reply helpful? Yes No
v-yujincui-msft on at

Like (0)

Report

Hi @Kelley ,

I noticed that there is no data in your HTML table, could you please check the outputs of the Filter Array Action and the Create HTML table Action and see if there are values there? Maybe this is the reason for the problem.

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
Kelley 19 on at

Like (0)

Report

Hi Charlie,
There appears to be information within the Filter array 4 but not within the Create HTML table. (see below).

Here is what I have added into the Filter Arrays/COnditions and Create HTML table (as well as the outputs below).

Was this reply helpful? Yes No
v-yujincui-msft on at

Like (0)

Report
Hi @Kelley ，

1. According to the screenshots you provided, I found that the outputs of your Filter Array 4 is empty.
2. Could you please check the outputs of Filter Array 3 to see if the dynamic content you have selected is correct, since in this Action you are using the Choice column?
3.In addition, I found two filling errors in your Create Html table Action. You should write the expression in the Expression tab.
Note: Use the internal name of the field.
item()?['Title']
item()?['Re_x002d_Registering_x0020_Compa/Value']

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
Kelley 19 on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No
v-yujincui-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
Kelley 19 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-yujincui-msft on at

Like (0)

Report

Hi @Kelley ,

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

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
Kelley 19 on at

Like (0)

Report

Hi Charlie,

Yes there is data within the SharePoint list.
thank you 🙂

Was this reply helpful? Yes No