Browsers aren't suited for loading hundreds of rows of data so being able to limit the number and range of rows to return is crucial to ensuring performance. I thought I'd share a very simplified version of my solution to achieve this functionality. 

The Big Picture

This solution utilizes custom HTML (buttons), JavaScript, and stored procedure. The idea is that whenever a user clicks on the "Next" or "Previous" button, it increments / decrements field values that dictate the range of rows to return. These values are passed to a stored procedure which returns rows that fall within this range. 

Forms Layout

Rows to show = single line field = holds the number of rows to display

LowerRange = single line field = holds the lower end of the range to display

HigherRange = single line field = holds the higher end of the range to display

Custom HTML:

<p>Showing results <span class="low">0</span> to <span class="high">5</span></p> <button type="button" id="Prev">Previous</button><button type="button" id="Next">Next</button>

Table = we will fill this as we return results from stored procedure (explained later). For the demo, we have a very simple table with name "Test" and two columns "Name" and "Date". 

Note: I chose to display 5 rows by default but this can be easily modified by changing the default field values. I have assigned a class called "hide" for both LowerRange and HigherRange (used in CSS, shown further down the post). 

JavaScript

I won't get into the details of the logic behind incrementation / decrementation nor the basics of HTML and JavaScript but the code is basically doing this:

1. Whenever user clicks on "Previous", the code decrements LowerRange and HigherRage appropriately, updates the "Showing results x to y" custom html, and runs the stored procedure (explained later)
2. Same idea for "Next"; the code simply does above but instead, increments the field values by the number of rows to display
3. When user changes the "Rows to Show", it adjusts the range, updates the custom html, and runs the stored procedure

Note: the id's of fields (q4, q5, q9) will likely be different for everyone's forms. Visit the link to learn how to find the id's of elements on your form: https://www.softwaretestinghelp.com/locate-elements-in-chrome-ie-selenium-tutorial-7/ 

Realistically, this (number of) "Rows to Show" would be a drop down field where users select 5, 10, 25, 50,...etc rows to show. However, the logic below should still apply. I kept this as a single line field for more flexibility; the user can return however many rows  they want and they aren't confined to certain increments.  

$(document).ready(()=>{
    //Initial Values
    var low = parseInt($('#q4 input').val());
    var high = parseInt($('#q5 input').val());
    var rows = parseInt($('#q9 input').val());

    $('#Prev').on('click',()=>{
        low = low < rows ? 0 : low - rows;
        $('#q4 input').val(low);
	high = high - rows < rows ? rows : high - rows;
	$('#q5 input').val(high);
	update(low, high);
    })
    $('#Next').on('click',()=>{
    	low = parseInt(low) + rows;
        $('#q4 input').val(low);
        high = parseInt(high) + rows;
	$('#q5 input').val(high);
	update(low, high);
    })
    $('#q9 input').on('keyup',()=>{
       	x = parseInt($('#q9 input').val());
	if(x){
          rows = parseInt(x);
      	  high = parseInt(low) + x;
          $('#q5 input').val(parseInt(high))
          update(low, high);
        }
	else{
          $('#q9 input').val(parseInt(rows))
        }
    })
    const update = (lowval, highval) => {
    	$('.low').text(lowval);
      	$('.high').text(highval);
      	$('.autofill').click()
    }
})

SQL Table

For this demo, I kept the tables and procedure as simple as possible. Here's the table (the name of the table is LazyLoad).

Stored Procedure

The stored procedure takes the value from LowerRange and HigherRange and uses them to return range. Again, this is a very simplistic example but you can always modify your stored procedure to add more conditions and change the sorting logic / mechanism. 

A few things you probably want to change here:

1. The first line should be USE [yourdatabasename]. 
2. ALTER PROCEDURE [dbo.LazyLoader] .The "ALTER" should be replaced with "CREATE" when you first run this query to create the stored procedure. Afterwards, you can revert to ALTER.
3. select Name, Date from [dbo].[LazyLoad]. The "Name" and "Date" basically corresponds to the column names from the table "LazyLoad" - make sure these are matching.

Forms Lookup

When you select "Stored Procedure" from the Lookup Tab, you should see the name of the stored procedure. Also, make sure that "Show Autofill button" is checked; whenever user clicks on "Next", "Previous", or updates the "Rows to Show" fields, JavaScript clicks on this button in the background to run the stored procedure. The result set column ... should match the column names of your table. 

CSS

Extremely simple; just hid the LowerRange, HigherRange, and Autofill button.

That's it! The end result should look like this:

DEMO

1) Default on load

2) As soon as you change the "Row to Show", it updates the table

3) Let's say you type 10

And now hit "Next". You can see that the "Showing result x to y" part has been updated and the results is only showing 10-20th row returned by the stored procedure.

4) You can change the Rows to Show anytime

Hope this helps!

For further inquiries and custom forms / workflow development, please reach out to keno@idealogical.com