AddDays Expression and approval

[Atennantsp]

Hi, 

I want my flow expression to say Get items If the date in the date column is today and if the manager approved column is approved 

please can you view my photos and tell me where I am going wrong? The flow is succeeding but it's not sending the approval when i am testing. 

Thank you! 

[Expiscornovus]

Hi @Atennantsp,

It is very likely that the Date_x003a_ (Date:) includes a timestamp as well. Because you are using an equals (eq) it needs to exactly match. In your comparison you are only using the date (without time).

So, my guess is that it does not exactly match and therefore not returning any items in the Get Items action.

Can you double check what type of value is returned in the Get Items for the Date_x003a_ field?

[Atennantsp]

@Expiscornovus! Thanks for your help with this 🙂 So as far as I can see it is just a date being returned. Here is an example below which shows the date: 

[Expiscornovus]

Hi @Atennantsp,

That is in the list view itself. Sorry, I was talking about the outputs of the Get Items action. Normally you can check that output as well.

To check this output you could temporarily remove that Date: filter and replace it by an ID eq 1 filter, to test with one single item (replace 1 with the actual ID of the item). This way you can see the format of the value of the Date: field.

[Atennantsp]

@Expiscornovus so the output is: 2023-09-08T13:51:01.5450122Z

[Expiscornovus]

Hi @Atennantsp,

Thanks for checking, it does include a timestamp.

In that case try something like the below instead:

I have added two filters which check if the time is greater than or equal (ge) to 00:00 and less than or equal (le) to 23:59, which should cover the whole day.

(Date_x003a_ ge '@{addDays(utcNow(),-1,'yyyy-MM-ddT00:00:00.0000000Z')}') and (Date_x003a_ le '@{addDays(utcNow(),-1,'yyyy-MM-ddT23:59:00.0000000Z')}') and (Managerapproved_x003f_ eq 'Approve')

[Atennantsp]

@Expiscornovus Thanks for all your help with this but still no luck! I have tested it by creating a record with yesterday's date so it should be sending me the approval? but no joy.  Can you see any other errors that maybe causing the issue?

[Expiscornovus]

Hi @Atennantsp,

Be aware we are comparing it with the utc date time and not your local date time (what you have selected in the interface).

I would suggest to double check that new item in the Get Items output again (with the ID filter). Just to see, what the time difference is with your local time.

My expectation is that the Date: value of the item of your last test is not yesterday, but the day before (very late in the evening) 😁

Btw, can you also share the settings of your Date: column in the list?

[Atennantsp]

So there was around an hours difference, it was 08/09/23 15:10 here (UK) and the date output was 08/09/23 14:09:59. I do have the regional settings on the site set to UK time so hopefully that date is dd/mm and not mm/dd! 

Of course! Please see screenshot: 

[Expiscornovus]

Hi @Atennantsp,

Thanks for double checking, an hour difference shouldn't matter for this example (it is in the middle of the day). And it is good to know that your using UK time, that is pretty close to UTC 😁.

Did you select the correct date in your test? You mentioned you were testing with yesterdays date (07/09/23). However, in your test you used 08/09/23, which is today 😉

Which would explain why the filter does not work.

[Atennantsp]

@Expiscornovus Yep I ran another test after I noticed this earlier and did it again for yesterday's date: 

So I'm stuck! Sorry for taking up so much of your time - it is appreciated! 😊

[Expiscornovus · Accepted solution]

Hi @Atennantsp,

It looks like the Date: column filter is not the issue. Your Manager approved is a yes/no type of column. You are filtering for the text 'Approve'. That part won't work for that type of field.

Can you try the below?

(Date_x003a_ ge '@{addDays(utcNow(),-1,'yyyy-MM-ddT00:00:00.0000000Z')}') and (Date_x003a_ le '@{addDays(utcNow(),-1,'yyyy-MM-ddT23:59:00.0000000Z')}') and (Managerapproved_x003f_ eq 1)

[Atennantsp]

@Expiscornovus That worked! Amazing thank you so much for all your help/time 😁 

[Atennantsp]

Hi @Expiscornovus sorry me again! So the flow is working but it is now sending me multiple alerts. I am going to change the trigger to a weekly shedule, so is there a way I can add in another filter which says If reflective feedback is empty? So it only sends the alert to people who haven't completed the approval. I

and (Postactivityreflection_x003a_ eq empty? or "" etc. 

Thanks!