Klipfolio Datasource : Zendesk Pagination
Disclaimer:Zendesk API response can have large number of pages, which can create large number of datasource instances, it can cause performance issues on your dashboards. you can limit the number of datasource instances by limiting the number of results the query brings back. Zendesk limits the number of results per query to 100. If you have more than 100 results you need to use their pagination mechanism to get the next page. You can use the Zendesk incremental ticket export. Explained here. You can also limit the data by filtering it using date parameters and getting the data for certain period of time.
They have an optional parameter called “page” which you can specify in the query. More details are given in the documentation here.
For example to pull the number of users on second page you can use following query.
https://subdomain.zendesk.com/api/v2/users.json?page=2
Klipfolio has a concept called dynamic data sources which allows you to make the page number as a dynamic property instead of static value and using the formulas you can pull all the available data from multiple pages.
Each value of the dynamic property will create a dynamic datasource instance in the background. You can use instructions about troubleshooting dynamic data sources and remove stale/bad/unwanted instances.
For example, say you create called pageNum in the Klipfolio Klip editor, assign a default value =1 to the same. Instructions to create variable are linked here.
You can use the same variable in the above query as shown below.
https://subdomain.zendesk.com/api/v2/users.json?page={props.pageNum}
Now , you need to configure your formulas to loop through the number of available pages and get all of the records.
Following query gives me the list of tickets solved in the last month. Please note that I am using {props.pageNum} as dynamic property. Also {date.addMonths(-1).format()} as dynamic date. You can use also date parameters in data source queries.
https://<your_subdomain>.zendesk.com/api/v2/search.json?page={props.page}&query=type:ticket+solved>{date.addMonths(-1).format()}
To select all the ticket ids returned by the above datasoure, you can use MAP function as shown below.
New Information : Now instead of going all this trouble of joining all the results together and then making an array you can use MAPFLAT function, thanks to Klipfolio. So formula is much simpler now.
MAPFLAT(CUMULATIVE ( REPEAT ( 1, ( CEILING ( count/100)))) ,“pagenum”, (id))))
--------
Let’s break it down to understand. In this formula CUMULATIVE ( REPEAT ( 1, ( CEILING ( count/100)))) will generate an array of (1,2,3..)
Here count is number of total items in the result of the query.
For example if count = 234 the CUMULATIVE function will return 1,2,3 which are the number of pages we have for the result.
MAP function will act as a loop, where first parameter is a list of values to loop though; in this case it is array of offset values. In this loop, for each iteration, one of the values from this array will be assigned to a variable called “pagenum” specified as a second parameter of the MAP function. You will manually enter the name of the variable as a string there.
Remember while creating the datasource you have used it as page=: {props.pagenum} ? Every time the pagenum variable value is changed a new instance of this datasource will be created.
MAP (CUMULATIVE ( REPEAT ( 1, ( CEILING ( count/100)))) ,“pagenum”, JOIN(id) )
Third part here JOIN (id) will give you joined list of ticket ids separated by comma for a single instance of the dynamic datasource. So for single page here you will get a comma separated ticket ids from that page.
You can use JOIN to join the Ids from one instance in a single value separated by comma, as MAP will return one value for each input value of the variable
Now in this example you will have 3 sets of joined values for each datasource instance, you can JOIN all of them together and convert them to an array to get the list of 234 ids in this example.
ARRAY( JOIN(MAP(CUMULATIVE ( REPEAT ( 1, ( CEILING ( count/100)))) ,“pagenum”, JOIN(id))))
Happy Dashboarding!
