Flow Test Successful, but...

[forbudt4u]

Here I am again after researching and trying to find a similar post with a solution, but have found nothing.  I'm hoping someone can help me out in diagnosing my issue.  Short and skinny is I have a flow set up to monitor one field in a list and send a reminder email out 30 days prior to a respective date should there be a record in the list that triggers the flow.  Now, the test successfully runs, but it will NOT send the email even though I have a test record that should trigger it.  

We are dealing with a Date/Time field, so the first thing I did was to make sure the field formatting matched the flow format, and it does.  So, I'm at the end of my rope trying to find out why this flow isn't sending the email and need some guidance or a shot of whiskey!  Screen shot's below showing the list I'm working from with the date/time format confirmed (Top), the flow as set up (Left), and the flow results showing the email not sending (Right).

[PrasadAthalye]

What is the data type of LeaseCalibrated field (I am sorry for the name as I can not see it clearly in screen shot) ? Does it store Date only or DateTime? If its date time, your filter query should have time factor too. Instead of equals I use Ge something like LeaseCalibrated  ge '2020-08-01 00:00' and LeaseCalibrated  le '2020-08-02 00:00'

[Pstork1]

Date fields in SharePoint always include a Date and a Time.  If they are set for Date Only then the time defaults to midnight UTC.  So doing an OData query using eq will never work, since the date is not equal to a date and time.  Change the OData filter in your Get Items to be gt 29 days and lt 31 days.  That should give you some results.

[forbudt4u]

@PrasadAthalye & @Pstork1 

Okay, I could not get a test to successfully go through by using each one of your solutions individually, so I just combined the logic and was able to get a successful test, but still no email.  

I wanted to add that I do understand that SharePoint should always give a time along with the date, but that's why I placed the data output at the top of my original screencap to show that it was only outputting the date with NO time included.  In addition, I wanted to reiterate that there is a integer variable set up as part of my flow (-335 days) which allows the flow to get the date via the varReminderDateStart & varReminderDateEnd string variables I need for the flow to trigger.  

Here are the results of the modified flow which covers all times from Midnight to 11:59pm (or 23:59) on the respective day.  I'm lost!

[PrasadAthalye]

So if you click Click to Download in outputs of GetItems, Do you see any data OR is it empty? 

[Pstork1]

Just to be clear, the output in your screen capture is the date you set in the variable, which has no time.  The actual dates from SharePoint don't show in your screen capture anywhere.  Those are the ones that always have time associated with them.  One suggestion would be to switch your comparisons to 'ge' and 'le' (Greater than or equal to..) instead of just greater than or less than.  the date with 00:00:00 (midnight) is very possible.

[forbudt4u]

@PrasadAthalye  No, I'm not getting any data from the SharePoint list... just the typical flow coding.  

@Pstork1 The screencap I'm referring to at the top that's below the list item in SharePoint IS the output data from the list.  I spent all day yesterday trying to diagnose this issue myself and the first thing I did was start with the datetime formatting. So, I created an Output data flow that I manually triggered that would output ALL data for records/items listed in my name where my test record for this current flow resides as well.  The output data from that Output data flow is exactly what the Get Items produced... a Date with NO time associated with it.  

I also did go back and change the filters to ge and le but I'm still getting the same results... a successful test, but no email or data being outputted.  

[Pstork1]

Sorry, I was looking at the Flow screenshot, not the SharePoint list.  The list will show a date as date only.  But when retrieved it has always been a date and time.  Nothing in the flow itself was showing the output of the data from SharePoint.

[Pstork1]

OK, I just did some additional testing and it appears that MS has implemented a change to the way this works.  In the past it worked as I described and Date only fields provided the dates as Date and Time with the time set to midnight.  Date only fields in SharePoint now actually provide the date as a date only.  The problem with your original filter is the formatting of the target date.  It has to match the locale formatting that SharePoint is using.  I just ran a new test on a list I have with Date only fields.  This get items works.  Notice the 'mm/dd/yyyy' formatting.

[forbudt4u]

@Pstork1 

I think this is a case of not being able to see the forest through the trees here.  What I'm trying to explain is I totally understand the list won't show the time in the actual list (unless you tell it to in the column properties).  That's why I created a totally separate flow that outputs ALL Data from SharePoint records where I am the listed Holder.  I wanted to see what SharePoint was actually passing to the flow, and that's why I posted the result of the output under the list item in white with an explanation note.  If you look, you can see the white screencap showing the corresponding columns with the results (or data output)... "Calibration_x0020_Required":true etc.  The LastCalibrated column passes on "LastCalibrated": "2020-08-01" with NO Time listed in addition to the date.

