REST API OData filter expanded records

jgiese.wci · November 16, 2020, 3:43pm

Has anyone else ever used the filtering on expanded children records? REST v2 is not throwing any errors but it’s also no filtering. I think I have it setup per the odata website under “Nested Filter in Expand” Basic Tutorial · OData - the Best Way to REST

https://myserver/instance/api/v2/odata/COMP/Ice.BO.UserCodesSvc/UserCodes(‘COMP’,‘MyCodeTypeID’)?$expand=UDCodes($filter=CodeDesc eq ‘ABC Code’)

The above responds but does not filter the children to where CodeDesc is “ABC Code” I just get all the children of the expanded top level.

jgiese.wci · November 16, 2020, 4:02pm

This version of filtering doesn’t filter the children either

$expand=UDCodes&$filter=UDCodes/all(c: c/CodeID eq ‘1’)

@Olga is expanded record filtering supported in v2?

Olga · November 16, 2020, 4:10pm

Probably not, filter was left as it was in v1

hkeric.wci · November 16, 2020, 4:12pm

v2 API Docs have

https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.Bo.SalesOrderSvc/Sales
Orders?$expand=OrderDtls($select=OrderLine)&$select=OrderNum

Notice the ($select=OrderLine) right after OderDtls… I wonder if you can also do $filter within the parenthesis, or is that just limited to select?

Aside Note… An Example how to Expand Sub-Tables

{{Host}}/{{Environment}}/{{path}}/{{Company}}/Erp.BO.SalesOrderSvc/SalesOrders?$expand=OrderDtls($expand=OrderRels,OrderMscs),OHOrderMscs&$filter=OrderNum eq 800500

OData atleast shows this example on msdn

https://localhost:port_number/orest/Customers?$expand=Orders($filter=Id eq 10)

hkeric.wci · November 16, 2020, 4:16pm

Try

$expand=UDCodes($filter=UDCodes/all(c: c/CodeID eq ‘1’))

jgiese.wci · November 16, 2020, 4:47pm

That’s not valid oData @hkeric.wci

hkeric.wci · November 16, 2020, 5:50pm

In v2 according to msdn it’s valid… it fires fine in v2… but the results are not filtered

https://server/EpicorERP/api/v2/odata/GLB/Ice.BO.UserCodesSvc/UserCodes?$expand=UDCodes($filter=CodeID eq '1030')&$filter=CodeTypeID eq 'PG_1000'

I guess Epicor doesn’t support it.

https://www.odata.org/getting-started/basic-tutorial/

Also looks like Epicor doesnt support /all either

jgiese.wci · November 16, 2020, 6:12pm

Olga pretty much said that 4 threads ago pay attention LOL!

hkeric.wci · November 16, 2020, 6:15pm

I know but I figured since its just OData there was nothing for Epicor to do on their part… OData is OData… but apparently they parse the $commands

–

If you are looking for a single Item I guess you could use this.

jgiese.wci · November 16, 2020, 6:37pm

I just built a BAQ to do what I needed and moved on.

hkeric.wci · November 16, 2020, 6:39pm

You mean Epicor Function right?

jgiese.wci · November 16, 2020, 6:39pm

Nope BAQ just returning data a func wouldn’t make sense.