Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Articles
Search: 

Pagination in Visual FoxPro
Rafael Copquin, July 1, 2006
When we are examining data from a table, using generally a grid control, even if we did it with a browse window, we are in reality viewing a small part of a, perhaps, enormous quantity of data. We are literally viewing only the amount of records that fit in the grid or the browse window. If we wa...
Summary
When we are examining data from a table, using generally a grid control, even if we did it with a browse window, we are in reality viewing a small part of a, perhaps, enormous quantity of data. We are literally viewing only the amount of records that fit in the grid or the browse window. If we want do see more records, as Visual FoxPro users used to the ease of handling of the product interfase, we simply click on the page down and page up buttons and, if the cursor is on the top or on the last rows, clicking down or up on the side bar of the grid, we make the screen move forward or backwards, viewing an additional quantity of records. This is a class for bringing back from the data source only those records that can be seen in a page at any one time.
Description
When we are examining data from a table, using generally a grid control, even if we did it with a browse window, we are in reality viewing a small part of a, perhaps, enormous quantity of data.

We are literally viewing only the amount of records that fit in the grid or the browse window. If we want to see more records, as Visual FoxPro users used to the ease of handling of the product interfase, we simply click on the page down and page up buttons and, if the cursor is on the top or on the last row, clicking down or up on the side bar of the grid, we make the screen move forward or backwards, viewing an additional quantity of records.

Overview

In the case of the page down or page up keys, we see another "page", that is, a new set of rows that fit into the visible frame of the browse window or grid control.

But we can only see 20,30 or some more records, according to the size of the window. If the table or cursor the data come from had 100 or 1,000,000 records, there would be no difference: we would only be able to see a few lines, always constrained by the size of the grid control or the browse window.

But we know that we have a greater quantity of data in the data source. Let us assume that we are viewing a price list from a car spare parts reseller. We know that each car is composed by thousands of pieces and that they can be grouped by product (carburetors, engines, shock absorbers, etc) and that in turn these groups can be further subdivided into hundreds of different pieces. A simple multiplication renders a total quantity that can be measured in tens of thousands of items. If our client carries a complete stock of those pieces, their price catalogues are hundreds of pages thick.

Let us think therefore that we have a table (I simplify the example thinking it is about a single table, even though in reality the elements of the list reside in different normalized tables, according to families, groups, sets, etc.)

In any case, in order to be able to work with those data, knowing that they arise from a combination of tables, a well configured select statement would result in a cursor which in the end, would be the one we show in the grid.(In the rest of this article I am going to refer to a grid control and bring the data from a single table to simplify things. In real life, the class should receive as parameter an appropriate select statement, as per our needs)

The question that comes to mind then is: how many records should I bring from the data source (either residing in a VFP database or in SQL Server databases or other sources), to minimize network traffic and, at the same time, refresh the screen quickly and efficiently every time the user wants to see another "page" ?

Obviously, I am not going to bring back thousands of records when I can only show a few at a time.

The answer is also obvious: bring only the 20 or 30 records that can be shown in that page and afterwards, pressing the page down or page up keys, bring another set of 20 or 30 records and so on.

As everything, it is very easy to ask, very easy to give theoretical answers but, when we try to carry out in practice those easy answers, things do not look that easy any more.

There are many alternatives at our disposal and they consist basically of filtering the data according to varied criteria. In the price list example developed in this article, filters can vary from a family, a group or a certain heading to a stratification based on price, or car models or whatever.

And making those filters is the easy part of the answer: all that one has to do is build a good select statement and produce a cursor as per those filters and voilá, we get the data that we need.

But the main problem is still there: that cursor can also have thousands of records. And if we bring back thousands of records from their source in any query, we will be necessarily generating a very big traffic in the network.

Let us think of a store with 50 salespeople, each one with their own machine, trying to reach the price list at the same time. If every one of them is going to bring back to their PC thousands of records every time they make a global query, the network can become very slow and overall performance will degrade.

In order to solve this problem, I thought of designing a class that, among other advantages, can be reused.

Breaking down the problem

We want to instantiate the class sending but a few parameters to it and, by calling its methods every time we click on the applicable navigation buttons, produce the expected results, that is, bring back the records that will be seen at that time.

Let us see what parameters we should send to the class to make it work:

  1. name of the base table
  2. name of the cursor to be viewed
  3. we shall have to order this cursor by a certain field, so we send the name of the field
  4. the quantity of rows that will be shown on the grid
  5. the name and location of the grid (it could be on a pageframe, on the form or on a formset)
  6. the name of the method on the form to be called every time we put the cursor on a record on the grid
