Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you very much for your answer Anonymous !

 

I am so sorry, but I have no clue, how your answer helps me.

 

JIRA Rest API is limited to 1000 rows. As I understand it right, the problem is, I need some looping mechanism to get all datas. List.Generate seems like to act like a loop. But unfortunately I don´t get to work...

 

So I hope somebody already had the same problem.

 

freiburgc

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Here is a sample that I used on a similar issue. You can use list.generate in a similar way to loop thru all pages.

 

 

let
BaseUrl = "http://xxxx",
Token = [Headers=[#"key"="xxx"]],
EntitiesPerPage = 1000,
WebCall = try Json.Document(Web.Contents(BaseUrl,Token)),
Value = WebCall[Value],
count = Value[totalHits],
countMax = Number.RoundUp(List.Max({1,count/EntitiesPerPage})),

nextURL = (counter,sid ) =>
let
url = BaseUrl & "&pageNumber=" & Text.From(counter) & "&scrollId="&Text.From(Record.Field(sid,"Value")),
call = Web.Contents(url,Token)
in
call,

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(url),
data = try Source[featureMatchEvents] ,
next = try Source[scrollId] ,
res = [Data=data, Next=next]
in
res,


GeneratedList =
List.Generate(
()=>[i=1, res = FnGetOnePage(nextURL(1,[Value=""]))],
each [i]<countMax,
each [i=[i]+1, res = FnGetOnePage(nextURL(i,[res][Next]))],
each [res][Data]),