Stephan Grieger and Mark Trescowthick - GUI Computing
Perhaps the defining new control of the "GUI" look and feel is the grid. Almost every user, and developer, would agree that grids are one of the most useful interface elements available. They give the developer the ability to show large amounts of data on the screen and (mostly) provide the user with the ability to modify the data in situ. Which, for Toolbook developers, is frustrating - if this is such a handy control, why doesn't Toolbook provide us with one? Toolbook has a fairly extensive and complete tool kit, and the absence of a grid is really its only drawback.
Now that Toolbook supports VBXs, and especially given our recent articles here on bringing Formula One to Toolbook, why not use a custom control? The answer is that whilst Toolbook supports custom controls none of them actually print, except as black blobs. "Do you really want to print?" is the answer we got out of Asymetrix in the States when we posed this problem to them. Really not adequate, but not atypical of "VBX Support" in many products.
In any event, we needed a grid (boy, did we need a grid!) so the only answer was to craft one in Toolbook.
This was no small undertaking, and we're by no means finished just yet, so you should consider this article a sort of 'interim report'. We think we have all of the issues covered now, but we're still refining some areas and the next step is to make what we now have into a true reusable Toolbook object. What you see here is by no means the final product, so judge us not harshly.
This article presents an overview of the nature of the approach, including source for a simple grid as well as examining a couple of the more interesting / useful functions we've developed along the way. The code for the basic grid is contained in a demonstration book and accompanying system book, that for some of the smaller features in individual demo books - though it can also be found in the basic grid book (sorta).
A Basic Grid
Our first approach to a grid was to place as many EditBoxes on the screen as there were cells in the grid. However, as all Toolbook developers are aware, there is a 64K limitation on the pages. As our grid may be as big as an A4 page, and as we might actually want to enter data into the grid, we quickly discovered that this was not a viable solution.
The next approach (which worked) was to make each column an EditBox and set BaseLines to True. This gives the effect of a grid with the horizontal and vertical lines. The fact that BaseLines do not print out (at least, they don't according to Asymetrix, but tell that to our HP4M printer <g>) didn't perturb us as in our application this was not desirable anyhow.
The first interface issue to be solved was how to move the mouse pointer to the next column on Tab, Arrow or Enter. It was difficult for the user to know where the cursor was on larger grids, if all they had to look for was a small flashing cursor. What we really needed was to have some sort of input box which moved with the cursor and showed you exactly where you were entering data. In a sort of 'back to the future' approach (remember VB2?), it was time for another EditBox. We simply had to discover where we were in a column then lay an EditBox over that position.
So here's an overview of how the whole thing works.
First, we draw up each of the columns. Each column has some very specific Properties that need to be set in order for this approach to work correctly. First, the grid must be given a name. Let's say for example that the grid will be called 'AVDF'. Each column would then need to be named 'AVDFColX', where X is the column number starting from one. The reason for this naming convention was that there may, in fact, be more than one grid on the page and the editor was to be shared between grids. This then enabled us to move between columns and rows, etc., using the name of the grid as the identifier.
Next, we set the Activated property of all columns to True. It was undesirable for the user to be able to click on a column and for the mouse cursor to appear inside the edit field as we wanted to place the EditBox over the selected row and have the user type within that.
Each Column then had a few other User defined properties such as Col (the column Number), EditAllowed (either True or False depending on whether you could type into this column) and MaxChrs (the maximum number of Characters allowed in this cell).
There are several other User Properties that we can set to provide specialised functionality but we'll get into those a little later on.
We then group all the columns and give the group the name agreed - in this case 'AVDF'. The group also needs to have a few User Properties set to enable the code to work:
With the grid now set up, the Editor needs to be defined. As each grid on the page could share the same editor, we need, again, to define a few properties for it to work:
Now all we need to do is to add the System Book required to drive the whole thing and away we go.
The above describes a very limited grid capable of performing only the basics. With this in mind, we can add several properties to both the columns and the grid group which will enhance the grid into something which is actually useful to you.
In our application, we had columns which performed calculations which would then update other columns. We also had default values for new rows added, data validation and DropDown ComboBoxes.
To facilitate all these special requirements, we decided to add a few more user properties which told the application what to do when data was entered into the cell.
Capitalising Text: We defined a Caps property of a column which, when set would capitalise the text when the EditBox was moved off the cell.
Default Values: Setting DefaultValue to anything other then Nil, would place the contents of the property into the cell whenever a new line was added. New lines are added when the user presses <Enter> at the end of a line.
Line validation: When data must be present before a new line can be added, you can specify which fields need to be filled before a new line can be made. In our sample, we have specified that both columns 1 and 2 need to have data in them before a new line can be created. Thus, setting the NewRowRequiredFields property of the grid group to 'AVDFCol1,AVDFCol2', prevents the addition of a new line before the specified two have been filled.
Data Types: Some cells require a numeric value to be entered. Setting the DataType property to either Int, Numeric, % or Text, limits user entry to only those types in that field.
Calculations: In our example, we have placed a calculation on columns 7 and 8 with the result appearing in column 6. Column 6 is also non editable.
In the CellCalulation property of both column 7 and 8, we placed
TextLine (Row of Field GridEditor) of Text Of Field AVDFCol7 + TextLine (Row of Field GridEditor) of Text of Field AVDFCol8
This is the exact line of code that will be sent to an Evaluate() expression. The calculation must be a line that the Evaluate expression can evaluate (we'll add error checking later - trust us). All calculations are done this way.
Next we set RequiredFields, again in columns 7 and 8, to 'AVDFCol7,AVDFCol8' which informs the program that there must be a value in both column 7 and 8 before the calculation can be performed.
The last thing we need is to set, in columns 7 and 8, the ResultCol property to 'AVDFCol6' so that the application knows where to place the result of the evaluation.
Be aware that these calculations are not restricted to cells in the grid. We could have said in the CellCalculation property,
(Text of Field TaxRate / 100) * TextLine (Row of Field GridEditor) of Text of Field AVDFCol6.
Some columns, when they change, are required to perform calculations which can not be expressed in such a way that the Evaluate command can process them. In this case, we can place a comma separated list of functions that are to be called when the contents of this column change. Setting the 'Special' property to say 'CalcTax,CalcNetIncome' will call the two functions whenever the contents of that column change.
These special functions may take some time to execute. Because this interferes with the speed of moving between cells in the grid, we decided to place all the special cases into a stack. Functions get added onto the end of the stack and are called whenever there is some idle time. You can adjust the idle time in the code yourself by adding a counter.
Function Keys: Say for example, that when you are over column 1, and the user presses the F4 key, that you want a drop down list to appear. If you place '115,DropDown' into the 'SpecialKeys' property, the DropDown function will be called whenever the user presses Key 115 or F4 whilst over that column.
Adding an <Enter> followed by a second series of Key,Command parameters, will force the code to check for that instance of the key as well. You can add as many lines as you wish, but the more you add the slower data entry will be.
Limiting a column to a list: The LimitToList property disallows the user to enter data into the field and only allows the cell to be modified by some other means such as a drop down list.
Scrolling: Setting the GridExpandable property of the group to False will only allow the user to enter as many rows as there are visible on the screen. Setting it to True however, will allow the user to enter lines past the number of screen rows. The grid should then automatically scroll down as new lines are added.
Getting that automatic scrolling caused some pain for a while. There seemed to be just the odd special case or two which served to confuse.
The scrolling field included in the example book sets a field to the number of lines hidden at each 'end' whenever the field is scrolled via the scrollbars, mouse click on last (partially visible) line or via an arrow key. We actually catch keyUp for all those arrow keys, etc. This is wrongly, or at least unclearly, stated in the docco as not working. It does. Only keys which navigate from a field don't generate keyUp, it would seem. Which accidental discovery made our life a deal easier...
Given we then had an 'offset' into the text, we should be able to easily determine which actual row was in what row position. That gave us scrolling, and the key to placing our editor precisely (and filling it with the right data - did we mention the grid had to be pseudo data-aware?).
The basic function underlying all that looks like this:
to handle doOffsets ufl = textunderflow of target ofl = textoverflow of target if ufl <> 0 then get characters 1 to ufl of text of target lns = textlinecount(it) else lns = 0 end if set text of field infield to lns if ofl <> 0 then totl = charcount(text of target) get characters (totl - ofl) to totl of text of target lns = textlinecount(it) else lns = 0 end if set text of field outfield to lns end
Getting the RowHeight
Easy, we thought. Once we had a row position, all we needed was the height of the rows to enable us to position the EditBox exactly where we wanted it. All we needed to do was to determine the height of the rows (i.e. the space between the baselines), then multiply by the row which we were on and Bingo!
We wrote a small function to do the job:
to get RowHeight fntsize - set font size set fontsize of field tstfld to fntsize - make field zero height set item 4 of bounds of field tstfld to item 2 of bounds of field tstfld - lock screen set syslockscreen to true - increment height until no text overflow do increment item 4 of bounds of field tstfld until textoverflow of field tstfld = 0 - unlock screen set syslockscreen to false - return the height return (item 4 of bounds of field tstfld - item 2 of bounds of field tstfld) end
This simply sets the font size (we were always using Arial, or we would have added a font option) then, using a hidden field tucked away somewhere, increases its size from zero until there's no textOverflow. When that happens, it's the row height. Easy!
In fact, 'Bingo!' turned into 'Boing!' when we used this piece of wonderful logic. By about row 8, we were nearly half a row out in our placement. By row 20, things were completely and utterly out of whack. Desk checking the code brought no obvious answer. We first thought it might have been the pixel or two spacing that Toolbook places under the baseline, but that seemed wrong.
Finally, we nailed it. The field we'd been using had a rectangular border. Remove it, and our height calculation was spot on. We only do this once on startup, so though it's a bit slow, it doesn't get run that often.
Getting a Unique Name
Even though everything was now in place (or thereabouts) we realised that we were going to need to move some objects to the background if we wanted to stay under the dreaded 64k limit on a couple of particularly complex pages.
This was moderately late in the piece, and amongst the objects we wanted to move the grid editor - which, unfortunately but not unsurprisingly, was referenced in many of our common routines.
But we didn't want to move all the grids and their editor into the background on all pages, and we certainly didn't want to create two sets of routines. We could, I suppose, have edited our common routines and branched here there and everywhere depending on the background / foreground position of the editor. That, too, sounded bad as (huge surprise, this) time was of the essence.
The actual approach, when it came, was as usual quite simple. We simply created a small function to determine whether a given field was in the background or foreground, and which returned the uniqueName of the field. We then modified our code (ever so slightly) to use the uniqueName.
The function looks like this:
to get EditorUnique fld - stop error messages set syssuspend to FALSE - clear any old syserror clear syserror - first, try the page get uniquename of field fld of this page - if syserror isn't null, then it wasn't there if syserror <> "" then - now try the background get uniquename of field fld of this background end if - save it set EditorUnique to it - turn on error trapping clear syserror set syssuspend to TRUE - return the name (or null if it wasn't found at all) return EditorUnique end
In fact, not only is the mainline code more solid, but it's actually smaller and faster as well, as this routine only needs be run once, and we have a uniqueName for one of our most commonly used objects.
A good deal of the base code for the initial versions of the grid was coded by Ki Yun Lin. The testing prowess, determination and good nature of Stuart Penny was essential.
Examples for Download