Answered

Using ODATA Filter Query on Get Items to Compare SharePoint List Date & Excel Date

(1) Share

Report

Posted on by Krickner

Hello, first, thank you for looking into this issue, I appreciate you and your time.

I have an automated flow that triggers when a new excel file has been created in one of my SharePoint Libraries. The flow creates a table in the excel file and then does an 'Apply_to_each' Get Items from my SharePoint List (Named: Shipping List). I have an ODATA Filter Query that needs to compare Job Number and Due Date fields between the excel file and SharePoint List. Those fields are used because they'll never be replicated.

This is to determine if Power Automate must create a new SharePoint list item or update an existing one. I keep getting the following error:

"Title" is my SharePoint column ID for "Job Number", the "200164-25" is just one example of a job number. "Field_4"is Due Date.

I believe the issue is due to the date format, as it's probably coming in from the "List rows present in a table" in one format, while Get Items from the SharePoint list is in another format. I don't know how to resolve this issue. (See Flow Below)

______________________________________________________________

Here is the formula I'm using to attempt to fix the issue, however, even without manipulating the date, it did not work. Still, every ODATA filter query fails to find a match.

----------------------------------------------------------------------------------------------------------------------------------

Categories:

Building flows

I have the same question (0)

All responses (19)

Answers (1)

Sundeep_Malik 6,484 on at

Like (0)

Report

Hey @Krickner

Do the condition like this:

(Title eq 'JobNumber') and (field_4 eq 'formatdatetimeexpression')

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

I'm going to try this now @Sundeep_Malik . I'll update you in a few. thank you : )

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

@Sundeep_Malik the same error occurred. My instinct is telling me it has to do with date formatting, but I don't know how to see which date format the SharePoint list is using. I set the "list rows present in a table (excel)" format to MM/dd/yyyy.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

I also tried changing the "formatdate" to be "MM/dd/yyyy" which excludes the time. My SharePoint List column is not set up to display time (see below). I figured it would be a long shot as I'm pretty sure it keeps that data on the backend and uses it when importing into Power Automate. Never-the-less, it did not work.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

I have been able to verify that the issue lies in the "field_4 (DueDate)" portion of the ODATA Filter Query, as I removed that portion and just left my job number and the flow ran successfully.

When the flow ran successfully I was able to see the data format GET ITEMs was pulling from my Due Date field out of my SharePoint List, and it was "yyyy/MM/dd" So, I adjusted the ODATA filter Query to match that format "
formatDateTime(items('Apply_to_each')?['DueDate'], 'yyyy/MM/ddT00:00:00.000Z')" and it still didn't work. 😥 😪

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report

@Krickner

No, it's nothing to do with the time format currently. I think the problem could be because of apply to each. I will try to replicate it on my end tomorrow. Hopefully someone else till then would get back to you.

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report

@Krickner

I want you to try some things, can you filter with 1 condition at a time, lets see which one is giving error.

Try one more thing. Run the flow without any filter query. In the next step add a compose and in that pass the value expression you are getting from get items. Could be possible the field stored in Sharepoint for field_4 and Title could be different name. Though as you have already entered field_4 I am suspecting you have already done that.

Lastly, if one condition works, use a filter query action and filter the array you are getting from get items on the other condition. I found some posts where people have done that.

I hope this helps 🙂

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report

@Krickner

and also instead of this : formatDateTime(items('Apply_to_each')?['DueDate'], 'yyyy/MM/ddT00:00:00.000Z')

use this:

formatDateTime(items('Apply_to_each')?['DueDate'], 'yyyy/MM/dd')

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

I tried filtering by just the Job Number (Title), and the flow worked. I didn't try just filtering field_4 (Date), but I'll do that now. Yeah, I verified the field names in Share Point. Okay, so I should leave the ODATA filter in Get Items for SharePoint field (Title), but before the condition, I should insert a Filter Query action?

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report

@Krickner

So, just keep Title in get items filter query.

After that get items, use filter array action and filter the array you are getting from get items on the field_4.

Was this reply helpful? Yes No