REST API GetRows() limited by URL length?

Hello,

I have been using the REST API for some time in several custom applications and it has been very helpful. However, I repeatedly come across the same problem of querying data for a list of keys and hitting a URL limit.

Say I have a list of part numbers and I would like to get a field called “ClassID” for each. I end up with a HTTP GET url that ends with something like this:

…/Erp.BO.PartSvc/Parts?$select=ClassID&$filter=PartNum%20eq%20’part_a’%20or%20PartNum%20eq%20’part_b’

If I want to do the same with a list of 25-50 part numbers, the URL seems to exceed some length (~2000 characters) and I get an error (404).
Thus, I have been splitting the list into several smaller API requests and merging the result.

What I’ve tried so far:

  • Splitting the list into several smaller API requests and merging the result.
  • The “in” syntax from Odata v4 is a little more compact and could fit more part numbers in the query url and would probably satisfy my need. Unfortunately, that results in “Error: Expression not yet supported: In”
  • The $batch and $query syntax of Odata v4.01. It seems that neither of these functions are implemented in the API.

Is there a better way to do this type of request? It would be nice if there was some way POST the query in the body for a longer query.

Currently on Epicor 10.2.6

Use the custom methods and post the where clause that way maybe?

Thanks for responding @jgiese.wci
Would you mind explaining what you mean by “custom methods”. Is that an Epicor thing or and OData/REST thing?

edit: or a BAQ? I’ve never done that (more developer than Epicor user)

Those are viewable in swagger. They basically give you access via REST directly to the BO method. Be warned when you go to the custom methods pages (especially for partsvc) it will take a long long time to load. Just wait it out.



Awesome! That’s exactly the type of method I was hoping for! I will test this out and mark the solution appropriately

In my opinion you should definitely be using a BAQ, and then running that BAQ over REST. It doesn’t make much sense to use REST as you would SQL for example, and a BAQ will almost certainly run faster anyway.

If his filters are static yes, if not then he will have the same issue with the BAQ since the filter is in the query params.

My filters are indeed dynamic (in that the part numbers change as well as the quantity).

Regarding the first proposed method, does anyone know where I can find out the difference between all these whereClauses? It seems like they are all required?

GetRows_input {
whereClausePart (string),
whereClausePartAttch (string),
whereClausePartCOO (string),
whereClausePartDim (string),
whereClausePartLangDesc (string),
whereClausePartPlant (string),
whereClausePartRestriction (string),
whereClausePartRestrictSubst (string),
whereClausePartRev (string),
whereClausePartRevAttch (string),
whereClausePartAudit (string),
whereClausePartCOPart (string),
whereClausePartRevInspPlan (string),
whereClausePartRevInspPlanAttch (string),
whereClausePartRevInspVend (string),
whereClausePartSubs (string),
whereClausePartPC (string),
whereClausePartWhse (string),
whereClausePartBinInfo (string),
whereClausePartUOM (string),
whereClauseEntityGLC (string),
whereClauseTaxExempt (string),
pageSize (integer),
absolutePage (integer)
}

And IMHO, use Epicor Functions. Still REST. Still BAQ. Still dynamic queries. Less traffic than multiple smaller payloads.

2 Likes

REST v2 is POST so the query parm size limit wouldn’t be reached.

@jgiese.wci I have also gotten your suggestion to work. At first I didn’t realize whereClause meant a literal SQL style where clause haha. I was trying to use the ‘eq’ syntax.

@Mark_Wonsil Thank you for the suggestion. I’ll give that a try as well.

When you call GetRows, you can specify a where clause for each table in the dataset. You don’t have to though. The criterias follow each joined table. So for example if you specify PartNum for table Part, the rest of the tables will return for that part only. However if you don’t need the 17 other tables, one trick I use is set their where clause to “1=0”. This will exclude the rows from the dataset and reduce bandwidth usage…

Thanks for the tip @HLalumiere
Is there anyway to use a "SELECT’ style syntax with GetRows? In my case, I’m only interested in a single field in Part but I am pulling the entire row.

No you cannot specify the individual fields you want returned, you would need to use a BAQ for that. GetRows returns the entire row including the UD fields. GetList returns a reduced subset of fields for each row of the main table only, so if the fields you need are there you might be able to use it.