Consequently, we create a property on our form to instantiate the class and subsequently refer to it in the applicable methods.
thisform.addproperty('oNV')
and we then instantiate the navigation object using that property:

(we will assume that we have an item table called ITEMS.DBF and view the necessary records in a cursor called CURITEMS.

Furthermore we shall assume the class to be called NAVIGATE)

This is the way in which the navigation object is instantiated:

thisform.oNV = createobject("navigate","ITEMS.DBF","CURITEMS",thisform.cOrderField,;
 thisform.nRowQty,"grid1","navigation")

The form property nRowQty is the result of the calculation of the quantity of rows in the grid, made by the INIT method of the grid, in the following way:

thisform.nRowQty = int ( ( this.Height - this.HeaderHeight ) / this.RowHeight )
This very simple calculation renders an almost exact number of rows in the grid, depending of course on the font name and size that we use for the grid. In this example, I use the Arial font with a 9 point size. The reader should modify this calculation as per the font and size which will be used on the grid.

Creation of the class

The class should have methods to advance or go back one page, go quickly to the beginning or the end of the table and, besides, allow navigation back and forth for every one of the records shown on the grid at a given time.

Furthermore, and because grids are messed up when the table or cursor they are based on changes, it is necessary to provide a method to reconfigure it, every time a new cursor is brought back when pagination occurs.

Let us see the complete class and then we shall analize the code:

DEFINE CLASS NAVIGATE AS SESSION

DataSession = 1

PROCEDURE INIT( tcTable          as String  ,;
		tcAlias          as String  ,;
		tcOrderField     as String  ,;
		tnRowQty         as Integer ,;
		tcGrid           as string  ,;
		tcNavigateMethod as string  ,;
		tcSortMethod     as string )


this.AddProperty("cTable",tcTable)			&& table to obtain records from
this.AddProperty("cAlias",tcAlias)			&& cursor that populates grid
this.AddProperty("cOrderField",tcOrderField)		&& field by which to sort cursor
this.AddProperty("nRowQty",tnRowQty)			&& grid row quantity (calculated
                                                           in grid init method)
this.AddProperty("cGrid",tcGrid) 			&& form's grid name and location
this.AddProperty("cNavigateMethod",tcNavigateMethod)    && form's navigate method name

ENDPROC

PROCEDURE MOVEBOTTOM

Local cCmd

this.blankgrid()

cCmd = [ Select top ]+transform(this.nRowQty)+[ * from ]+this.cTable+;
       [ order by ]+this.cOrderField+[ desc into cursor ]+this.cAlias

ExecScript( cCmd)

this.resetgrid()
this.navigation()

ENDPROC

PROCEDURE MOVETOP

Local cCmd

this.blankgrid()

cCmd = [ Select top ]+transform(this.nRowQty)+[ * from ]+this.cTable+;
       [ order by ]+this.cOrderField+[ asc into cursor ]+this.cAlias


ExecScript( cCmd)

this.resetgrid()
this.navigation()

ENDPROC

PROCEDURE MOVENEXT

Local lFileEnd

Select (this.cAlias)

if not eof()
   skip
   lFileEnd = .f.
   if eof()
      go bottom
      lFileEnd = .t.
   endif
else
   go bottom
   lFileEnd = .t.
EndIf

If lFileEnd = .t.
   this.next_page()
Else
   If Len(Alltrim(this.cNavigateMethod)) > 0
      this.navigation()  
   endif   
endif

ENDPROC

PROCEDURE MOVEPREVIOUS

Local lFileTop

Select (this.cAlias)

if not bof()
   skip -1
   lFileTop = .f.
   if bof()
      go top
      lFileTop = .t.
   endif   
else
   go top
   lFileTop = .t.
endif

If lFileTop = .t.
    this.previous_page()
Else
   If Len(Alltrim(this.cNavigateMethod)) > 0
       this.navigation()
    endif   
endif

ENDPROC

PROCEDURE PREVIOUS_PAGE

Local cOrderField,cFirst,cCmd

Go top in (this.cAlias)

cOrderField = this.cTable+[.]+this.cOrderField 
cFirst      = evaluate(cOrderField)

this.blankgrid()



cCmd = [ select top ]+transform(this.nRowQty)+[ * from ]+this.cTable+;
       [ into cursor ]+this.cAlias +[ where ]+ cOrderField+ [ < ]+["]+cFirst+["]+;
       [ order by  ]+this.cOrderField+[ desc ]



ExecScript( cCmd)

select (this.cAlias)
If Reccount() = 0
   this.movetop()
else
   this.resetgrid()
   this.navigation()
endif

ENDPROC

PROCEDURE NEXT_PAGE

Local cOrderField,cCmd,cLast

Go bottom in (this.cAlias)

