cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lugho
Frequent Visitor

SP Get Items Date Field Unavailable for ODATA Filter Query

Hello, community:

This is a first time for me, so here's a question I can't seem to figure out on my own.

I'm using SharePoint 'Get Items' connector to retrieve records about to expire based on field 'Expiration Date' ('Date and Time' type). 

ODATA query keeps saying 'Bad Request' and field 'Expiration Date' does not exist when I know it does.

This is the ODATA, first two filters work fine:

(Extended_x003f_ eq 'No' and Status eq 'Approved') and Expiration_x0020_Date ge '@{addDays(utcNow(),15)}'

 

Thanks and greetings!

GH

1 ACCEPTED SOLUTION

Accepted Solutions
Lugho
Frequent Visitor

I finally figured this one out! In ODATA, I must use SharePoint internal names that are normally the same as the "display names" in SharePoint Designer or when building list views.

In my case, for whatever reason, my 'Expiration Date' field was named just 'field3' as SharePoint's internal name.

Internal name can be found in the SP list settings page. Then click on the name of the field whose internal name is needed (or just hover over it) so in the address bar the address ends with 'Field=????' ('????' being the internal name).

Lugho_3-1619462115265.png

Thanks and regards!

View solution in original post

6 REPLIES 6
Mira_Ghaly
Dual Super User II
Dual Super User II

@Lugho

To compare date fields in OData you need to format the date as yyyy-MM-dd

(Extended_x003f_ eq 'No' and Status eq 'Approved') and Expiration_x0020_Date ge '@{formatdatetime (addDays(utcNow(),15),'yyyy-MM-dd') }'

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Lugho
Frequent Visitor

Thanks for the reply, @Mira_Ghaly 

I still get the same error. I've tried with and without formatting the date.

Lugho_0-1618878291091.png

Looks like field 'Expiration Date' is not found but I know it does exist.

Here's the field in SharePoint designer.

Lugho_1-1618878356764.png

It's also a member of the view I'm using to limit columns in the Get Items connector.

Lugho_3-1618878538482.png

This is the connector configuration.

Lugho_2-1618878464924.png

Thanks!

Mira_Ghaly
Dual Super User II
Dual Super User II

@Lugho 

your query looks ok to me , can you try removing other conditions and keep only the expiration date condition , also from from history are you able to view the Raw Inputs of this step?

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Lugho
Frequent Visitor

Just made a test with just the Expiration Date condition.

This is the raw input:

Lugho_0-1618879556007.png

This is the SP connector:

Lugho_1-1618879677980.png

I can only think of use the filters that work and add a condition connector to just filter by Expiration Date.

I know this is the "wrong way" of setting conditions but ODATA won't seem to fully work for my needs.

 

Thanks!

Mira_Ghaly
Dual Super User II
Dual Super User II

@Lugho 

Can you try not to select a specific view?

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Lugho
Frequent Visitor

I finally figured this one out! In ODATA, I must use SharePoint internal names that are normally the same as the "display names" in SharePoint Designer or when building list views.

In my case, for whatever reason, my 'Expiration Date' field was named just 'field3' as SharePoint's internal name.

Internal name can be found in the SP list settings page. Then click on the name of the field whose internal name is needed (or just hover over it) so in the address bar the address ends with 'Field=????' ('????' being the internal name).

Lugho_3-1619462115265.png

Thanks and regards!

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,048)