REST Error

Epicor ERP 10
1

Getting the following Error while trying to filter a DateTime in a BAQ in Rest.
This is what the BAQ Result Looks Like

image
image.png779×450 16.3 KB

I’m trying to filter on Order Date Greater Than X

I’ve tried the following formats for filtering with differing errors.

Filter: OrderHed_OrderDate gt '2017-11-1'
Error: {"HttpStatus":500,"ReasonPhrase":"REST Api Exception","ErrorMessage":"A binary operator with incompatible types was detected. Found operand types 'Edm.DateTime' and 'Edm.String' for operator kind 'GreaterThan'.","ErrorType":"Microsoft.Data.OData.ODataException"}

Filter: OrderHed_OrderDate gt 2017-11-1
Error: {"HttpStatus":500,"ReasonPhrase":"REST Api Exception","ErrorMessage":"Syntax error at position 29 in 'OrderHed_OrderDate gt 2017-11-1'.","ErrorType":"Microsoft.Data.OData.ODataException"}

Any combination I try yields one of these two errors. Any ideas?

PING:@Olga @bconner @Bart_Elia?

Thanks!

2

Got it! You need quotes on both sides… (ugh those quotes are gonna give me gray hairs)
Correct syntax is ‘OrderHed_OrderDate’ gt ‘2017-12-07’

1 Like
3

And that’s a problem because…
Clooney

1 Like
4

Cause I ain’t no Clooney :joy:
image

1 Like
5

at least there is hair to turn gray::disappointed_relieved:

2 Likes
6

I wonder why it works for you. I’d think it requires something like
DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.

7

Well it “Works” as in I get data… it looks like its doing some “string” filtering. Let me try your trick.

8

So if I try the datetime trick

$filter='OrderHed_OrderDate' gt  datetime'2017-11-1'

I get

{"HttpStatus":500,"ReasonPhrase":"REST Api Exception","ErrorMessage":"Unrecognized 'Edm.DateTime' literal 'datetime'2017-11-1'' at '24' in ''OrderHed_OrderDate' gt datetime'2017-11-1''.","ErrorType":"Microsoft.Data.OData.ODataException"}

Looking at the Meta Data it does show that OrderHed_OrderDate is a Date…

image
image.png732×188 6.6 KB

Any ideas?

9

looks incorrect, probably just a typo here?

10

Yeah there’s another quote , the site ate it

image
image.png918×536 20 KB

11

It wants -YYYY-MM-dd exactly,
That is OrderHed_OrderDate gt datetime’2017-11-01’

1 Like
12

ok So removing the quotes from the Left Side and adding the extact format as you showed works…
So are there any rules we can find on when we need quotes on what side?

This worked! (no quote on the left)

OrderHed_OrderDate gt datetime'2017-12-01'

However this

'OrderHed_OrderDate' gt '2017-12-01'

Also works however it filters… based on string matching? Notice the quotes on the left, trying this with just the quotes on the right fails… #HeadAche

Thank you very much @Olga

1 Like
13

And OrderHed_OrderDate is property name and should not be quoted

1 Like
14

It is sent into BAQ so how it filters depends on its internal implementation. As I remember it will add where clause to the subquery.

1 Like