Rest API - Pagination Not Working

[Kevin-Marlow]

Hi,

I'm using the REST API connector and I'm successfully able to connect to the data source, however the JSON service will only return 100 items per page and I need to pull 1,000's of items.  I have checked the Response Header and the next page is  displayed as 'link' item.  If I select 'Pagination', 'Next URL' and enter '_response_header/link' as the 'Next URL' path, check 'Look in header' and run the reload it still only loads the first 100 items and never goes to the second page.  Am I misunderstanding the use of 'Next URL' or is this functionality not supported?

Thanks,

Kevin

[Levi_Turner]

Let's take an example which is actually how Qlik Cloud Services (QCS) forms their responses.

Response:

{
  "data": [
      {
          "name": "AppName",
          "resourceAttributes": {
              "_resourcetype": "app",
              "createdDate": "2019-12-12T17:43:32.964Z",
              "description": "",
              "dynamicColor": "",
              "hasSectionAccess": false,
              "id": "2694b8ce-0711-4bb7-ab98-08ca66daa45c",
              "lastReloadTime": "2019-12-12T17:46:12.060Z",
              "modifiedDate": "2019-12-12T17:46:12.461Z",
              "name": "AppName",
              "originAppId": "",
              "owner": "DOMAIN\\UserId",
              "ownerId": "ownerID",
              "publishTime": "",
              "published": false,
              "spaceId": "",
              "thumbnail": ""
          }
    }
  ],
  "links": {
      "self": {
          "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items"
      },
      "next": {
          "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items?next=FgAAAAdfaWQAXj28vOUOhQABwV2UAA"
      }
  }
}

For the Qlik REST Connector, the config for pagination URL would be:

• root/links/next/href

The root bit is needed as an artifact of the Qlik REST Connector. The remainder is a straight-forward path to the element in the response.

Hope that helps.

[Kevin-Marlow]

Maybe it's a limitation of the API I'm using but we don't see at the root level.  We are using the FreshService API:  https://api.freshservice.com/v2/#pagination

Pagination

API responses that return a list of objects, such as View List of Tickets are paginated. To scroll through the pages, add the parameter page to the query string. The page number starts with 1.

https://domain.freshservice.com/api/v2/tickets?page=2

By default, the number of objects returned per page is 30. This can be adjusted by adding the per_page parameter to the query string. The maximum number of objects that can be retrieved per page is 100. Invalid values and values greater than 100 will result in an error.

https://domain.freshservice.com/api/v2/tickets?per_page=10

The per_page and page parameters can also be used together. The following example will retrieve the 11th to 20th tickets.

https://domain.freshservice.com/api/v2/tickets?per_page=10&page=2

The 'link' header in the response will hold URL of the next page, if it exists. If you have reached the last page of objects, then the link header will not be set.

Headers:
"link":< https://domain.freshservice.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"

The Qlik Connector is generating the following code:

RestConnectorMasterTable:
SQL SELECT
"Transfer-Encoding",
"Connection",
"status" AS "status_u0",
"x-freshservice-api-version",
"pragma",
"x-xss-protection",
"x-request-id",
"link",
"x-frame-options",
"x-content-type-options",
"x-envoy-upstream-service-time",
"x-fw-ratelimiting-managed",
"x-ratelimit-total",
"x-ratelimit-remaining",
"x-ratelimit-used-currentrequest",
"Cache-Control",
"Content-Type",
"Date",
"Expires",
"__KEY__response_header"
FROM JSON "_response_header" PK "__KEY__response_header";

[_response_header]:
LOAD [Transfer-Encoding],
[Connection],
[status_u0],
[x-freshservice-api-version],
[pragma],
[x-xss-protection],
[x-request-id],
[link],
[x-frame-options],
[x-content-type-options],
[x-envoy-upstream-service-time],
[x-fw-ratelimiting-managed],
[x-ratelimit-total],
[x-ratelimit-remaining],
[x-ratelimit-used-currentrequest],
[Cache-Control],
[Content-Type],
[Date],
[Expires],
[__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);

DROP TABLE RestConnectorMasterTable;

and I'm see the following response:

Date: Tue, 03 Mar 2020 16:35:38 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
status: 200 OK
cache-control: no-cache, no-store, max-age=0, must-revalidate
x-freshservice-api-version: latest=v2; requested=v2
pragma: no-cache
x-xss-protection: 1; mode=block
x-request-id: 4952f88f-9436-4254-8f99-efd13dcf84bc
link: <https://freshservice.com/api/v2/tickets?page=2>; rel="next"
x-frame-options: SAMEORIGIN
x-content-type-options: nosniff
expires: Wed, 13 Oct 2010 00:00:00 UTC
x-envoy-upstream-service-time: 45
x-fw-ratelimiting-managed: true
x-ratelimit-total: 5000
x-ratelimit-remaining: 4998
x-ratelimit-used-currentrequest: 1

[AHMB]

were you able to resolve this issue?

I'm facing the same problem with Freshdesk REST API.   the pagination using "Next URL" does not work.

[Kevin-M · Accepted solution]

Yes, but I stopped using Pagination and started using 'With Connection'

Let startAt = 1;
Set Page = 500;
Let ValidateCount= 0;
Let FirstRun = 1;
For startAt = 1 to $(Page) step 1

LIB CONNECT TO 'FreshService_Tickets';

RestConnectorMasterTable:
SQL SELECT
"fr_escalated",
"spam",
"email_config_id",
"group_id",
"priority",
"requester_id",
"responder_id",
"source",
"status",
"subject",
"department_id",
"id",
"type",
"due_by",
"fr_due_by",
"is_escalated",
"description",
"description_text",
"category",
"sub_category",
"item_category",
"created_at",
"updated_at",
"deleted",
"to_emails",
"__KEY_tickets",
(SELECT
"@Value",
"__FK_cc_emails"
FROM "cc_emails" FK "__FK_cc_emails" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_fwd_emails"
FROM "fwd_emails" FK "__FK_fwd_emails" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_reply_cc_emails"
FROM "reply_cc_emails" FK "__FK_reply_cc_emails" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_to_emails"
FROM "to_emails" FK "__FK_to_emails" ArrayValueAlias "@Value_u2"),
(SELECT
"__FK_custom_fields"
FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap off) "tickets" PK "__KEY_tickets"
WITH CONNECTION(Url "https://<company>.freshservice.com/api/v2/tickets?per_page=100&page=$(startAt)");

Let CurrentLoad = NoOfRows('RestConnectorMasterTable');
Let ValidateCount= If($(ValidateCount)=0,NoOfRows('RestConnectorMasterTable'),$(ValidateCount));
Let startAt = If($(FirstRun)=0,If($(ValidateCount)=$(CurrentLoad),$(Page),$(startAt)),$(startAt));
Let ValidateCount = NoOfRows('RestConnectorMasterTable');
Let FirstRun = 0;
NEXT startAt;

[gamaescalante]

That worked for me testing out the Departments Get Call.  Appreciate your contribution to the community, seeing the same 30 departments was racking my brain!

[JBR1993]

Thnx! I've had some difficulties with the paging REST API, but this solutions works perfect!

[jerry654]

1. Verify the Endpoint:

Ensure that you are making the API requests to the correct endpoint that supports pagination. The endpoint should be designed to handle pagination, typically using query parameters like page and per_page.

https://domain.freshservice.com/api/v2/tickets?page=2

By default, the number of objects returned per page is 30. This can be adjusted by adding the per_page parameter to the query string. The maximum number of objects that can be retrieved per page is 100. Invalid values and values greater than 100 will result in an error.

https://domain.freshservice.com/api/v2/tickets?per_page=10

The per_page and page parameters can also be used together. The following example will retrieve the 11th to 20th tickets.

https://domain.freshservice.com/api/v2/tickets?per_page=10&page=2

https://dtchiropractic.com

The 'link' header in the response will hold URL of the next page, if it exists. If you have reached the last page of objects, then the link header will not be set.

Headers:
"link":< https://domain.freshservice.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"

2. Check Query Parameters:

Review the query parameters you're using for pagination. Common parameters include page, per_page, limit, and offset. Make sure they are correctly formatted and are being sent in the API request.

[JQlik5]

Hi,

Can share me sample code for more understanding ....Thanks for added comments on my query...