REST/oData "paging" to retrieve more than 20k rows from a Creatio table

Hi.  This is a question for anyone there who is knowledgeable in Creatio’s implementation of oData…

We're connecting our BI tool “Qlik” to Creatio via the OData service and in our first test we, of course, have hit the 20,000-record limit per request. To retrieve full datasets (e.g., from the 'Opportunities' table), we need to configure paging in the Qlik connector (using Qlik’s native REST connector).

Like other BI tools, there are several ways to do paging in Qlik -- depending how it is implemented by the oData provider.  
(I refer to this document at Qlik:  Loading paged data | Qlik Connectors Help )

Can anyone tell me which paging method is used by Creatio's OData feed?
Typical options are:

  • Next URL   (e.g., @odata.nextLink)
  • Offset   (e.g., using $skip/$top)
  • Next Token
  • Next Page   (using field values)
  • Custom
    • If a custom script using parameters like $skip and $top is required,  I'd need an example of how to structure the calls to loop through and get all records.

Thanks in advance for any guidance you can provide!
~~~ Rob

The screencap below is the configuration screen for the Qlik REST connector -- It will probably look like other BI vendors connectors.   The current connection works, as long as we limit the resultset to $top=20000:

qlik

Like 1

Like

2 comments

Creatio OData API uses $top and $skip to retrieve the data in pages. 

For example: 

Page 1 (first 1000)

https://creatiourl/0/odata/contacts?$top=1000&$skip=0

Page 2 (next 1000) 

https://creatiourl/0/odata/contacts?$top=1000&$skip=1000

Page 3 (next 1000) 

https://creatiourl/0/odata/contacts?$top=1000&$skip=2000

etc (I have no idea how that translates to your Qlik dialog)

Ryan


Hi Ryan --

Yes, that part I got working in Postman.  The tricks are:
1)  get the BI tool Qlik to connect using their REST connector
2)  configuring the connector to automatically do the skips, and load all the data
3)  configure the connector to automatically get a fresh security token from Creatio each time the connector is called.  (OR, make a token that doesn't expire)

I got #1 and #2 ... working on #3.  I had to figure out which "pagination type" would work with Creatio's version of the oData implementation. 

Once I got "Offset" right, I was able to use the $skip and $top parameters to make the connector go get 10000 records, and loop until there were no more.  (This image is part of the Qlik REST connector settings)

 

pagination

--- regards,
---Rob

Show all comments