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:
thisform.addproperty('oNV')
(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 )
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:
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)
_screen.activeform.Grid1.recordsource = ""
"Pfr.Page3.Grid1"
_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.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.