cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jaylou
Level 8

Conditional And in an Apply for each

Hi all,

I have been banging my head on a wall for the past few days trying to figure out how to get a FLOW to get me all items in a Shareppoint online custom list, where the target date is within 2 weeks AND Date Sent to Tax is empty...

 

Now for the fun 🙂

I may be wrong, but I believe that an empty date in SP online is set to '01/01/001'.  I have a SPD2013 work flow where I check for empty dates to send an email alert.  much to my surprise the alerts never went out due to empty dates, all dates seem to always be filled.  I added a log to history capturing my variables and they are all 01/01/0001, so I updated my SPD WF where date = 01/01/0001 and all is well...

 

Back to FLOW!!

I was able to get the items where target date is within 2 weeks using:

@lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14)))

 

I can't figure out the sent date being empty though.  I have tried all the below, but I am missing something about checking for empty or 1/1/0001.

 

@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),lessOrEquals(items('Apply_to_each')?['DateSentToTax'], formatDateTime('01/01/2017')))


@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),empty(items('Apply_to_each')?['DateSentToTax']))

 

@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),equals(items('Apply_to_each')?['DateSentToTax'], formatDateTime('1/1/0001 12:00:00 AM')))

 

@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),Equals(items('Apply_to_each')?['DateSentToTax'], formatDateTime('1/1/0001 00:00:00')))

 

 

TIA,

Joe

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Conditional And in an Apply for each

Hi @ jaylou,

 

Could you please share a screenshots of your flow’s configuration?

Do you want to get all the items that match the condition, or do you want to use “Apply to each” to get each item that meets the criteria?

I have made a test on my side, the empty date would still be empty in Microsoft flow.

 

I have made a sharepoint list as below:

5.png

 

The “ACCTGTARGETRELEASEDATE” and “DateSentToTax” column are both date type column.

If you want to get all the items that match the condition, you could create a flow as below:

6.png

 

The expression in the “Filter array”as below:

 

@and(lessOrEquals(item()?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),empty(item()?['DateSentToTax']))

 

 

 

The flow would run successfully as below:

7.png

 

The item which’s title is equal to “c” would be filter out.

 

If you want to use “Apply to each” to get each item that meets the criteria, you could create a flow as below:

8.png

 

The expression in the Condition as below:

@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),empty(items('Apply_to_each')?['DateSentToTax']))

 

The flow would run successfully as below:

9.png

 

Only the item which’s title is equal to “c” would be figured out and would send an email alert.

 

 

 

Regards,
Alice Zhang

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Conditional And in an Apply for each

Hi @ jaylou,

 

Could you please share a screenshots of your flow’s configuration?

Do you want to get all the items that match the condition, or do you want to use “Apply to each” to get each item that meets the criteria?

I have made a test on my side, the empty date would still be empty in Microsoft flow.

 

I have made a sharepoint list as below:

5.png

 

The “ACCTGTARGETRELEASEDATE” and “DateSentToTax” column are both date type column.

If you want to get all the items that match the condition, you could create a flow as below:

6.png

 

The expression in the “Filter array”as below:

 

@and(lessOrEquals(item()?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),empty(item()?['DateSentToTax']))

 

 

 

The flow would run successfully as below:

7.png

 

The item which’s title is equal to “c” would be filter out.

 

If you want to use “Apply to each” to get each item that meets the criteria, you could create a flow as below:

8.png

 

The expression in the Condition as below:

@and(lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 14 ))),empty(items('Apply_to_each')?['DateSentToTax']))

 

The flow would run successfully as below:

9.png

 

Only the item which’s title is equal to “c” would be figured out and would send an email alert.

 

 

 

Regards,
Alice Zhang

View solution in original post

jaylou
Level 8

Re: Conditional And in an Apply for each

Hi Alice,

My configuration is the same as yours for the Apply_to_each.  It looks like you used the same code I put into the question.  I copied yours out of the soution, and compared to what I was running, and besides an extra space after the 14, mine was exact. 

I copied yours into my condition and it worked as I expected!!!  THANKS!!! 🙂

 

The wierd thing here is that if I load a variable and I log it ot the history list from my SharePoint online list, I get 1/1/0001 for all empty dates, but FLOW sees them as empty...

Oh well as long as I know how to handle each I am good. 

 

Thanks Again,

I almost pulled out the one standing hair I have left on my head 🙂

 

Joe

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 340 members 5,885 guests
Please welcome our newest community members: