REST Connector & Pagination on Pipedrive

    This document explains some issues identified in testing the REST connector with Pipedrive data using the Start & Limit parameters for paging the output results from the Pipedrive API.

     

    I have been using the REST Connector to connect to Pipedrive data using the information in the REST Connector documentation and from the Pipedrive API site.  Pipedrive API v1 API Documentation
    This proved to be challenging to get pagination working consistently.

     

    Looking at the Pipedrive API, a single query returns a maximum of 500 rows. To get more data, then the data must be requested in separate queries, each returning 500 rows. This paging is enabled by setting a "start" and "limit" parameter in the api call.

     

    Whilst this appears straightforward according to the Pipedrive documentation, when this is applied to the Qlik REST connector it becomes a bit more fun.

     

    The pagination settings in the REST connector seem to prefer the information for the page settings to be in the header of the returned data. The issue with pipedrive is that the pagination table is in the footer of the results. This can cause the SELECT option for the REST connector to completely miss the additional tables unless a small value for the limit parameter is used.

     

    The API call I have been using is https://api.pipedrive.com/v1/deals?pretty_output=1&start=0&limit=500&api_token=**********

    to return the first 500 deals. You can use this in Chrome to preview the results.
    The "pretty_output=1" option just formats the data in the browser.

     

    At the end of the results set is the additional data pagination table showing the start, limit and the next_start value, as well as other tables under related objects

     

    "org_hidden":false,

      "person_hidden":false

      }

      ],

    "additional_data":{

      "pagination":{

      "start":0,

      "limit":9,

      "more_items_in_collection":true,

      "next_start":9

      }

      },

      "related_objects":{

      "user":{

      "1106711":{

      "id":1106711,

     

    If this same query is used to create a REST connection, the subsequent select option below connect only offers the data tables in the results, as shown below.

    REST_missing_tables.JPG

     

    If instead the limit is set to a low value, say 5 instead of 500, then the data, pagination & related objects are all visible under root.

     

    REST_limit5.JPG

    When testing both of these queries in Chrome (IE does not display JSON results by default), the pagination tables and related_objects are included, but these tables only appear to be read by the select statement that builds the object & fields list if in they are not too far down the result set.

     

    In testing we found that setting the limit at 19 records or less would show the additional data, but using a 20 record limit or higher, the additional data tables would not be displayed in the Objects & Fields list!

     

    Once the select statements have been created in the wizard against the small limit value, the connect statement can be edited to increase the limit to the maximum value of 500 and the script will execute successfully, or you can replace the fixed values with variables in a Do..Until loop to load all data rows.

     


    This has been tested on both QlikView 11.2 SR15 and v12 SR4 using the REST Connector v1.0.

     

    This may explain some of the questions relating to the REST connector and pagination that have been posted on the community.

     

    Colin Albert, K3FDS.