Normally we need to create a derived class from TDatabase, if we want to add some calculated columns or rename columns. This new feature allows us to quickly do this without actually creating a derired class.
With MapCol() method we can create new alias names for the dbf fields and use the new names in our program instead of the original fieldnames, AddCol() method allows us to add new columns in the memory calculated on the fields of the dbf and use the newly created columns as if they are in the dbf. We can achieve the same results by subclassing TDatabase, but these two methods enable us to do this without subclassing.
As an example, I created a DBF with fieldnames 'FLD1', 'FLD2', etc. In the TDatabase object I mapped these columns to more meaningful names like 'InvoiceNo', 'InvoiceDt', etc and use the new names in the program as if they are the dbf field names:
- Code: Select all Expand view
oDbf:MapCol( { { 'FLD1', 'InvoiceDt' }, ;
{ 'FLD2', 'InvoiceNo' }, ;
{ 'FLD3', 'Quantity' }, ;
{ 'FLD4', 'Rate' } } )
Now I added three calculated columns with the names Amount (Read/Write), Tax (ReadOnly) and WithTax (ReadOnly), which we can use in the program as if they are in the DBF. Chaning Quantity or Rate changes Amount, Tax and WithTax. Changing Amount changes Rate, Amount, Tax and WithTax.
We can use oDbf:Quantity, oDbf:Rate instead of oDbf:Fld3, oDbf:Rate in our program.
Code for creating the calculated columns:
- Code: Select all Expand view
oDbf:AddCol( 'Amount', ;
{ |Self,x| If( x != nil, ::Rate := Round( x / ::Quantity, 2), nil ), ::Quantity * ::Rate }, ;
'N', 12, 2 )
oDbf:AddCol( 'Tax', { |Self| Round( ::Amount * 0.14, 2 ) }, 'N', 11, 2 )
oDbf:AddCol( 'WithTax',{ |Self| ::Amount + ::Tax }, 'N', 12, 2 )
Now we can use oDbf:Amount, oDbf:Tax, etc in our programs as if they are fields.
As against this structure in DBF
my TDatabase object has these columns for me to use in the program.
Sample Code
- Code: Select all Expand view
- [code]#include "FiveWin.Ch"
#include "ord.ch"
#include "xbrowse.ch"
REQUEST DBFCDX
//------------------------------------------------------------------//
function Main()
local oDbf
local oDlg, oBrw, oFont
CreateSampleDBF()
oDbf := TDataBase():Open( , 'SALE' )
oDbf:MapCol( { { 'FLD1', 'InvoiceDt' }, ;
{ 'FLD2', 'InvoiceNo' }, ;
{ 'FLD3', 'Quantity' }, ;
{ 'FLD4', 'Rate' } } )
oDbf:AddCol( 'Amount', ;
{ |Self,x| If( x != nil, ::Rate := Round( x / ::Quantity, 2), nil ), ::Quantity * ::Rate }, ;
'N', 12, 2 )
oDbf:AddCol( 'Tax', { |Self| Round( ::Amount * 0.14, 2 ) }, 'N', 11, 2 )
oDbf:AddCol( 'WithTax',{ |Self| ::Amount + ::Tax }, 'N', 12, 2 )
DEFINE FONT oFont NAME 'TAHOMA' SIZE 0,-12
DEFINE DIALOG oDlg SIZE 800,300 PIXEL FONT oFont ;
TITLE 'TDataBase Calculated Columns'
@ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
OBJECT oDbf AUTOCOLS AUTOSORT FASTEDIT ;
CELL LINES NOBORDER FOOTERS
AEval( oBrw:aCols, { |o| o:nEditType := EDIT_GET }, 3, 3 )
AEval( oBrw:aCols, { |o| o:lTotal := .t., ;
o:nTotal := 0, ;
o:bOnChange := { || oDbf:Save(), ;
oBrw:MakeTotals(), ;
oBrw:RefreshFooters() } ;
}, 3 )
WITH OBJECT oBrw:Rate
:lTotal := .f.
:nTotal := nil
:bFooter := { || oBrw:Amount:nTotal / oBrw:Quantity:nTotal }
END
WITH OBJECT oBrw
:nStretchCol := 2
:MakeTotals()
:CreateFromCode()
END
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
oDbf:Close()
return ( 0 )
//------------------------------------------------------------------//
init procedure PrgInit
SET DATE ITALIAN
SET CENTURY ON
SET EPOCH TO YEAR(DATE())-50
SET TIME FORMAT TO 'HH:MM:SS'
SET DELETED ON
SET EXCLUSIVE OFF
RDDSetDefault( 'DBFCDX' )
SET OPTIMIZE ON
SetGetColorFocus()
XBrNumFormat( 'A', .t. )
return
//------------------------------------------------------------------//
static function CreateSampleDBF()
field FLD1,FLD2,FLD3,FLD4
local n
local aCols := { ;
{ 'FLD1', 'D', 8, 0 }, ;
{ 'FLD2', 'N', 4, 0 }, ;
{ 'FLD3', 'N', 10, 0 }, ;
{ 'FLD4', 'N', 10, 2 } }
DBCreate( 'SALE', aCols )
USE SALE EXCLUSIVE
for n := 1 to 20
APPEND BLANK
SALE->FLD1 := Date() - HB_RandomInt( 1, 30 )
SALE->FLD2 := 2345 + n
SALE->FLD3 := HB_RandomInt( 1, 1000 )
SALE->FLD4 := HB_Random( 1.0, 999.0 )
next n
INDEX ON FLD1 TAG ONE
INDEX ON FLD2 TAG TWO
INDEX ON FLD3 TAG THREE
INDEX ON FLD4 TAG FOUR
CLOSE SALE
return nil
//------------------------------------------------------------------//
[/code]
If we ask the question whether these features are a substitute to subclssing, the answer is 'no'. The calcuated columns created with this feature apply only to the particular instance, where as the calculated columns created in a subclass apply to all the instances of that subclass. Once we know this difference, we know what to do when.