cOrderField =  this.cTable+[.]+this.cOrderField 
cLast = evaluate(cOrderField)

this.blankgrid()


cCmd = [ select top ]+transform(this.nRowQty)+[ * from ]+this.cTable+;
       [ into cursor ]+this.cAlias +[ where ]+ cOrderField+ [ > ]+["]+cLast+["]+;
       [ order by  ]+this.cOrderField+[ asc ]


ExecScript( cCmd)

select (this.cAlias)
If Reccount() = 0
   this.movebottom()
else 
	this.resetgrid()
	this.navigation()
endif

ENDPROC

PROCEDURE NAVIGATION

** this is a hook method that calls your form's navigation method
** this is what your form would do every time you click on your navigation bar
** Example: show data of a child table in a second grid

Local cNavigateMethod

cNavigateMethod = [_screen.activeform.]+this.cNavigateMethod+[()]

ExecScript(cNavigateMethod)

ENDPROC

PROCEDURE BLANKGRID

** you must blank the grid's recordsource prior to running a select
** statement that changes the cursor

Local cRecordSource

cRecordSource = [_screen.activeform.]+this.cGrid+[.recordsource = ""]

ExecScript(cRecordSource)

ENDPROC

PROCEDURE RESETGRID

Local cSetGrid,cSetFocus

cSetGrid  = [_screen.activeform.set_grid()]   
cSetFocus = [_screen.activeform.]+this.cGrid+[.column1.setfocus]

ExecScript(cSetGrid)
ExecScript(cSetFocus)

ENDPROC

ENDDEFINE

The MoveBottom and MoveTop methods

We use the TOP clause of the SELECT command to bring back the quantity of records that will be shown, determined by the nRowQty property of the form. The difference between going to the end or the beginning of the list is simply adding another clause: DESC to bring back the last N records and ASC to bring back the first N records.

The Move_Next and Move_Previous methods

In these two methods we find the following alternatives:

  • move record by record from the first one shown on the grid to the last one
  • produce a pagination when we are at the last or at the first visible record
  • program what to do if we reach the beginning or the end of the file
Taking the MOVENEXT method as example, we can see that if we are not at the end of the file, we can move forward with SKIP. If we reach the end, we simply go back to BOTTOM, to avoid the end of file error. And we use a local property, lFileEnd, to determine at all times whether or not we are at the end of file.

When lFileEnd is TRUE, we jump to the NEXT_PAGE pagination method. If the value of this property remains FALSE, which means that we are navigating the visible part of the grid, we can call the NAVIGATION hook method, which could or could not be necessary in our form, as per the situation we are in. For instance, if every time the cursor is positioned on a record of the grid we want to show data on another grid, using a select method (this is the typical case of a father table that, when its records are being navigated, allows us to show the records of a child table by means of a SQL statement). In this case, the call to the NAVIGATE method would do whatever is necessary to show on the other grid the records of the child table.

In the same manner, the MOVE_PREVIOUS method produces a backwards pagination or navigates the grid in its visible part, if the beginning of file is reached or not (i.e. when the BOF() function returns TRUE)

Next_Page and Previous_Page methods

These methods are the "soul" of the class. They are the ones that bring back the N records necessary to view on the grid, but starting from the last page we viewed.

In these methods we have to contemplate the possibility that, when reaching the end or the beginning of the file, we don't have any more pages to show. In that case we simply "turn back" and begin again from the start.

Let us suppose that the value of the nRowQty property is 20 and we are viewing a page consisting of the last 20 records of the table. If we called the NEXT_PAGE method we would see nothing, because there are no more records. The same happens if we are viewing the records in the first page and want to go back. The solution in these cases is to make a call to the MOVETOP and MOVEBOTTOM methods respectively. That is, if I am at the end, I go back to the top and if I am at the top, "touching the ceiling", I go to the end.

In order to procede to paginate, we need to position the cursor, if we wanted to paginate forward, on the last visible record and make the select command bring back the nRowQty records that are "greater than" the said last visible record. In a similar way, in order to paginate backwards we position the cursor on the first visible row on the grid and bring back the nRowQty records that are "lower than" the said first visible record.

And we then reconfigure the recordsource of the grid, by calling the respective method on the form.

Why use _SCREEN.ACTIVEFORM?

Having reached this point, the readers are probably asking themselves why I use constructions such as the one in the BLANKGRID method.

The thing is we could be navigating the grid from a toolbar, or from a button group on the form. In the latter case it would be enough to use THISFORM, but if we use a toolbar, we necessaryly have to make use of _SCREEN.ACTIVEFORM.

As can be seen in that method and others that use this type of construct, a VFP command line is formed and subsequently it is executed using the EXECSCRIPT function.

