DEV Community

Sarah Schlueter
Sarah Schlueter

Posted on

Resolving API Records Limit Issue with Pagination

For a bit of context, Kronos is a workforce management software use to help employers manage their employees, time and attendance, scheduling and payroll. This post isn't necessarily supposed to be a tutorial, but I wanted to document the experience with this since it was helpful to me, it may be helpful to someone else. Please feel free to comment if you have questions or would like further clarification or context on something.

I was writing a program that would pull records from saved reports using their report IDs from Kronos. It was known that the API call had a limit of 100,000 records so the reports were saved in a way that they had under 100k records.

This was fine until all of the sudden, I'm calling the API to get reports data in order to insert into a SQL database, and the result set is only 50,000 records when previously it was around 60-70k records.

After checking the documentation, I realized I could resolve this issue using pagination. Here's how I tested and resolved the issue...

Set up and Testing in Postman

I created a collection in Postman titled 'Kronos Reports' and set up my requests. The first POST request for logging in to the tenant and retrieving the token, setting it as an environment variable to use in the next POST request for retrieving the saved reports. The APIs used:

{{baseurl}}/ta/rest/v1/login
{{baseurl}}/ta/rest/v1/report/saved/{{reportID}}

I initially had started out using their GET request for the reports, however, in order to apply pagination you need to use a post request and put the following json in the response body:

{
    "page": {
        "number": 0,
        "size": 50000
    }
}
Enter fullscreen mode Exit fullscreen mode

Page number needs to start at 0, and will return the first 50000 records (or whichever amount you choose). I set it at 50,000 since that seemed to be the cap after throttling.

Once I set my relevant headers, I created a Post-response script to write the record counts to the console:

const xml2js = require('xml2js');
let jsonResponse;
xml2js.parseString(pm.response.text(),{explicitArray: false}, (err, result)=>{

    jsonResponse = result;

});
const rows = jsonResponse.result.body.row;
const rowCount = Array.isArray(rows) ? rows.length : (rows ? 1 : 0);
console.log("Number of rows:", rowCount);
Enter fullscreen mode Exit fullscreen mode

Then I could call the API and see that for page 0, records returned were 50000, set the page to 1 and ran again which showed the records returned were 18927. Perfect, we're done.

Implementing in the code

My project is a .NET 8.0 Console App written in C# and using the latest version of RestSharp (version 111.4.1 at the time of this writing).

I adjusted my API call to use Method.Post, and wrapped it in a while loop. I set a bool variable hasMorePages initially to true, and created variables for the page number and page size, initially set to 0 and 50000, respectively.

RestSharp has an AddJsonBody() method in which I used to insert the pagination json code to the request body:

request3.AddJsonBody(new
{
    page = new
    {
        number = pageNumber,
        size = pageSize
    }
});
Enter fullscreen mode Exit fullscreen mode

I also created another variable List<row> allRecords to add the results to. The code loops through as long as hasMorePages is true. If the results count is less than the page size, hasMorePages is assigned to false and exits the loop. Otherwise, the pageNumber is incremented by 1.

Adding Records from Multiple Reports to be added to Database together

Since we have the 100,000 record limit on pulling saved reports data, this resulted in splitting our report into 3 parts. Since we will run this daily to update the data, we want to combine all of the reports in the code so that we only need to run it and insert into the database once.

For this I just wrapped my API call in the while loop in a foreach loop that loops through a list of the reportIDs, passing the temporary reportId variable in the RestRequest.

Here is the final API call code:

var options = new RestClientOptions(strBaseURL)
{
    ConfigureMessageHandler = handler =>
    {
        var httpClientHandler = (HttpClientHandler)handler;
        httpClientHandler.CookieContainer = new CookieContainer();
        httpClientHandler.UseCookies = true;
        return httpClientHandler;
    },
    Timeout = Timeout.InfiniteTimeSpan,
    UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36"
};

var client = new RestClient(options);
var request2 = new RestRequest("/ta/rest/v1/login", Method.Post);
request2.AddHeader("Api-Key", strAPIKey);
request2.AddHeader("Accept", "application/json");
request2.AddParameter("application/json", JsonConvert.SerializeObject(me), ParameterType.RequestBody);
var response2 = await client.ExecuteAsync(request2);
Kronos_Login_ResponseBody responsebody = JsonConvert.DeserializeObject<Kronos_Login_ResponseBody>(response2.Content);

string strToken = responsebody.token;

bool hasMorePages = true;
int pageNumber = 0;
int pageSize = 50000;

var options2 = new RestClientOptions(strBaseURL)
{
    ConfigureMessageHandler = handler =>
    {
        var httpClientHandler = (HttpClientHandler)handler;
        httpClientHandler.CookieContainer = new CookieContainer();
        httpClientHandler.UseCookies = true;
        return httpClientHandler;
    },
    Timeout = Timeout.InfiniteTimeSpan,
};

var client2 = new RestClient(options2);
List<Row> allRecords = new List<Row>();

// just example report numbers
List<string> reportIds = new List<string> { "123456789", "987654321", "456478415" };

foreach (string reportId in reportIds)
{
    while (hasMorePages)
    {
        var request3 = new RestRequest($"/ta/rest/v1/report/saved/{reportId}", Method.Post);
        request3.AddHeader("Authentication", "Bearer " + strToken);
        request3.AddHeader("Accept", "application/xml");
        request3.AddHeader("company", strCompanySysID);
        request3.AddHeader("Content-Type", "application/json");

        request3.AddJsonBody(new
        {
            page = new
            {
                number = pageNumber,
                size = pageSize
            }
        });

        var response3 = await client2.ExecuteAsync(request3);

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(response3.Content);

        string KronosXMLResponse = JsonConvert.SerializeXmlNode(doc);
        Root KronosEmployeeHoursReport = JsonConvert.DeserializeObject<Root>(KronosXMLResponse);

        allRecords.AddRange(KronosEmployeeHoursReport.result.body.row);

        if (KronosEmployeeHoursReport.result.body.row.Count < pageSize)
        {
            hasMorePages = false;
        }
        else
        {
            pageNumber++;
        }
    }

    hasMorePages = true;
}
Enter fullscreen mode Exit fullscreen mode

Further, you want to add ordering by fields to the body so that each page doesn't produce duplicates. You can get the field names by calling the GET request with the additional /metadata on the end after the report id to include in the body of the post request. Here I used the employeeID and date:

{
    "page": {
        "number": 0,
        "size": 50000
    },
    "fields": [
        "EmplEmployeeId",
        "CalendarDate"
    ],
    "orders": [
        {
            "field_name": "EmplEmployeeId",
            "is_descending": false
        },
        {
            "field_name": "CalendarDate",
            "is_descending": false
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

C# code:

request4.AddJsonBody(new
{
    page = new
    {
        number = pageNumber,
        size = pageSize
    },
    orders = new[]
    {
        new
        {
            field_name = "EmplEmployeeId",
            is_descending = false
        },
        new
        {
            field_name = "CalendarDate",
            is_descending = false
        }
    }
});
Enter fullscreen mode Exit fullscreen mode

Resources:
https://secure2.saashr.com/ta/docs/rest/public/?r=v1report_saved_(settings_id)
https://restsharp.dev/docs/usage/request/#json-body
https://community.postman.com/t/occational-warning-that-xml2json-is-deptrecated/59745/5

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay