cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
landsend
Resolver II
Resolver II

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

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

landsend_0-1652738467868.png

 

 

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

landsend_1-1652738467872.png

 

 

Each step opened up below

 

landsend_2-1652738467875.png

 

 

landsend_3-1652738467877.png

 

landsend_4-1652738467877.png

 

landsend_5-1652738467879.png

 

 

landsend_6-1652738467880.png

 

 

landsend_7-1652738467881.png

 

 

landsend_8-1652738467882.png

 

 

 

 

 

 

5 ACCEPTED SOLUTIONS

Accepted Solutions
landsend
Resolver II
Resolver II

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

landsend_1-1652739215665.png

And the step its in

 

landsend_0-1652739177974.png

 

View solution in original post

mprentice
Frequent Visitor

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')

View solution in original post

landsend
Resolver II
Resolver II

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

   

landsend_10-1652827229240.png

landsend_11-1652827277197.png

 

Here each step opened up before run.

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

     landsend_1-1652825182422.png

 

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

      landsend_2-1652825298593.png

 

3) Current time and expiration date substringed to ten characters

     landsend_3-1652825354498.png

 

3) Added the convert to time zone for EST

    landsend_4-1652825481528.png

 

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

landsend_5-1652825777696.png

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.

   

landsend_6-1652826103427.png

 

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.

   

landsend_8-1652826891697.png

 

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

    

landsend_9-1652827087856.png

 

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

 

Below is output from a run with flow checker good

landsend_12-1652827433040.png

 

--------- 

 

landsend_13-1652827706404.png

landsend_14-1652827722431.png

landsend_15-1652827760084.png

landsend_16-1652827799481.png

landsend_17-1652827829817.png

landsend_23-1652828366793.png

landsend_1-1652829599062.png

landsend_2-1652829655080.png

landsend_3-1652829698884.png

landsend_4-1652829739968.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

    

 

 

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

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

landsend_13-1652891188851.png

 

--  Here is the output after a run --

landsend_0-1652890479307.pnglandsend_1-1652890495031.pnglandsend_2-1652890512331.pnglandsend_3-1652890528673.png

landsend_6-1652890596735.png

landsend_7-1652890668869.png

landsend_8-1652890792775.png

landsend_9-1652890837225.png

landsend_12-1652891127674.png

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

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

landsend_14-1652893598070.png

 

 

 

 

View solution in original post

6 REPLIES 6
landsend
Resolver II
Resolver II

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

landsend_1-1652739215665.png

And the step its in

 

landsend_0-1652739177974.png

 

mprentice
Frequent Visitor

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
Resolver II
Resolver II

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

   

landsend_10-1652827229240.png

landsend_11-1652827277197.png

 

Here each step opened up before run.

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

     landsend_1-1652825182422.png

 

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

      landsend_2-1652825298593.png

 

3) Current time and expiration date substringed to ten characters

     landsend_3-1652825354498.png

 

3) Added the convert to time zone for EST

    landsend_4-1652825481528.png

 

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

landsend_5-1652825777696.png

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.

   

landsend_6-1652826103427.png

 

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.

   

landsend_8-1652826891697.png

 

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

    

landsend_9-1652827087856.png

 

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

 

Below is output from a run with flow checker good

landsend_12-1652827433040.png

 

--------- 

 

landsend_13-1652827706404.png

landsend_14-1652827722431.png

landsend_15-1652827760084.png

landsend_16-1652827799481.png

landsend_17-1652827829817.png

landsend_23-1652828366793.png

landsend_1-1652829599062.png

landsend_2-1652829655080.png

landsend_3-1652829698884.png

landsend_4-1652829739968.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

    

 

 

 

 

 

 

 

 

 

 

 

 

 

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. 

mprentice_0-1652843553105.png

 

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

mprentice_1-1652843705367.png

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. 

 

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

landsend_13-1652891188851.png

 

--  Here is the output after a run --

landsend_0-1652890479307.pnglandsend_1-1652890495031.pnglandsend_2-1652890512331.pnglandsend_3-1652890528673.png

landsend_6-1652890596735.png

landsend_7-1652890668869.png

landsend_8-1652890792775.png

landsend_9-1652890837225.png

landsend_12-1652891127674.png

 

 

 

 

 

 

 

 

 

 

 

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

landsend_14-1652893598070.png

 

 

 

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,034)