Trigger email flow when sharepoint list date is today

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

[v-yujincui-msft · Accepted solution]

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

[Kelley]

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? 

[v-yujincui-msft]

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

[Kelley]

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

[v-yujincui-msft]

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

[Kelley]

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 🙂

[v-yujincui-msft]

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]

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]

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]

Hi Charlie,

Yes there is data within the SharePoint list.

thank you 🙂

[v-yujincui-msft]

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]

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 · Accepted solution]

Hi @Kelley ,

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

Best Regards,

Charlie Choi

[Kelley]

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

[v-yujincui-msft]

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