Maketotals() not only calculates totals but also averages, standard deviaion, variance, max, min, count, etc. Usage
We are aware of the usage during creation of XBrowse:
- Code: Select all Expand view RUN
oBrw:lFooter := .t.
oCol:nFooterType := AGGR_SUM // or AGGR_AVG, _STD, COUNT, etc. Ref: xbrowse.ch
oBrw:MakeTotals()
nFooterType can be switched to different types during runtime and different results can be viewed without having to recompute the aggregates.
Advantage of using oBrw:MakeTotals() is that (i) it simplifies the program code and (ii) when needed it does complex computations like STD but it does not offer any performance gains. This method literally calculates the aggregates by scanning the data from the beginning till end. For this reason, while it is appropriate to call this method initially once, calling this method during runtime frequently may hit the performance.
But we need to update the aggregates when the values of a record are modified during runtime. When the value of a cell is changed with inline edit, xbrowse recomputes the aggregates using the difference between the original and modified values and refreshes the footer without having the call MakeTotals() again. This recalculation feature highly improves the performance.
Issues:
However this recalculation feaure is used by xbrowse only when the value of a cell is modified with inline edit and the recalculation is limited only to the column modified.
(1) It is possible the modifying the value of one column might affect the values in the other columns too. In such cases the programmer had to explicitly call MakeTotals({othercolumns}) for such other columns.
(2) When (a) record is edited outside xbrowse in a dialog, (b) record is appended or (c) deleted, recalculation feature is not available and the programmer needs to explicity call oBrw:MakeTotals().
In both the above cases, there is hit on the performance. Extent of performance deterioation depends on the size of the data and speed of data access. So for obvious reasons, when we make changes to data, we would like the aggregates to be re-calculated without having to completely re-total the columns.
Improvements in FWH 16.12:
Issue (1) above is addressed. When the value of a column is changed by inline edit, aggregates of all columns which are automatially changed are also recomputed and footers updated, without having to call MakeTotals() for other affected columns. Since this is only recalculation applying the differences and therefore extremely fast. The application program need not make any explicit calls to recompute and update footers.
Improvements in FWH 17.01:
Issue (2) above is addressed. There can be two cases.
Case-1: Programs using the recommended built-in methods to edit / append / delete records, i.e,
oBrw:EditSource( .t. ) for appending new records
oBrw:EditSource() for Edit
oBrw:Delete() for deletion of records.
When these methods are used to add/edit/delete operations, the incremental recalculation of totals is done automatically, without any extra code.
Note: Whether we show totals are not, we recommend using EditSource(.t./.f.) method for add/edit of data and oBrw:Delete() for deletion of data.
(Sample provided towards the end)
Case-2: Programs not using the recommended methods but using its own way of saving changes and deletions. In this case, please follow these steps:
Edit (modifying existing record)
- Code: Select all Expand view RUN
oBrw:SaveTotals()
< save edited data>
oBrw:RecalcTotals()
Appending new record
- Code: Select all Expand view RUN
oBrw:SaveTotals( .t. ) // .t. for Blank
< save new record >
oBrw:ReCalcTotals()
Deletion of Record
- Code: Select all Expand view RUN
oBrw:SaveTotals()
< delete record >
oBrw:RecalcTotals( .t. ) // .t. for deletion
This way in case of all changes of data only incremental recalculation is adopted, avoiding time consuming MakeTotals()
The following sample program illustrates
- Recommended way to code xbrowse with add/edit/delete features using datarow
- Creating fully portable code. Demonstrates how the same code for browse and edits works without any change for dbf and ado (for any datasource)
- incidentally also demonstrates automatic incremental recalculation of totals using datarow for edits/appends
- Code: Select all Expand view RUN
- #include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
local uSource
RDDSETDEFAULT( "DBFCDX" )
SET DELETED ON
SetGetColorFocus()
uSource := OpenTable()
Browse1( uSource )
Browse2( uSource )
CloseTable( uSource )
return nil
//----------------------------------------------------------------------------//
function Browse1( uSource )
local oDlg, oBar, oFont, oBrw
local bRowCalc
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 400,500 PIXEL TRUEPIXEL FONT oFont
DEFINE BUTTONBAR oBar OF oDlg SIZE 80,32 2015
@ 50,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
DATASOURCE uSource AUTOCOLS ;
COLSIZES 40,80,60,60,100 ;
CELL LINES NOBORDER FOOTERS FASTEDIT
bRowCalc := { || oBrw:Amount:Value := oBrw:Qty:Value * oBrw:Rate:Value }
WITH OBJECT oBrw
AEval( :aCols, { |o| o:nEditType := EDIT_GET }, 2, 3 )
WITH OBJECT :Qty
:nFooterType := AGGR_SUM
:bOnChange := bRowCalc
END
WITH OBJECT :Rate
:bOnChange := bRowCalc
:bFooter := { || oBrw:Amount:nTotal / oBrw:Qty:nTotal } // Weighted average
END
:Amount:nFooterType := AGGR_SUM
:Item:cFooter := "TOTALS"
//
:lHScroll := .f.
:bEdit := { |oRec| EditDlg( oRec ) }
:MakeTotals()
:CreateFromCode()
END
DEFINE BUTTON OF oBar PROMPT "Add" CENTER ACTION oBrw:EditSource( .t. )
DEFINE BUTTON OF oBar PROMPT "Edit" CENTER ACTION oBrw:EditSource()
DEFINE BUTTON OF oBar PROMPT "Delete" CENTER ACTION oBrw:Delete()
DEFINE BUTTON OF oBar PROMPT "Close" CENTER ACTION oDlg:End()
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
return nil
//----------------------------------------------------------------------------//
function Browse2( uSource )
local oDlg, oBar, oFont, oBrw, oRec, oGet
local bRowCalc
local lEdit := .f.
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 640,300 PIXEL TRUEPIXEL FONT oFont
@ 50,20 XBROWSE oBrw SIZE 360,-20 PIXEL OF oDlg ;
DATASOURCE uSource AUTOCOLS ;
COLSIZES 40,80,60,60,100 ;
CELL LINES NOBORDER FOOTERS FASTEDIT
bRowCalc := { || oBrw:Amount:Value := oBrw:Qty:Value * oBrw:Rate:Value }
WITH OBJECT oBrw
AEval( :aCols, { |o| o:nEditType := EDIT_GET }, 2, 3 )
WITH OBJECT :Qty
:nFooterType := AGGR_SUM
:bOnChange := bRowCalc
END
WITH OBJECT :Rate
:bOnChange := bRowCalc
:bFooter := { || oBrw:Amount:nTotal / oBrw:Qty:nTotal } // Weighted average
END
:Amount:nFooterType := AGGR_SUM
:Item:cFooter := "TOTALS"
//
:lHScroll := .f.
:MakeTotals()
oRec := :DataRow( nil, nil, .t. )
:bChange := { || oRec:Load(), lEdit := .f., oDlg:AEvalWhen(), oDlg:Update() }
//
:CreateFromCode()
END
@ 80,420 SAY "Item : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 110,420 SAY "Quantity : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 140,420 SAY "Rate : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 170,420 SAY "Amount : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 80,500 GET oGet VAR oRec:Item SIZE 100,20 PIXEL OF oDlg UPDATE ;
WHEN lEdit VALID ( oDlg:Update(), .t. )
@ 110,500 GET oRec:Qty SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT ;
WHEN lEdit VALID If( oRec:Qty > 0, ( oRec:Amount := oRec:Qty * oRec:Rate, oDlg:Update(), .t. ), .f. )
@ 140,500 GET oRec:Rate SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT ;
WHEN lEdit VALID If( oRec:Rate > 0, ( oRec:Amount := oRec:Qty * oRec:Rate, oDlg:Update(), .t. ), .f. )
@ 170,500 GET oRec:Amount SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT READONLY
@ 240,400 BTNBMP PROMPT "Save" SIZE 90,30 PIXEL OF oDlg FLAT ;
WHEN oRec:Modified() ;
ACTION ( oRec:Save(), lEdit := .f., oDlg:AEvalWhen(), oDlg:Update(), ;
oBrw:SetFocus() )
@ 240,530 BTNBMP PROMPT "Cancel" SIZE 90,30 PIXEL OF oDlg FLAT ;
WHEN lEdit ;
ACTION ( oRec:Undo(), lEdit := .f., oRec:Load(), oDlg:AEvalWhen(), oDlg:Update(), ;
oBrw:SetFocus() )
@ 10, 20 BTNBMP PROMPT "|<" SIZE 60,30 PIXEL OF oDlg FLAT ;
WHEN oBrw:KeyNo > 1 ACTION ( oBrw:GoTop(), oBrw:SetFocus() )
@ 10,090 BTNBMP PROMPT "<" SIZE 60,30 PIXEL OF oDlg FLAT ;
WHEN oBrw:KeyNo > 1 ACTION ( oBrw:GoUp(), oBrw:SetFocus() )
@ 10,160 BTNBMP PROMPT ">" SIZE 60,30 PIXEL OF oDlg FLAT ;
WHEN oBrw:KeyNo < oBrw:nLen ACTION ( oBrw:GoDown(), oBrw:SetFocus() )
@ 10,230 BTNBMP PROMPT ">|" SIZE 60,30 PIXEL OF oDlg FLAT ;
WHEN oBrw:KeyNo < oBrw:nLen ACTION ( oBrw:GoBottom(), oBrw:SetFocus() )
@ 10,300 BTNBMP PROMPT "Delete" SIZE 60,30 PIXEL OF oDlg FLAT ;
WHEN oBrw:nLen > 0 .and. !lEdit ;
ACTION ( oBrw:Delete(), Eval( oBrw:bChange ), oDlg:AEvalWhen(), oDlg:Update(), oBrw:SetFocus() )
@ 10,400 BTNBMP PROMPT "Add" SIZE 90,30 PIXEL OF oDlg FLAT ;
WHEN !lEdit ;
ACTION ( oRec:Load( .t. ), oDlg:Update(), lEdit := .t., oDlg:AEvalWhen(), oGet:SetFocus() )
@ 10,530 BTNBMP PROMPT "Edit" SIZE 90,30 PIXEL OF oDlg FLAT ;
WHEN !lEdit ;
ACTION ( lEdit := .t., oDlg:AEvalWhen(), oGet:SetFocus() )
oDlg:bPainted := { || oDlg:Box( 50, 400, 210, 620 ) }
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
return nil
//----------------------------------------------------------------------------//
static function EditDlg( oRec )
local lNew := ( oRec:RecNo == 0 )
local oDlg, oFont
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 400,190 PIXEL TRUEPIXEL FONT oFont ;
TITLE If( lNew, "APPEND", "EDIT" )
@ 40,40 SAY "Item : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 70,40 SAY "Quantity : " SIZE 60,20 PIXEL OF oDlg RIGHT
@ 100,40 SAY "Rate : " SIZE 60, 20 PIXEL OF oDlg RIGHT
@ 130,40 SAY "Amount : " SIZE 60, 20 PIXEL OF oDlg RIGHT
@ 40,140 GET oRec:Item SIZE 100,20 PIXEL OF oDlg UPDATE ;
VALID ( oDlg:Update(), .t. )
@ 70,140 GET oRec:Qty SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT ;
VALID If( oRec:Qty > 0, ( oRec:Amount := oRec:Qty * oRec:Rate, oDlg:Update(), .t. ), .f. )
@ 100,140 GET oRec:Rate SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT ;
VALID If( oRec:Rate > 0, ( oRec:Amount := oRec:Qty * oRec:Rate, oDlg:Update(), .t. ), .f. )
@ 130,140 GET oRec:Amount SIZE 100,20 PIXEL OF oDlg UPDATE RIGHT READONLY
oDlg:bPainted := { || oDlg:Box( 20, 20, 170, 260 ) }
@ 20,280 BTNBMP PROMPT "UNDO" SIZE 100,40 PIXEL OF oDlg FLAT UPDATE ;
WHEN oRec:Modified() ACTION ( oRec:Undo(), oDlg:Update() )
@ 75,280 BTNBMP PROMPT "SAVE" SIZE 100,40 PIXEL OF oDlg FLAT UPDATE ;
WHEN oRec:Modified() ACTION ( oRec:Save(), oDlg:Update() )
@ 130,280 BTNBMP PROMPT { || If( oRec:Modified(), "CANDEL", "CLOSE" ) } ;
SIZE 100,40 PIXEL OF oDlg FLAT UPDATE ;
ACTION ( oRec:Undo(), oDlg:End() )
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
return nil
//----------------------------------------------------------------------------//
static function OpenTable()
local oCn, oRs
local uSource, nChoice := 0
local aCols := { ;
{ "ID", "+", 4, 0 }, ;
{ "ITEM", "C", 10, 0 }, ;
{ "QTY", "N", 3, 0 }, ;
{ "RATE", "N", 2, 0 }, ;
{ "AMOUNT", "N", 5, 0 } }
local aTran := { ;
{ "ITM-1", 100, 20, 2000 }, ;
{ "ITM-2", 120, 30, 3600 }, ;
{ "ITM-3", 50, 40, 2000 } }
nChoice := Alert( "Choose Database", { "DBF", "ACCESS" } )
nChoice := Max( 1, nChoice )
if nChoice == 1
if ! File( "TESTSUM.DBF" )
DBCREATE( "TESTSUM.DBF", aCols, "DBFCDX", .T., "TST" )
FW_ArrayToDBF( aTran, "ITEM,QTY,RATE,AMOUNT" )
CLOSE TST
endif
USE TESTSUM NEW ALIAS TST SHARED VIA "DBFCDX"
SET FILTER TO !DELETED()
GO TOP
uSource := "TST"
else
if ! File( "testsum.mdb" )
FW_CreateMDB( "testsum.mdb" )
endif
oCn := FW_OpenAdoConnection( "testsum.mdb" )
if ! FW_AdoTableExists( "testsum", oCn )
FWAdoCreateTable( "testsum", aCols, oCn )
endif
oRs := FW_OpenRecordSet( oCn, "testsum" )
if oRs:RecordCount() == 0
AEval( aTran, { |a| oRs:AddNew( { "ITEM", "QTY", "RATE", "AMOUNT" }, a ) } )
endif
oRs:MoveFirst()
uSource := oRs
endif
return uSource
//----------------------------------------------------------------------------//
static function CloseTable( uSource )
if ValType( uSource ) == 'C'
( uSource )->( DBCLOSEAREA() )
elseif ValType( uSource ) == 'O'
uSource:ActiveConnection:Close()
endif
return nil
//----------------------------------------------------------------------------//
