In order for this site to work correctly, and for us to improve the site, we need to store a small file (called a cookie) on your computer.
By continuing to use this website, you agree to our cookies and privacy policy.
  
Home page Home page Home page Home page
Pixel
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel
Pixel

Spreadsheet Emulation in ARev Windows

When we mentioned in the recent Window Common series that WC_WC% (the exit key from SCRIBE) could be used to good effect in spreadsheet emulation, regular subscribers would immediately have deduced that this subject had already reared its ugly head in Sprezzatura's day to day consultancy work (a surprisingly large number of our articles start off life that way.) In fact, this problem has been with us almost since the dawn of ARev, when one of the very few features of Revelation G which was immediately missed was the ability to define a multivalued column as both horizontal and vertical simultaneously, effectively creating a small table. This is no different in appearance from an associated multivalued group, but we all know how nasty and slow these can be to navigate around, especially when they are large.

We've mentioned before that AMV groups have their flaws in terms of database design anyway, but even among those of a strictly relational bent they are still often used as a simple collection device for a more normalised structure which is hidden from the user (this was the subject of an interesting talk by Maurice Champagne and David Wolff at the recent conference in New Orleans.) To be effective in any circumstances, however, the entry area should be easily navigable, and it should be able to cope quickly and efficiently with large arrays of data, not a currently recognised ARev feature!

One solution is to hardcode the entire spreadsheet interface yourself in R/Basic. (I've seen it done, but it's very time-consuming.) It is usually preferable to take advantage of the consistent interface, numerous processing hooks, and standard features of ARev windows. This allows the same sort of interface to be created much more quickly, and more flexibly as well.

Obviously, no single solution will fool all of the people all of the time, so we now present a series of techniques which can be adapted to your own particular circumstances. The techniques (if not the syntax) should be equally valid in all versions of ARev, although the availability of the mouse in version 3.x can help out greatly with the navigation problem. In the code segments which follow, it is assumed that Window_Common% & Edit.Keys have been made available when required. Furthermore, in these examples it is assumed that the entry area is located in a collector window which has no columns present other than those in the spreadsheet grid. It is a simple matter to adjust the code for windows which are bound to a table or which contain other prompts as well.

Recognising the problems inherent in multivalued groups, a good starting point is simply to do away with them altogether, mimicking their appearance by creating a grid of single valued prompts. This makes navigation by mouse much more straightforward; simply click on any visible cell (even in V3, the mouse is still limited to horizontal movement through AMV groups). It is a good idea to adjust the normal function of certain keystrokes as well. I prefer to use the left and right arrows exclusively for navigation between cells (consistent with the behaviour of the up and down keys - see later), rather than for movement within any given prompt. This can be achieved by means of a setup commuter call which adjusts the exit keys recognised by SCRIBE, as follows:


0001    SETUP:
0002      * Amend behaviour of left and right arrows.
0003      WC_WExit_Keys% := @Fm : @Move.Keys<Right$>
0004      WC_Wexit_Keys% := @Fm : @Move.Keys<Left$>
0005    Return

To edit existing values, use F3 to zoom an individual cell where the original left/right functionality will be unchanged. If you prefer to use other keystrokes (e.g. Tab/BackTab) the setup can be a little more complicated and the following perpetual process will also need to be adjusted accordingly.

The decision about which prompt to move to next is usually made from a perpetual processing call, leaving post prompt processing free for specific situations. The easiest way to work out which way the user is trying to move is to check the keystroke used to exit SCRIBE, WC_Wc%. A sample of such a perpetual commuter call follows, based on a window of ten rows of six columns each (which is usually enough a fill a single page window in V3.)


