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

[Bizzo]

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?

[v-yamao-msft · Accepted solution]

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.

Best regards,

Mabel Mao

[Bizzo]

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]

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]

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?