Forum Discussion
QuantusPools
2 years agoFrequent Visitor
Pagination - Not Returning All The Records
(accessToken as text, apiKey as text, optional page as number, optional data as list, optional lastLoadedDate as datetime, optional hasMore as logical) =>
let
// Set default values for optional parameters
page = if page = null then 1 else page,
data = if data = null then {} else data,
lastLoadedDate = if lastLoadedDate = null then #datetime(2021, 1, 1, 0, 0, 0) else lastLoadedDate,
// Calculate today's date at 11:59:59 PM
today = DateTime.Date(DateTime.LocalNow()),
createdBefore = DateTime.ToText(DateTime.From(today) + #duration(1, 0, 0, -1), "yyyy-MM-ddTHH:mm:ss") & "Z",
// Base URL for the API with page number and createdOnOrAfter and createdBefore filters
BaseURL = "https://api.servicetitan.io/jpm/v2/tenant/824293295/jobs?page=" & Text.From(page) & "&includeTotal=true&createdOnOrAfter=" & DateTime.ToText(lastLoadedDate, "yyyy-MM-ddTHH:mm:ss") & "Z" & "&createdBefore=" & createdBefore,
// API request headers
Headers = [
Headers = [
Authorization="Bearer " & accessToken,
#"ST-App-Key" = apiKey
]
],
// Make the API request
Source = Json.Document(Web.Contents(BaseURL, Headers)),
// Extract the 'hasMore' property, current data, and total count
hasMore = Source[hasMore],
currentData = Source[data], // Assuming 'data' is the correct field for your data
totalCount = Source[totalCount],
// Convert the createdOn field to datetime
currentDataWithDatetime = List.Transform(currentData, each Record.TransformFields(_, {{"createdOn", each DateTime.FromText(Text.BeforeDelimiter(_, "Z"))}})),
// Combine the current data with the existing data
appendedData = List.Combine({data, currentDataWithDatetime}),
// Increment the page counter
nextPage = page + 1,
// Determine the latest loaded date from the current data
newLastLoadedDate = List.Max(List.Transform(currentDataWithDatetime, each [createdOn])),
// Determine the output
output =
if hasMore = false or List.Count(appendedData) >= totalCount
then
// Return the appended data and the new last loaded date
[data = appendedData, lastLoadedDate = newLastLoadedDate]
else @#"GET Activity"(accessToken, apiKey, nextPage, appendedData, newLastLoadedDate, hasMore)
in
output
I have been trying to get the pagination to work with this API but it is not returning all the records. It is currenlty only returning 428 records when it should be roughly 52k. Any idea what is wrong?
1 Reply
- AnonymousNot applicable
Hi QuantusPools ,
Double-check the API documentation to make sure you are using the paging parameters correctly.
Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.