0001    PERPETUAL:
0002      Rows = 10
0003      Cols = 6
0004      Begin Case
0005       Case WC_Wc% = @Move.Keys<Right$> OR WC_Wc% =  @Move.Keys<Enter$>
0006        If Mod(WC_Wi%,Cols) Else
0007         WC_Wi_Next% = WC_Wi% - Cols + 1
0008        End
0009       Case WC_Wc% = @Move.Keys<Left$>
0010        If Mod(WC_Wi% + Cols - 1, Cols) Else
0011         WC_Wi_Next% = WC_Wi% + Cols - 1
0012        End
0013       Case WC_Wc% = @Move.Keys<Up$>
0014        WC_Wi_Next% = WC_Wi% - Cols
0015        If WC_Wi_Next% < 1 Then
0016         WC_Wi_Next% = WC_Wi% + Cols * (Rows - 1)
0017        End
0018       Case WC_Wc% = @Move.Keys<Down$>
0019        WC_Wi_Next% = WC_Wi% + Cols
0020        If WC_Wi_Next% > (Cols * Rows) Then
0021         WC_Wi_Next% = WC_Wi% - (Cols * (Rows - 1))
0022        End
0023      End Case
0024    Return

In a similar fashion, cases can be established to detect the PgUp, PgDn, Home and End keys, and to reposition the cursor according to your own requirements. (If you have used multivalued as opposed to single columns, then you will need to set WC_Wi_Next% AND WC_Mv_Next% on every operation you intercept.)

The previous example covers the situation where the maximum number of rows required can all be seen together at once in one page. When the user presses the Down key on the bottom line the cursor simply returns to the top of the current column. For reasons of speed, and ease of maintenance, it is usually desirable not to exceed one physical page. This means that we must find some efficient technique to pan the data when it is NOT all visible at once. Rather than trying to adjust the data, a little lateral thinking leads us to a much more efficient conclusion - adjust the field numbers of the prompts themselves, but leave WC_Wi_Next% unchanged, so that the window thinks it is displaying a different set of prompts and redisplays the data by itself. The case block for the operation of the down arrow might now become:


0001    * Update @Record and WC_Is% BEFORE the numbers change.
0002    Cols = 6 ;* Or whatever...
0003    CurrentColumn = WC_Si%<4>
0004    @Record<CurrentColumn> = WC_Is%
0005    WC_Is% = @Record<CurrentColumn+Cols>
0006  
0007    * Now adjust the field numbers for all prompts in the spreadsheet.
0008    For I = 1 To WC_W_Cnt%
0009      WC_W%(I)<4> = WC_W%(I)<4> + Cols
0010    Next
0011    WC_Display_Action% = 5
0012    WC_Wi_Next% = WC_Wi%

Similar logic would also apply for panning upwards, and also for panning from left to right, although in this latter case, it is necessary to anticipate the maximum number of columns permissible and ensure that the field numbers of the prompts as painted into the initial window leave a sufficiently large gap between rows! E.g. to cater for a maximum of 10 columns with only six visible at once, the original field numbering would be :

 1    2    3    4    5    6  ³   7    8    9   10
11   12   13   14   15   16  ³  17   18   19   20
21   22   23   24   25   26  ³  27   28   29   30
     Actual Window           ³    Unused Numbers

To pan by one column to the right, each field number is simply incremented by one, and decremented by one again for a simple pan to the left.

Pans of whole pages of information at a time are easily available, by extension of the above code segments. The prompt altering technique can also be used to change any other features of the prompts (often this is essential in a horizontal pan to maintain options, edit patterns etc.). Changing the background colour of alternate columns to emphasise the columnar structure, and moving these colours with the pan can be a very impressive visual feature.

The final task involved in the spreadsheet emulation is one which will be specific to your individual applications, namely the initial parsing of the data from disk into the spreadsheet, and its subsequent manipulation back in to its native format when the spreadsheet is saved. Some applications will obviously be faster at this task than others (that's one of the few good things about non-normalised multivalued groups), but bear in mind that this is a one-off overhead which should NOT affect your normal database design methodology - whatever you do, don't say I recommended AMV groups! It may even be feasible on occasion to store each cell permanently as an individual field in @Record for maximum speed, but reporting requirements do not usually permit this.

(Volume 4, Issue 9, Pages 4-7)
Pixel
Pixel Footer R1 C1 Pixel
Pixel
Pixel
Pixel