On expiration date check cant' get condition to go to yes when it seems it should to send email

[landsend]

Hi,  I keep getting this error and not sure why, any help most appreciated.

Here is the flow overview I’ll open them all up under this screenshot. I can’t get past the condition being false.

Each step opened up below

[landsend · Accepted solution]

Also here is the query filter from the output log of a run

And the step its in

[mprentice · Accepted solution]

What's the format of "Expiration Date"? Date or DateTime? The action you are using to set your variable, Add To Time gives this format 2022-06-16T02:55:41.9015689Z, it's unlikely this is the same format as the Expiration Date column. Also worth mentioning, Current Time with give you the time in the UTC timezone. 

Since you are using the Date Time functions, I would add an action of Convert Time Zone. Use your output from Add to Time as the input value, UTC as the base time, your time zone as the convert to time, then select the format that matches your SharePoint column. Also keep in mind if you are using the condition of Equals against a DateTime, both the date and time values given have to be equal. If your SharePoint column is just a date then make sure you select that as your output format in the Convert Timezone action, if your SharePoint column is a DateTime, then I would suggest converting both to the format yyyy-MM-dd. 

Going to add as well for anyone that needs it. The time conversion can be done in one step using expressions. This is how you would add 30 days to the current time in PST, and return yyyy-MM-dd format 

formatDateTime(addDays(convertFromUtc(utcNow(),'Pacific Standard Time'),30),'yyyy-MM-dd')

[landsend · Accepted solution]

Hi, Thanks for your help / patience lol.  I'm still not getting results expected.  Below I've expanded each steps' output. Apologize for the long thread but I've added results of the latest run as well. 

* Expiration date is a date time field in my SharePoint list.  I've used substring f(x) to knock it down to 10 characters (yyyy-mm-dd).

*  I added the 'Convert time zone' as suggested and logs show timestamps PST converted to EST.

*  I've changed the headings on steps to be more relevant. 

*  The current time (current time) and the 30 out expiration date (Calculated time) to check against I substringed both to yyyy-mm-dd.

*  I substringed 'Expiration Date' from the SharePoint list to get that to yyyy-mm-dd as well.

I'm opening up each step in the workflow before a run so you can see what I've changed in the flow before execution and beneath that are the results of this workflow executed - outputs from the run log file.

0) Overview

Here each step opened up before run.

1) Getting the task to run daily and on today's date

     

2) Getting the expiration date from 'current time'.

      

3) Current time and expiration date substringed to ten characters

     

3) Added the convert to time zone for EST

    

4) Connection to SharePoint list bringing back only what should be a single test record and weirdly this seems to work.

5) Once I have a result(s) from my SharePoint list I do a substring on the result set to find/set the expiration date returned to same date format as above to 10 characters.

6) This is my selection for condition/comparison 'Expiration Date' (from the SharePoint list) vs 30 days out 'add to time' that was calculated from today's date.

7) If condition met or not I get a status email.

😎 I've been only getting the emails for no.   

Below is output from a run with flow checker good

--------- 

[mprentice]

So, two issues left that I can see, but I just realized that you are filtering the SharePoint list against the expiration date (today +30 days), then doing a condition that checks the same thing. 

You can remove the condition entirely and just have your send email action in an apply to each loop for each item returned from SharePoint. They will already all meet your condition of Expiration_x0020_date = Today+30Days.

Here are the issues that I can see wrong with what you currently have, just so you understand them if you have another similar flow in the future with this issue:

1) Your condition is comparing the current item in the loop to the output of the expiration date substring. By doing that you are comparing the whole output from SharePoint for that item eg:

{

"id":10,

"title":"test",

"expiration_x0020_date":"2022-05-17"

}

Correct this by having the substring action that collects the first 10 characters of your expiration date from SharePoint in the same loop as your condition, and select the output of that substring action in the left field of the condition. The "Apply to Each 3" loop is redundant since you only need to look through your SharePoint results once. 

2) While you aren't using the output anywhere that I can see, be aware that with convert time zone, you need to input a time or a DateTime, what you have there is just the date, which is giving you a strange output

The output here should read

{

"body":"2022-06-16"

}

You can correct this, but if you're just working with yyyy-MM-dd dates, the convert isn't needed. 

[landsend · Accepted solution]

Hi MPrentice,  Thanks for the response that almost got me there.  I took out the condition and after the get items initiated email see below.  The input to the email looks right but the output section is blank.  Not sure what to make of that.

Overview here and log output beneath

--  Here is the output after a run --

[landsend · Accepted solution]

Hi MPrentice,  I'm calling this question answered.  I actually did get the email just that in outlook it ended up in 'Other' and not 'Focused'.  And yes first 'success' email from the flow.   I sure appreciate all the help your team has afforded me.  Can't say I look forward to asking more questions but why not and why we're here in the first place to extend knowledge and tools for our customers.  Again, many thanks !   I have more fields to add to the email but this was a turning point 🙂 

---   email recieved