REVMEDIA FKB Articles - AREV, OI, REV G, jREV, JCDC, LIVEWATER,ADVANCED REVELATION, OPENINSIGHT, REVELATION G

RevMedia FKB

Document	V4I9A1
Title	Spreadsheet Emulation in ARev Windows
Keywords	SPREADSHEET EMULATION SCRIBE AMV
Text	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: SETUP: * Amend behaviour of left and right arrows WC_WExit_Keys% := @Fm : @Move Keys WC_Wexit_Keys% := @Fm : @Move Keys 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 ) PERPETUAL: Rows = 10 Cols = 6 Begin Case CASE WC_Wc% = @Move Keys OR WC_Wc% = @Move Keys If Mod(WC_Wi% Cols) Else WC_WI_NEXT% = WC_WI% Cols + 1 End Case WC_Wc% = @Move Keys If Mod(WC_Wi% + Cols 1 Cols) Else WC_Wi_Next% = WC_Wi% + Cols 1 End Case WC_Wc% = @Move Keys WC_Wi_Next% = WC_Wi% Cols If WC_Wi_Next% < 1 Then WC_Wi_Next% = WC_Wi% + Cols * (Rows 1) End Case WC_Wc% = @Move Keys WC_Wi_Next% = WC_Wi% + Cols If WC_Wi_Next% > (Cols * Rows) Then WC_Wi_Next% = WC_Wi% (Cols * (Rows 1)) End End Case 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: * UPDATE @RECORD and WC_IS% BEFORE the numbers change Cols = 6 ;* Or whatever CurrentColumn = WC_Si%<4> @Record = WC_Is% WC_Is% = @Record * Now adjust the field numbers for all prompts in the spreadsheet For I = 1 To WC_W_Cnt% WC_W%(I)<4> = WC_W%(I)<4> + Cols Next WC_DISPLAY_ACTION% = 5 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)

[revmedia/copyrigh.htm]

Page last modified: 08/02/03