topic Re: Qlik Rest Connector Pagination in Connectivity & Data Prep

Qlik Rest Connector Pagination

DrB1 — Tue, 25 Jun 2024 20:11:27 GMT

I'm new to using API's and I don't understand pagination enough to get all the data from Planning Center People. I connect, and can get data but depending on the settings I choose for pagination, I get anywhere from 25 to 1632 of 6507 records. I don't understand what I should be doing. The API Explorer Planning Center provides for developers (I'm a wannabe developer) gives this server response:

"root":{ 4 items

"links":{ 2 items

"self": "https://api.planningcenteronline.com/people/v2/people"

"next": "https://api.planningcenteronline.com/people/v2/people?offset=25"

}

"data":[ 25 items

0:{... .................... to 25.......

"included":[] 0 items

"meta":{ 8 items

"total_count": 6507

"count": 25

"next":{...}1 item

"can_order_by":[...] 20 items

"can_query_by":[...] 29 items

"can_include":[...] 15 items

"can_filter":[...] 3 items

"parent":{...} 2 items

}

I interpreted this to mean I should use Pagination one of these - I've tried both and cannot get all the 6507 records:

Offset:

'Start' parameter name: offset
'start' initial value: 0
'count' parameter name: 'count'
''count' initial value: 25
'total records' path: root/meta/total_count

Next URL
- 'next url' path: root/meta/next

I tried various "offset" settings but simply cannot get this to work. Any help anyone can provide would be most appreciated. - super indebted!!

Re: Qlik Rest Connector Pagination

marksouzacosta — Tue, 25 Jun 2024 20:31:10 GMT

Hi @DrB1,

I don't have access so far to this API, but it looks similar to one that I did before. Can you please try this on your REST API Connection? Don't worry about Offset or anything else. You should be good.

Re: Qlik Rest Connector Pagination

DrB1 — Tue, 25 Jun 2024 20:35:19 GMT

@marksouzacosta It errors out -

RestConnectorMasterTable << RestConnectorMasterTable

The following error occurred:

(Connector error: HTTP protocol error 429 (Too Many Requests): {"errors":[{"code":"429","title":"Too May Requests","detail":"Rate limit exceeded: 101 of 100 requests per 20 seconds"}]})

Re: Qlik Rest Connector Pagination

marksouzacosta — Tue, 25 Jun 2024 20:39:35 GMT

I think that indicates the pagination is working. But the number of records per call is small compared to the total number of records you have, so you are reaching the limit of requests. I found some details here:
https://developer.planning.center/docs/#/overview/rate-limiting

Let me try to find a better way to get that...

Re: Qlik Rest Connector Pagination

marksouzacosta — Tue, 25 Jun 2024 20:49:53 GMT

Can you try to add this parameter to your URL?
per_page=100
It will be something like: https://api.planningcenteronline.com/people/v2/people?per_page=100

Sorry again, I can't test myself to give you the right answer. Going blind here!

Re: Qlik Rest Connector Pagination

DrB1 — Tue, 25 Jun 2024 21:53:45 GMT

Yes, @marksouzacosta !! Adding the per_page=100 to my URL worked! Thank you so much!

Re: Qlik Rest Connector Pagination

marksouzacosta — Wed, 26 Jun 2024 03:47:22 GMT

You are very welcome.

Re: Qlik Rest Connector Pagination

DrB1 — Sat, 24 Aug 2024 16:38:35 GMT

@marksouzacosta I am learning that in addition to the first url which is considered the "person" data set ( https://api.planningcenteronline.com/people/v2/people?per_page=100) there are 60 or 61 additional urls that contain additional data for each person, for example, https://api.planningcenteronline.com/people/v2/addresses, or https://api.planningcenteronline.com/people/v2/anniversary_couples

I've been reading about ways to loop through the urls to get complete data sets for each person. I've so new at this I'm not sure if it's possible or how to do it. Any thoughts?

Re: Qlik Rest Connector Pagination

marksouzacosta — Mon, 26 Aug 2024 13:00:32 GMT

Hi @DrB1,

Yes, it is possible. I have created a video before that talks about pagination and association between multiple REST API Calls manipulating the WITH CONNECTION attribute from REST Connections and looping. Everything is explained here: https://youtu.be/lFwar30BNkQ?si=7gkyCQAiS1wtT4Yj

You just need to adapt to your REST API Endpoints.

I hope it helps!

Regards,

Mark Costa

Re: Qlik Rest Connector Pagination

marksouzacosta — Mon, 26 Aug 2024 13:07:40 GMT

Oh and @DrB1, I noticed some of the data you can add to your People Endpoint request. For instance, to add the addresses and emails, you can change your URL to add include=addresses,emails:

Example: https://api.planningcenteronline.com/people/v2/people/150356435?include=addresses,emails

This is the list of data sets you can include:

Re: Qlik Rest Connector Pagination

DrB1 — Tue, 27 Aug 2024 18:35:38 GMT

Oh, @marksouzacosta , this may be exactly what I need. I will try it and let you know! Thank you so much! I will also check out the video about looping urls. Thank you !!!