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

Compare SharePoint date columns in get items filter query or in condition

I have a list that has an Exported column. The column will either be 0 (default) or 1 (once exported). I want to run a flow with a recurrence, get any items that haven't been exported or any items that have been exported but have been modified since they were exported, then create a new record for items that haven't been exported or update a record for items that have been exported.

 

I tried the following in the Filter Query:

(Exported eq 0) or (Exported eq 1 and Modified gt ExportedDate)

 

And it failed. The problem seems to be comparing Modified with ExportedDate. Can I compare 2 date columns?

 

As a workaround, I looked at any item that was modified in the last 4 hours (which will capture anything I need). I was able to get the results, added a switch statement on Exported. The Exported = 0 part was easy, but the already exported is giving me problems.

 

I'm trying to compare Modifed and ExportedDate (so we only update if needed) and can't seem to compare dates in a condition. As a workaround I tried to use formatDateTime on the 2 columns, Modified and ExportedDate, and format them yyyymmddHHss and then cast it as a string. I initialized 2 variables and set them like this (changing the column name):

string(formatDateTime(items('Apply_to_each')?['Modified'],'yyyymmddHHss'))

 

The columns weren't available in the dynamic content builder, so I typed that out. The variables come out as exactly what I typed, string(formatDateTime(items('Apply_to_each')?['Modified'],'yyyymmddHHss')). Is there any way to compare 2 date columns in a condition?

 

Any other suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare SharePoint date columns in get items filter query or in condition

Hi @Bizzo,

 

To filter items that Modified greater or equals ExportedDate, I would suggest you use a Condition but not the Odata filter query for this scenario.

 

I have made a test on my side. The function used in the Condition is:

@greaterorequals(formatDateTime(items('Apply_to_each_2')?['Modified'],'yyyy-MM-dd'), items('Apply_to_each_2')?['Date'])

 

Note: Date is a column of date only type, so I modified the format of Modified.

 

You don’t need to format the date as a string.

 

The flow is configured likes below, please have a try with it.

1.PNG

 

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Compare SharePoint date columns in get items filter query or in condition

Hi @Bizzo,

 

To filter items that Modified greater or equals ExportedDate, I would suggest you use a Condition but not the Odata filter query for this scenario.

 

I have made a test on my side. The function used in the Condition is:

@greaterorequals(formatDateTime(items('Apply_to_each_2')?['Modified'],'yyyy-MM-dd'), items('Apply_to_each_2')?['Date'])

 

Note: Date is a column of date only type, so I modified the format of Modified.

 

You don’t need to format the date as a string.

 

The flow is configured likes below, please have a try with it.

1.PNG

 

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Bizzo
Level 8

Re: Compare SharePoint date columns in get items filter query or in condition

Hi @v-yamao-msft,

 

I'll give that a try and let you know how it goes.

 

Question - Why do you recommend using the condition instead of the Odata filter query for this scenario?

 

Thanks,

Bizzo

Bizzo
Level 8

Re: Compare SharePoint date columns in get items filter query or in condition

Needed to make a couple minor edits, but that was the basic solution. Not sure what I did or how I missed trying that on my own, but thanks for getting me all fixed up! 🙂

Bizzo
Level 8

Re: Compare SharePoint date columns in get items filter query or in condition

Hi @v-yamao-msft,

 

I setup a simple test that switched on the condition and sent an email. The email was different based on the outcome of the condition, and it seemed to work fine. When I updated the Flow to actually do something after the condition (sql server update if "yes" or send the same email if "no"), the condition is now failing.

 

ActionConditionFailed. The execution of template action 'Condition' failed: an unexpected exception encountered when evaluating branching condition.
 
No other details seem to be available.
 
Comparing 2 date columns should be simple and straight forward, and it shouldn't suddenly stop working. Can you suggest anything to make this work?

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!

Top Kudoed Authors (Last 30 Days)
Users online (4,795)