On yesterday filters

[ThomasH]

Hello, I would like to see only yesterday's values, but unfortunately I can't get it to work. I would like to see all the tasks that were completed yesterday in the planner, which I get filtered, but unfortunately not with the restriction yesterday.

I filter whether the task is done like this, 

@equals(item()?['percentComplete'], int(100))

but how can I then still filter on yesterday?
Thanks

[Expiscornovus]

Hi @ThomasH,

You could use the completedDateTime property and use some formatting. After that you can compare it to UtcNow -1 (yesterday).

Below is an example of such an expression.

@and(equals(item()?['percentComplete'], int(100)), equals(formatdatetime(item()?['completedDateTime'], 'yyyy-MM-dd'), addDays(utcNow(),-1, 'yyyy-MM-dd')))

[ThomasH]

Hi @Expiscornovus 

Hello, thank you for your example and the short explanation, unfortunately my expression knowledge is rather poor, if I copy your example code, it doesn't work. Can you explain to me in more detail what the example does or how I could solve the problem.
Thanks

[Expiscornovus · Accepted solution]

Hi @ThomasH,

Just to double check. You had some tasks in your planner which have been 100% completed yesterday (2022-09-01), correct? If there aren't any the outcome of [] is expected. 

To explain the expression:

I used an and to add the second part of the condition. Both expressions should result in true in order to get a planner item back.

and(<expression1>, <expression2>)

That second expression is :

equals(formatdatetime(item()?['completedDateTime'], 'yyyy-MM-dd'), addDays(utcNow(),-1, 'yyyy-MM-dd'))

In that second expression I am comparing the completedDateTime of the item with yesterday. They need to be exactly the same.

So, for today that means the completedDateTime needs to be equal to 2022-09-01.

Both will be formatted into the same date time format, yyyy-MM-dd. For item()?['completedDateTime'] I am using a formatDateTime function.

formatdatetime(item()?['completedDateTime'], 'yyyy-MM-dd')

Yesterday is retrieved by using UtcNow as the timestamp and using an adddays function to calculate yesterday. 

addDays('<timestamp>', <days>, '<format>'?)

When using -1, you subtract 1 day.

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

Hope this makes a bit more sense.

[ThomasH]

@Expiscornovus 

Hello, thanks again for the hint, in this case I actually didn't have a completed task the day before.... It now works exactly as it should, if there is a completed task the day before, then I get it by email on the current day Overview sent as HTML table. Thank you for the detailed explanation, not only did I solve my problem, but I also learned something.

[ThomasH]

@Expiscornovus 

Hi again, I just noticed in this context that I also receive an empty email if there are no tasks with "Completed" the day before, how could I check that? My idea would be to add a condition after array filtering in the form of "If value = "" then do that, otherwise something else" But how can I add that there?

Thank you

[Expiscornovus · Accepted solution]

Hi @ThomasH,

You could check if the Array filtern Completed action outputs is empty with a condition action.

With a length function you could check this. If it is not equal to 0 you are good to go to send your e-mail.

Below is an example of such an approach.

length(body('Array_filtern_Completed'))

[ThomasH]

@Expiscornovus 

That's simple and work great! Thank you very much for your help