This contributes to generalize the class for reuse under different circumstances.

The tcGRID parameter

A grid can be used directly on a form, or on a specific form of a formset or even on a page of a pageframe.

The class should be able to know the location of the grid to be able to reference it in the respective methods.

Let us re-analize the BLANKGRID method

Local cRecordSource

cRecordSource = [_screen.activeform.]+this.cGrid+[.recordsource = ""]

ExecScript(cRecordSource)
The local property cRecordSource ends up being read as shown:
_screen.activeform.Grid1.recordsource = ""
This is so because when we instantiated the class we passed "Grid1" as a parameter. But if the grid had been on page 3 of a pageframe called "Pfr", we would have had to pass the parameter like so:
"Pfr.Page3.Grid1"
so that the resulting sentence would be:
_screen.activeform.Pfr.Page3.Grid1.recordsource = ""

The methods in the form

As I explained above, we have to have some methods in the form to be called by the class.

These methods are:

INIT method in the grid, with the calculation of the quantity of rows to be shown in each page.

thisform.nRowQty = int ( ( this.Height - this.HeaderHeight ) / this.RowHeight )
MOVEBOTTOM method

thisform.oNV.movebottom()

In the same way we shall have the following methods:

MOVENEXT, MOVEPREVIOUS and MOVETOP which will call the respective methods in the class, making reference to the oNV object instantiated in the form.(thisform.oNV.movenext(), thisform.oNV.moveprevious() and thisform.oNV.Movetop() )

The hook method NAVIGATION, called by the class through its NAVIGATION method.

Lastly, two pagination methods, NEXT_PAGE and PREVIOUS_PAGE, which call the respective methods in the class with the following constructions:

thisform.oNV.next_page() and thisform.oNV.previous_page()

The calls to these two methods are placed in the click method of two buttons with icons to indicate backwards pagination or forwards pagination.

Conclusion

I hope this class to be usefull, above all in those cases where minimizing network traffic is essential, to obtain the highest performance.

Rafael Copquin, Estudio Copquin
Public Accountant and Certified Internal Auditor; Fox programmer since 1987, starting with FPD and upgrading to VFP. Vast experience in conversions from FPD to VFP, specialized in business and accounting systems. Member of the Microsoft Users Group of Argentina's Board of Directors as Treasurer.
More articles from this author
Rafael Copquin, April 1, 2006
This class is especially designed to build cursor adapters by simply passing a few parameters, such as table name, cursor name, cursor adapter object name, updatable or not, empty or not, complex or simple select statement.
Rafael Copquin, April 1, 2002
Those of us who have ever programmed FoxPro DOS applications, are marveled and at the same time overwhelmed with the possibilities of enhancement in the appearance, the performance and the different ways of doing the things that were difficult, or outright impossible to do in DOS, th...
Rafael Copquin, June 1, 2002
In the April 2002 issue, I showed the way to use a grid as a picklist, and in order to make the example clear and readily understandable, I did not worry about code reusability. The example simply showed how to program methods in the form containing the picklist grid in order to enable th...
Rafael Copquin, October 1, 2002
Background Early in my development experience, back in the days of Fox Dos, I had to develop a better way to make invoices than the one I had been using. What I had been doing was using the SCROLL statement to make the screen move upwards every time a new item was added. Whenever the user w...
Rafael Copquin, March 1, 2006
In October 2002 the UTMag published the first article on the subject of making invoices with grids. Ever since, I have received numerous emails from readers asking me how to save the invoice thus created in the server tables. This article explains how to use cursor adapters to save the data.
Rafael Copquin, August 1, 2002
Background Many VFP applications deal with accounting data in ways that, in my opinion, are not very efficient, from the program standpoint, or very clear to the user. An example of this is an account statement. Now, an account statement can be any accounting statement that portrays the tran...
Rafael Copquin, June 5, 2013
When Microsoft discontinued development of Visual FoxPro, many would-be writers on VFP issues stopped sending articles to the UniversalThread and as a result, no more articles on VFP were published, with a few exceptions. Over the years, I kept receiving requests from many readers for a continu...
Rafael Copquin, March 1, 2007
Accounting and computer systems should not be tied to any one language in particular. You can design a computer system to be written in Fortran, Pascal, Cobol, VFP or Visual Basic.NET. However, the love of my life as far as computer languages are concerned is Visual FoxPro and that is why the rest o...
Rafael Copquin, September 1, 2006
The title sounds quizzical, doesn’t it? What has the Visual FoxPro menu system in common with XML? Not much. However, it is possible to use XML as a means to generate separate menus for every user. It consists of a series of case statements that would define pads, popups or menu bars, according to t...