Hi @jorisdl 

I have been a bit slow with the REST guides lately. So I don’t have it in a guide yet. However it is possible to do so if there is something like a page and page size parameter.

There is this free API called https://www.openbrewerydb.org/documentation

It has an endpoint that can handle a page and a page_size parameter like this.

https://api.openbrewerydb.org/v1/breweries?page=15&per_page=3

This info is not saved in the headers or in some meta data for each page. So I don’t know he max page. 

We have an option for increasing a number on each iteration. To do so add a query parameter with the value {TX_NextPage}. 

For me the pagination is set up like so.

I also made a generic stop condition as you can see. {*/*/*} 1] means that it searches for anything in the page after the first three levels and stops if it finds nothing.

An important thing is that the query parameters need to be the correct case setting, so if I added the parameter as Page, it would not work, only page does.

As mentioned it also had a page_size parameter, but I added that to the endpoint.

It does not apply the pagination on the first call, only the second and as the default page_size is 50 it would not give back the correct amount of rows as the first call would be

https://api.openbrewerydb.org/v1/breweries

Then the second call would be

https://api.openbrewerydb.org/v1/breweries?page=2&per_page=200

So that is why it is added to the endpoint to be sure all calls are done with the correct amount of rows per page.

If your API needs a offset and a limit, which is also a common occurrence, you can use the {TX_NextPage} option in sql queries to increase it with an amount on each iteration.

For example like this.

You can see the SQL expression is the pagination field * the amount of rows I want each call to have.

I then apply the {offsetValue} as the query parameter. Similar to the other I got a $limit parameter on the actual endpoint.

hi @Thomas Lind ,

Thanks for the reply and the solution.
Managed to make it work by using the {TX_NextPage} as a value for the pagenumber parameter and the stopcondition parameter with the value (*/*/*))1].
Because there where some dataquality issues we also checked the "Perform exhaustive meta data scan" box.
 

Hi @jorisdl 

Good that it worked for you, it is often necessary to check that if all endpoints give back the same setup. 

Hi team & @Thomas Lind,

I am trying to connect an API using the TimeXtender REST provider. The API uses an offset and limit measured in number of records. The parameters that the API accepts are ‘skip’ for the offset and ‘take’ for the limit.

I tried to implement you're example above with SQL, but I am still only getting one page. The setup is as follows: 

And on the endpoint I have the parameter take set to 1000

I am getting one page of 1000 records, with no error messages. I am not sure what API calls are being made or attempted since it is not logged, and the cache just contains the single page. 

For reference, when setting the appropriate parameters in CDATA: 

Pagination is succesfully enabled: 

What am I doing wrong? Is there any way to check if the SQL query for the offset is working? Should I have any parameters set in the ‘parameters’ section of the pagination section?

Kind regards,

Andrew - E-mergo

HI @andrew.gebhard 

I use {offsetValue} in my value field. It is important to add curly brackets around the value so it is known as what to iterate over. So in your case try to add those around the OffsetValue part in the skip query parameter.

Hi @Thomas Lind,

I added curly brackets around the ‘value’. I still get one page of 1000 records:

I am a bit confused about how TimeXtender can determine the {TX_NextPage}.. is that an internal parameter of the API provider? Or was I supposed to add that somewhere as a parameter? 

Kind regards,

Andrew

Hej @Thomas Lind,

Any ideas on how to get this kind of pagination working? As far as I can tell, no query is even being passed on to the SQL Server and the query parameters from the pagination section are not being added to the URI. It would be great to figure this out, because it will be relevant to many other APIs. 

Kind regards,

Andrew