The first obvious solution would be to have all the textboxes with the totals in a container in a container (yes, two levels of containers), where the inner container would be as wide as the total width of all of the grid's columns, and the outer one as wide as the visible columns are. That would require some neat positioning – the .left of the outer container would have to be equal to grid.left plus the width of the record mark and deletion mark (if any), and then the .left of the inner container would have to be the negative of the first visible textbox, i.e. negative total width of the invisible columns... altogether, too complicated. And the textboxes still need to be created, maintained and positioned relative to the column widths.
The other solution seemed to be much more simple: have another grid just below the first, with no scrollbars, no headers, with just one row which would show the totals, and which would scroll horizontally with the first grid. It should also reflect true totals of the first grid at all times, and be easy to implement. This should relieve the programmer of having to write any code that calculates totals, puts them into variables or form's properties, then refreshes a dozen textboxes. The textboxes would also vanish – so no more cumbersome repositioning each time column widths are edited.
So I wrote that – took me about three hours to get the initial version right. First I had to decide on vcx vs prg – and I decided to make it a prg, because it will be based on Custom class, which doesn't have a visual representation anyway. This class builds the total grid, and drives it, but doesn't show anywhere.
Build it
The first task of this object is to find the first grid. Nothing too complicated here – the .findgrid() method just looks for the first object with baseclass=”Grid” in its .parent, and sets its .o1Grid property to it. If it can't find one, its .init returns .f. and the object simply vanishes – no error, but no result either.
Then it builds the second grid from the first. I first wanted the second grid to be of the same class as the first, but it didn't work with some grid classes which mention columns in their inits – this grid is supposed to start with no columns at all. Another problem would be if the first grid's class is defined with any nonzero number of columns, for the same reason. So it uses a simple, base class grid, and builds it column by column.
First, the recordsource of the new grid. It will be a cursor with almost the same structure as the recordsource of the first grid, the only difference being the replacement of any memo, general, blob and logical fields with character fields. There will be another cursor (aliases of both these cursors are properties of this class, so it will close them in its .destroy method) which will be used to refresh the contents of the grid. That's what Marcia and Andy call “soft select”.
Build the columns
Next step is to build the columns.To do this, there's a .ObjClone(oOriginalObj, oClonedObj) method, which recursively copies any non default property from the first grid's column, to the same property of the second grid's column. So any .inputmask, dynamic* and other properties are copied, which takes care of the alignment, formatting and other cosmetic issues. This method is maybe doing a few unnecessary things, because I stole it from my column swapper builder – for instance, it's checking for any additional objects in the target column and trying to remove them, which won't happen with a blank default column we just added.
Once the column is cloned, few properties are copied from the original grid just in case they were at their default values (and so not caught by .ObjClone method): width, inputmask, alignment. Controlsource is set to the corresponding field in the cursor.
While building the columns, it also builds a script. The script will refresh the contents of the total grid, by doing a select from first grid's .recordsource into the second cursor. Any numeric fields are selected as sum(), dates as max() and other fields as a single space. This select creates one record, which is Scatter Name -d, and Gather Name -d into the first cursor. The second cursor is then closed.
At the end of building of a column, two of column events are bound to the .ColMoved() method: moved() and resize(). Since aEvents() retrieves only the object reference, and generally the grid's column doesn't know its own index, a property nIndex is added to the top grid's column.When this delegate code is triggered, this property is retrieved and used to find the corresponding column in the lower grid. The lower grid's column's .width and .columnorder are then set to be equal to the top one's.
Put it in its place and bind it
After all the columns are built, the total grid is positioned and sized: its .top is two pixels below the first grid, and its .left, .deletemark and .recordmark are copied over. HeaderHeight is set to zero, .height to .rowheight (i.e. it is a single row grid), it's readonly, no scrollbars, no tabstop.‚The width of the total grid is special – it is set to the upper grid's width, minus the width of the vertical scrollbar, which is retrieved from sysmetric(5), if the upper grid has this scrollbar.
Finally, two BindEvent()s happen: the first grid's .AfterRowColChange() is bound to .Refresh2Grd() method, and .Scrolled() event is bound to .Scroll2Grd() method. The Refresh2Grd() runs the script first, and refreshes the total grid. It then compares the .leftColumn property between the grids, and scrolls the lower grid horizontally to whichever direction it needs to. The end result is that the columns are aligned. In the first versions of the code, this sometimes got into an endless loop, because scrolling the lower grid to the right didn't change its .LeftColumn property. How could this happen? Simply, there was enough room to display the last column as it were. You can't scroll a VFP grid more to the right if there are no invisible or partially invisible columns to the right. And it so happened that the last column was partly covered with the vertical scrollbar in the upper grid, but as the lower grid had no scrollbars, it was fully visible there and it wouldn't scroll, but rather got into an endless loop while trying.
The Scroll2Grd() just echoes the scroll of the upper grid, if it was a horizontal scroll. Any such scroll of the upper grid will make the lower grid scroll the same.
Make it run and think about tweaks
Finally, we need to add this grid to the form. It takes only one line in upper grid's parent's .init:
this.NewObject("oTotal","totalgrid","dcTotalgrid.prg")
The only tweak we may need here is to somehow handle the case when there are multiple grids with the same parent, and we want each of them to have its own total, or want a specific one to have a total. This could be done by passing the reference to such a grid as a parameter, and calling This.findgrid() only when absent.
Another possible extension would be to have different aggregate functions in the script – for example, in a grid where we have quantities and prices, sum(price) doesn't make sense, but sum(price*qty) does. Also, if we want some columns totaled and some not, we may want to have the latter skipped; we may want a count(*) in some column etc. This can be achieved by capturing the script in the debugger, and then editing it and adding something like this after the .newobject() line:
*-- TEXT BLOCK BEGIN text to this.oTotal.cScript noshow textmerge Select [ ] as sifra; , COUNT(*) as naziv1; , max(datum_nab) as datum_nab; , [ ] as mtroska; , sum(kolicina) as kolicina; , sum(nabavna) as nabavna; , sum(otpis) as otpis; , sum(sadvr) as sadvr; from OSNSRED into cursor <> select <> Local loRec Scatter name loRec loRec.naziv1="Count "+TRANSFORM(loRec.naziv1) select <> gather name loRec use in <> endtext *-- TEXT BLOCK END
What good is this?
I only wish I wrote this much earlier. My usual data entry form, and all of view forms, had a grid where at least two columns needed totals. This meant writing some code to calculate those totals, creating fields in some cursor to hold those totals, putting textboxes on the form to show those totals, rearranging them each time grid was changed, making sure the recalculation code was called when needed - a lot of error-prone work. Had I had this class back then, I'd have replaced a few dozen lines of code (and PEM tweaks to the textboxes) with a single line in form's .init() and all my forms would have a consistent look.
Source code
