Pagination Issue in REST API

Epicor ERP 10
,
1

I have an issue regarding pagination for some REST endpoints. For example HTTP GET requests against the api/v2/odata/<company>/Ice.BO.ZDataTableSvc/ZLookupLinks endpoint:

  • [skip=0, top=1000000] => 3092 unique records
  • [skip = 0, top = 1] => empty array
  • [skip = 0, top = 19] => empty array
  • [skip = 0, top = 20] => one record
  • [skip = 0, top = 28] => two records
  • [skip = 1, top = 28] => one record

If I were to use a fixed page size and offset value, that would result in skipped records when trying to read the whole table.

I think I can get all the records when not using a fixed increment for $skip but rather the total number of records received so far. But there are two issues with that approach:

  • When to stop? If an empty array is received was that the last page or there are more and need to keep asking for consecutive pages?
  • The framework we are using doesn’t have support for dynamic calculation of the paging offset

I’ve tried multiple $orderby setups, but it didn’t seem to have any effect.

This isn’t true for all endpoints, some (eg. Ice.BO.ZDataTableSvc/ZDataTables) seem to return as many records as specified in $top for not last pages.

Context:
We have a platform capable of blending data from multiple ERP systems and we would like to add support for Epicor as well. We are looking for a way we could read data from Epicor systems. A solution that would work more or less out of the box on most Epicor installations would be preferred. So using BAQ over the generic REST may be not a good option for us.

I have to admit I have zero Epicor related knowledge. I don’t even know what a BAQ is, but noticed a few recommendations on this forum to use BAQs over the generic REST API.

Can someone recommend a solution?

1 Like
2

Don’t use the odata endpoints.

Step back and give us the full picture of what you want to do.

Cool, I can use this new template :rofl: →

To help the community provide the best answer, could you include as much of the information below as you can? Your question appears to be lacking some much needed context

  • Epicor Version
  • Deployment Type (Cloud, On Prem, 3rd Party Cloud etc)
  • The business problem you’re trying to solve
  • What you’ve already tried
  • Is this a Kinetic UX (Web) or Epicor Classic issue?
  • Is it related to a Method Directive, Data Directive, Function, BAQ, UBAQ, Configurator, etc.?
  • Any screenshots, error messages, or logs.
  • Any code you’ve written (or borrowed , make sure it is property formatted)
    ```
    code here
    ```
  • Steps to reproduce the issue

For tips on how to ask questions effectively, check out this guide:
Tips for Asking Questions on the Forum
clippy-hi

1 Like
3

Lol, I must have missed this part, or I wouldn’t have posted the template. :person_shrugging:

1 Like
4

I’m not seeing the same results you posted, using that same table.

I have 37 results in that table.

I used $skip = 30, $top = 10, and received 7 records, which is correct.

5

This may be a good place to start before ruling the BAQ out. :wink:

The Business Activity Query is, at its heart, a SQL builder tool. Most newcomers to Kinetic tend to say, “I don’t need that. I know SQL!” But the BAQ does more than just create SQL queries. It also takes care of some security items: straight SQL does not respect field security, site security, company security, or territory security. A BAQ will add that security for you. BAQs are used throughout the UI where an external SQL query is more limited - especially if you’re in the cloud. A BAQ can be parametrized given you flexibility.

As Kevin mentioned, the OData interface is a wrapper around the business objects. Some of these, well, work better than others, as you’ve noticed. The BAQSvc allows you to bring down only the fields you want, create calculated fields to reduce client-side work, can move more records than the standard Odata calls, and has been quite reliable.

3 Likes
6

Epicor version: Wanted to include this in the original post, but TBH I don’t know and also don’t know how to get it. Have searched for it but all the solutions rely on having some kind of access I don’t have. We are a 3rd party team having API access to an on-prem Epicor system. I haven’t been able to find any way to get the version through the API.
Will ask our contact person, and update the original post.

Deployment: On-prem, but would love to have a solution working for all deployment types.

We can read the data we would like by a direct read-only DB connection. But would like to expand our solution to other potential clients using Epicor. We think relying on direct DB access could be a show-stopper at some places and using the API could be a better approach.

1 Like
7

Thanks for trying it out.
Will try to speculate on what might be the secret ingredient for this behavior. Will circle back when I have a theory.

8

Give me a minute lol. :rofl:

But yes, the version would be helpful, in case there is some problem with it, because as I said above, I am not seeing the same results.

You are absolutely correct here, because for any SaaS customers, if they has not purchased an add on package that provides read only SQL Access, you must use the api, or create some helpers.

1 Like
9

:nauseated_face:

Not in cloud, at least not the Production database.

:100:

image
image986Ă—377 23.7 KB

image
image808Ă—136 6.24 KB

image
image857Ă—271 3.68 KB

1 Like
10

I believe that will be restricted unless you are a security manager?

You can also use this one:

Ice.BO.KineticErpSvc.GetUserSessionAndVersion

portion of output

    "version": {
      "major": "11",
      "minor": "3",
      "build": "100",
      "revision": "10"
    },
    "fwVersion": {
      "major": "4",
      "minor": "3",
      "build": "100",
      "revision": "10"
    },

version & framework (ICE) version is in there

2 Likes
11

I’m sure you’re correct. I forgot there have been some restrictions on the SessionModSvc recently. Thanks for the new endpoint!

1 Like
12

It’s new to me too. Looking through it, I could have used it a few times in the last week. Dammit.

1 Like
13

And there’s a LOT more information available in a single call!

1 Like
14

(Phrase for future users trying to find how to get product version through the API with a search engine: How to get Epicor version through API)

Strange, neither of the two endpoints seem to be accessible to me.

The API explorer lists the SVCs, but not the mentioned sub items under them. Only the SVC root / and $metadata seems to be available

When I try to GET Ice.BO.KineticErpSvc.GetUserSessionAndVersion a 404 is returned.

image
image1304Ă—678 37.4 KB

image
image1276Ă—619 37.1 KB

15

If you’re using v2 of the API then you must provide an API key. For testing, you can use the v1 - at least for the time being.

Try clicking on the RPC tab instead of the OData methods.

2 Likes
16

Thanks Mark, It was right there!
Response:

"version": {
	"major": "11",
	"minor": "3",
	"build": "100",
	"revision": "10"
},
"fwVersion": {
	"major": "4",
	"minor": "3",
	"build": "100",
	"revision": "10"
},

Is there anything else from that response that might be relevant?

1 Like
17

Just a lot of user data. For an integration situation, you’ll want to learn about:

  • API Key Maintenance
  • Access Scope
  • Integration Accounts (User Account Security Maintenance)

These commands allow to you restrict the access of the integration to the objects/methods required.

3 Likes
18

Any progress?

19

The case is still alive from my side, but I haven’t been able to actively chase it in the last couple of weeks. I am picking it up again now, but first I would like to get a bit more familiar with the system as a whole.
Will update this thread once there is progress worthy of posting.

1 Like