Send reminder email when task is not 100% and today() is greater than target date

[diegoandre10]

Hi all

This is my first time using flow. I need to create a flow that connects to a sharepoint list. Basically a reminder email has to be sent to the responsible of the task when 2 conditions are met:

1) Today date is greater or equal to the target for completion date. 

2) the % of completion column of the specific task is not 100%

The email has to be sent to the responsible and myself to know who is late on closing the task. 

Currently I am here, I found on another post to use the expression https://powerusers.microsoft.com/t5/Building-Flows/past-due-reminder/m-p/49048#M4472: 

@less(formatDateTime(item()?['Due_x0020_Date'],'yyyyMMdd'), utcnow('yyyyMMdd'))

When I type that I get an error:

Error:

When I hit ok, it says the expression is invalid.

Thanks.

[diegoandre10]

Hi all

This is my first time using flow. I need to create a flow that connects to a sharepoint list. Basically a reminder email has to be sent to the responsible of the task when 2 conditions are met:

1) Today date is greater or equal to the target for completion date. 

2) the % of completion column of the specific task is not 100%

The email has to be sent to the responsible and myself to know who is late on closing the task. 

Currently I am here, I found on another post to use the expression https://powerusers.microsoft.com/t5/Building-Flows/past-due-reminder/m-p/49048#M4472: 

@less(formatDateTime(item()?['Due_x0020_Date'],'yyyyMMdd'), utcnow('yyyyMMdd'))

When I type that I get an error:

Error:

When I hit ok, it says the expression is invalid.

Thanks.

[abm]

Hi @diegoandre10 

Try this without the @ sign at the beginning.

less(formatDateTime(item()?['Due_x0020_Date'],'yyyyMMdd'), utcnow('yyyyMMdd'))

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

Thanks

[v-bacao-msft]

Hi @diegoandre10 ,

Please try to refer to following method to configure Flow.

Hope it helps.

Best Regards,

[diegoandre10]

Hi !

Thanks for your reply and help I really appreciate it.

The name of the column that has the due date is called "Target for completion date (original)", my question is, should I change the code you told me and place the name of the column within?

To understand the code, 'Due_x0020_Date' means a standard or is the name of the column?

less(formatDateTime(item()?['Due_x0020_Date'],'yyyyMMdd'), utcnow('yyyyMMdd'))

Thank you!

[abm]

I was just focusing on the error you were getting initially. Yes you need to replace the column name. Let me know how it goes.
Thanks

[v-bacao-msft]

Hi @diegoandre10 ,

I thought that the list you mentioned is Tasks list, it seems that it is just a normal SharePoint List.

For a comparison of the date you could refer to the following configuration:

Expression reference:

formatDateTime(item()['DueDate'],'yyyy-MM-dd')

The important thing to note here is that you need to replace the DueDate. The field name here can be obtained from Get items-Body. You could run Flow once and look at the Body of Get items.

Please take a try.

Best Regards,

[diegoandre10]

 hi Bacao, I was folllowing your initial solution.

Let me start again, this is a test sharepoint list:

I added a new condition, I should receive a message for being late when: % of completion is less to 100% And due date is less or equal to today. The new condition is also send an email when second taget for completion date is late compared to today or empty .

On the first row we are late so a message should be sent. On the second one eventhough I am late, I inserted a second completion date, so no message should be sent, on the 3rd one I am on time.

What I do is:

When I run it, I get an email saying test2 is late, eventhough it shouldnt.

I saw you used a variable assigned to, but honestly I dont know what that is for.

Thanks for the help!

[v-bacao-msft]

Hi @diegoandre10 ,

Please ignore my previous reply, which is the solution provided for Tasks list, not for your current situation.

You could still follow your previous configuration, but at the date of comparison, you could refer to the content of my last reply.

And note that when configuring expression, it is not directly filled in, you need to edit it in Expression first.

Based on the information you provided, it seems that you don't need to use formatDateTime() function to modify Target for completion date field, you could use it directly.

Best Regards,

[diegoandre10]

Hi

You mean the expression like this? It turned purple.

I still got an error, it says: 

ActionFailed. An action failed. No dependent actions succeeded.

Is there any way I can share my flow with you?

Thanks!

[v-bacao-msft]

Hi @diegoandre10 ,

Which action failed? Please provide more details about error message.

Best Regards,

[diegoandre10]

Hi @v-bacao-msft 

#1 Gave me an error

#2 was true,  but should be false

#3 error

Thanks!

[diegoandre10]

Hi abm

Thanks for following back, I am redefining the flow to make it easier for me to understand. But it gives me an error.

Let me know if you have a suggestion on what could be wrong.

Regards.

[v-bacao-msft]

Hi @diegoandre10 ,

The reason for the error I think is that some field values are empty, you can get it from the error message.

There are indeed some Date fields in your list that are empty.

Regarding the judgment result is true, you need to check to see if the date value is consistent with the expected. If it is judged to be true, at least prove that the configuration here is logical.

Best Regards,

[diegoandre10]

Hi

I almost have it, 2 out of 4 run succefully. The only problem is with this error that I dont know what it is:

That error was with item number 4 and 1:

I inserted the null as an expression, the error should be in condition 2:

I want to tell the flow, if you are late on original completion date & 100% is not met, go to condition 2. If you are late on the new target for completion date or the new target is blank send an email:

Should I declare a string for null first?

Thanks!

[v-bacao-msft · Accepted solution]

Hi @diegoandre10 ,

Should I declare a string for null first?

Yep, you need to change the two rows in Condition 2. First determine if the Date field value is empty.

Best Regards,

[diegoandre10]

Got it!!

Just for my knowledge, why the order of the 2 rows was the problem? I would have never guessed that was the problem.

Thanks a lot.

[v-bacao-msft]

Hi @diegoandre10 ,

Great!

Here is also judged one by one in the order you set, so first exclude the case where the field value is empty.

Best Regards,

[diegoandre10]

Ok just one final thing, I am getting the test4 as a late project, but it should be on time since it's 100%.

The condition 1 is: you are late if % completion is not equal to 100% and the original target completion date is late.

In this case the 100% is met.

What could be wrong?

Thanks.