Below is a screencap of the Data Output Flow with the corresponding data output from the results of the flow.  You can also see with the blue dashed callout that the Date Modified column is passing time through, so that tells me that SharePoint is distinguishing between Date and DateTime columns.   That's why this is totally throwing me for a loop because regardless of the route I'm taking, I'm not getting the result I need in the actual flow I'm trying to build. 

  

[Pstork1]

Check my subsequent post.  MS has evidently changed the behavior since I last checked this out.  I think the issue now is that you are matching the formatting of what you see in the output, 'yyyy-mm-dd'.  I see the same thing in the output of Get items, but the filter actually worked for me when I used 'mm/dd/yyyy' which is what you see in SharePoint.  Try changing the format of what you are testing for and see if that works.

Output from Get items

Working OData filter

[forbudt4u]

@Pstork1 

That's why I'm here, cause that's where I originally started and was getting the same results 😅  I've tried everything I can think of with all tests successfully running, but NOT producing any data even though I'm sitting here staring at a list item/record who has a Last Calibrated (or LastCalibrated) date as 08/01/2020.  The only nuance is that the SharePoint list actually shows 8/1/2020 without the Zero preceding the 8 or 1, but the output data does have the Zeros, albeit in yyyy-MM-dd format... which matches the flow formatting.  

Where's the Jack Daniels 😁🤣😅

[Pstork1]

So you are saying you tried 

Last_x0020_Calibrated eq '08/01/2020'

and 

Last_x0020_Calibrated eq '8/1/2020'

without using the variable that you are calculating?  I would try that manually first then we can proceed from there.  I tested it on a Date Only field in SharePoint and it works for me.

[forbudt4u]

@Pstork1 

Okay, so I've tried bypassing all the variables and doing direct entry dates in the following formats...

TEST FAILS due to column not existing (because it's named LastCalibrated in the column properties)

Last_x0020_Calibrated eq '08/01/2020'

Last_x0020_Calibrated eq '8/1/2020'

Last_x0020_Calibrated eq '2020-08-01' 

Last_x0020_Calibrated eq '2020-8-1'

TEST SUCCESSFUL but does NOT produce output data or email

LastCalibrated eq '08/01/2020'

LastCalibrated eq '8/1/2020'

LastCalibrated eq '2020-08-01' (This one matches the output formatting from test output flow)

LastCalibrated eq '2020-8-1'

[Pstork1]

Sorry, I used the wrong column name in the code.  It should be the second one.  I actually went the other way and used the variables as you did and I'm still getting output.  After one of the successful runs did you click on the Click to download link in the output section of the get items to make sure that you actually retrieved the records? 

If you did then we need to start looking at configuration of the loop and the emails.  Is the field Holder a person column or a single line of text with an email address?

[forbudt4u]

@Pstork1  I'm not getting any record data being returned even though I know I have 1 record that should trigger the flow.  That's why I think it's a date formatting issue as the holder column is a person column.  I have many other flows set up to email using this same column with no issues.

[PrasadAthalye]

Can you try these?

Last_x0020_Calibrated gt '07/31/2020'

and 

Last_x0020_Calibrated lt '8/2/2020'

OR 

Last_x0020_Calibrated gt '2020-07-31'

and 

Last_x0020_Calibrated lt '2020-08-02'

Either of these must return result... 

[ccc333ab]

This is crazy, but from what I can see, your logic should be working. I just created a test flow on my end, with same date format in SharePoint and hardcoding in the date like you did and works just fine. 

Just by you saying I have many other flows set up to email using the same column with no issues makes me wonder if this is just a "Microsoft feature"? 

If the flow isn't crazy big, maybe you can just do the following: 

1. Recreate the flow with just using a Get Items call, and a hard coded filter query for that date (LastCalibrated eq '2020-08-01'). Test.... 

2. And if that works, then just rebuild the flow around this Get Items call...and scrap your existing flow

3. If it doesn't work then what did you do for the other flows that do work???

[Pstork1]

All I can tell you is that I replicated your flow and my Get items works fine.  One record in the list with the expected date and one record returned.

[forbudt4u]

@Pstork1  I understand, but that's why I'm here cause something is broken.  Thank you for your help.

I'm likely going to try @ccc333ab recommendation and see what happens... I'll report back.