including columns not belonging to any table
Hii Sing Chung
I've seen a sample form containing a grid that is linked to a table but I
found that there is one column in the grid that has no corresponding fields
in the table but it is a column that shows a calculation of another 2
columns. The grid is linked to a table called invoice_item, and the column
amount doesn't exist in the table, but it shows multiplication of column
unitprice and quantity. My question is how do I add such a column into the
grid.
There is a txtsubtotal textbox on the form which is linked to the amount
column, so the subtotal amount of all invoice_items for a particular
inoviceno will be displayed in the txtsubtotal textbox. Is there a better
way to do this: to display the subtotal value in the txtsubtotal textbox for
all the invoice_item items (that means all the rows currently displayed in
the grid) without creating the amount column for the grid? The subtotal
should be the sum of all unitprice x quantity.
I am sorry I know too little of Foxpro.
Stefan Wuebbe
"Hii Sing Chung" <sing...@hotmail.com> schrieb im Newsbeitrag
news:E37D6BE3-E0AD-4C97...@microsoft.com...
> Hi,
>
> I've seen a sample form containing a grid that is linked to a table but I found that
> there is one column in the grid that has no corresponding fields in the table but it is
> a column that shows a calculation of another 2 columns. The grid is linked to a table
> called invoice_item, and the column amount doesn't exist in the table, but it shows
> multiplication of column unitprice and quantity. My question is how do I add such a
> column into the grid.
In my opinion, the easiest way is to have calculated columns directly
in the grid.RecordSource, that is use a Local View, a "persistent SQL"
statement like:
Create View vTest As Select *, Cast(field1-field2 As Int) As myColumn From theTable
Views can even be updatable, see help.
However, you can also have a calculating expressions in a
grid.Column.ControlSource ="theAlias.theField1 - theAlias.theField2"
(w/o quotes in the Properties window)
> There is a txtsubtotal textbox on the form which is linked to the amount column, so the
> subtotal amount of all invoice_items for a particular inoviceno will be displayed in the
> txtsubtotal textbox. Is there a better way to do this: to display the subtotal value in
> the txtsubtotal textbox for all the invoice_item items (that means all the rows
> currently displayed in the grid) without creating the amount column for the grid? The
> subtotal should be the sum of all unitprice x quantity.
That sounds as if an alternate way might be a calculated column via
SQL self-join, but the approach you are currently using is probably the
easier/better one, IMO.
hth
-Stefan
Mark
Please have a look at the following code.
***start of code***
CREATE CURSOR Fruits (FruitName C(20), Qty N(3), Price n(3))
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Pear",1,3)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Apple",2,5)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Grapefruit",3,6)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Banana",4,8)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Nut",5,9)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Strawberry",6,12)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Blueberry",7,34)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Raisin",8,8)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Pineapple",9,12)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Peach",10,54)
INSERT INTO Fruits (FruitName, Qty, Price) VALUES ("Abricot",11,66)
LOCATE
oForm = CreateObject("Form1")
oForm.AddObject("grdFruits","Grid1")
With oForm.grdFruits
.Column1.Header1.Caption = "Description"
.Column2.Header1.Caption = "Qty"
.Column3.Header1.Caption = "UnitPrice"
.Column4.Header1.Caption = "Qty * UnitPrice"
EndWith
oForm.AddObject("txtSum","Text1")
oForm.AddObject("lblLabel","Label1")
oForm.Show
Read Events
DEFINE CLASS Form1 As Form
AutoCenter = .T.
Caption = "Fruits"
PROCEDURE DESTROY
Close All
Clear Events
ENDPROC
ENDDEFINE
DEFINE CLASS Grid1 AS GRID
Left = 12
Top = 12
Width = oForm.Width - 24
Visible = .T.
ColumnCount = 4
RecordSource = "Fruits"
RecordSourceType = 2
Column4.ControlSource = "Fruits.Qty * Fruits.Price"
PROCEDURE AfterRowColChange
LPARAMETERS nColIndex
LOCAL ARRAY aSum[1]
GOTO RECNO(This.RecordSource)
Select Sum(Qty * Price) From Fruits Into Array aSum
With oForm.txtSum
.Value = aSum[1]
.Refresh()
EndWith
ENDPROC
ENDDEFINE
DEFINE CLASS Text1 AS TEXTBOX
Left = oForm.Width - This.Width - 12
Top = oForm.grdFruits.Top + oForm.grdFruits.Height + 12
InputMask = "999,999"
Visible = .T.
Value = 0
ENDDEFINE
DEFINE CLASS Label1 AS LABEL
Left = oForm.txtSum.Left - 126
Top = oForm.txtSum.Top
Caption = "Sum of Qty * UnitPrice"
AutoSize = .T.
Visible = .T.
ENDDEFINE
*!* End of Code
hth
Mark
Basoz@discussions.microsoft.com Cetin Basoz
OrdItems in your grid and you want to show Unit_price * Quantity as "Amount":
-Add a column or use an existing one
-Type "Amount" as Header caption
-For column controlsource type (in PEM sheet)
(OrdItems.Unit_price * OrdItems.Quantity)
That is all. It would automatically show the result of calculation and
update the value as you change one of the values on that row.
PS: Beware parentheses around expression is significant. Calculated columns
can be used for non numeric data too (ie: a FullName column combining last,
first).
Cetin
Cetin Basoz
efficency on large data):
Public oForm
oForm = Createobject("myForm")
oForm.Show
Define Class myForm As Form
DataSession = 2
Top = 0
Left = 0
Height = 398
Width = 426
DoCreate = .T.
Caption = "Form1"
Visible = .T.
Name = "Form1"
Add Object txtpid As TextBox With ;
Comment = "", ;
ControlSource = "fatparent.pkid", ;
Height = 23, ;
Left = 108, ;
TabIndex = 4, ;
Top = 12, ;
Width = 81, ;
Name = "txtPid"
Add Object lblpid As Label With ;
AutoSize = .T., ;
BackStyle = 0, ;
Caption = "Pid", ;
Left = 74, ;
Top = 18, ;
TabIndex = 3, ;
Name = "lblPid"
Add Object txtparentname As TextBox With ;
Comment = "", ;
ControlSource = "fatparent.fatname", ;
Height = 23, ;
Left = 106, ;
TabIndex = 8, ;
Top = 41, ;
Width = 132, ;
Name = "txtParentname"
Add Object lblparentname As Label With ;
AutoSize = .T., ;
BackStyle = 0, ;
Caption = "Parentname", ;
Left = 28, ;
Top = 44, ;
TabIndex = 7, ;
Name = "lblParentname"
Add Object txtmiktar As TextBox With ;
Alignment = 3, ;
Value = 0, ;
Height = 23, ;
Left = 275, ;
Top = 336, ;
Width = 137, ;
Name = "txtMiktar"
Add Object txttoplam As TextBox With ;
Alignment = 3, ;
Value = 0, ;
Height = 23, ;
Left = 275, ;
Top = 366, ;
Width = 137, ;
Name = "txtToplam"
Add Object label1 As Label With ;
Caption = "Toplam", ;
Height = 17, ;
Left = 227, ;
Top = 366, ;
Width = 43, ;
Name = "Label1"
Add Object label2 As Label With ;
Caption = "Miktar", ;
Height = 17, ;
Left = 234, ;
Top = 336, ;
Width = 36, ;
Name = "Label2"
Add Object grid1 As Grid With ;
ColumnCount = 4, ;
AllowAddNew = .T., ;
Height = 253, ;
Left = 24, ;
Top = 72, ;
Width = 389, ;
Name = "Grid1"
Add Object commandgroup1 As CommandGroup With ;
AutoSize = .F., ;
ButtonCount = 4, ;
Value = 1, ;
Height = 37, ;
Left = 264, ;
Top = 24, ;
Width = 144, ;
Name = "Commandgroup1", ;
Command1.AutoSize = .F., ;
Command1.Top = 5, ;
Command1.Left = 5, ;
Command1.Height = 27, ;
Command1.Width = 32, ;
Command1.Caption = "<<", ;
Command1.Name = "Command1", ;
Command2.AutoSize = .F., ;
Command2.Top = 5, ;
Command2.Left = 39, ;
Command2.Height = 27, ;
Command2.Width = 32, ;
Command2.Caption = "<", ;
Command2.Name = "Command2", ;
Command3.AutoSize = .F., ;
Command3.Top = 5, ;
Command3.Left = 73, ;
Command3.Height = 27, ;
Command3.Width = 32, ;
Command3.Caption = ">", ;
Command3.Name = "Command3", ;
Command4.AutoSize = .F., ;
Command4.Top = 5, ;
Command4.Left = 107, ;
Command4.Height = 27, ;
Command4.Width = 32, ;
Command4.Caption = ">>", ;
Command4.Name = "Command4"
Procedure parentchanged
* Parent degistiginde normal olarak tableupdate() olacak veya revert
* Boylece select-sql dogru sonuc verecek
Tableupdate(2,.T.,'FatParent')
Tableupdate(2,.T.,'FatChild')
External Array arrToplam
Select Sum(Fiyat*Miktar), Sum(Miktar) ;
from FatChild ;
where ParId = FatParent.PkId ;
into Array arrToplam
Thisform.txttoplam.Value = Iif(_Tally=0,0,arrToplam[1,1])
Thisform.txtmiktar.Value = Iif(_Tally=0,0,arrToplam[1,2])
Endproc
Procedure Load
Set Exclusive Off
Set Multilocks On
Set Deleted On
Rand(-1)
Create Cursor FatParent (PkId i,FatName c(10))
Create Cursor FatChild (ParId i,Aciklama c(20), Fiyat i, Miktar i)
Index On ParId Tag ParId
For ix = 1 To 20
lcParName = 'Test'+Padl(ix,3,'0')
Insert Into FatParent Values (ix,lcParName)
lnChilds = Int(Rand()*10)+1
For jx = 1 To lnChilds
Insert Into FatChild ;
values ;
(ix,lcParName+' - Item'+Padl(jx,2,'0'),;
(Int(Rand()*10)+1)*1000,Int(Rand()*5)+1)
Endfor
Endfor
CursorSetProp('Buffering',5,'FatParent')
CursorSetProp('Buffering',5,'FatChild')
Select FatParent
Go Top
Set Relation To PkId Into FatChild
Endproc
Procedure Init
With Thisform.grid1
.RecordSource = 'FatChild'
.Columns(1).ControlSource = 'FatChild.Aciklama'
With .Columns(2)
.ControlSource = 'FatChild.Fiyat'
.Header1.Caption = "Price"
.AddObject("myTextbox","myTextBox",;
"thisform.txtToplam, %%delta%% * FatChild.Miktar" )
.CurrentControl="myTextbox"
.myTextBox.Visible = .T.
Endwith
With .Columns(3)
.ControlSource = 'FatChild.Miktar'
.Header1.Caption = "Quantity"
.AddObject("myTextbox","myTextBox",;
"thisform.txtToplam, %%delta%% * FatChild.Fiyat"+;
",thisform.txtMiktar, %%delta%%" )
.CurrentControl="myTextbox"
.myTextBox.Visible = .T.
Endwith
.Columns(3).ControlSource = 'FatChild.Miktar'
.Columns(4).ControlSource = '(FatChild.Fiyat*FatChild.Miktar)'
.Columns(4).Header1.Caption = "ExtendedPrice"
Endwith
Thisform.parentchanged()
Endproc
Procedure grid1.AfterRowColChange
Lparameters nColIndex
If Empty(FatChild.ParId)
Replace ParId With FatParent.PkId In 'FatChild'
Endif
Endproc
Procedure commandgroup1.Valid
Select FatParent
Do Case
Case This.Value = 1
Go Top
Case This.Value = 2
Skip -1
If Bof()
Go Top
Endif
Case This.Value = 3
Skip
If Eof()
Go Bottom
Endif
Case This.Value = 4
Go Bottom
Endcase
Thisform.parentchanged()
Thisform.Refresh
Endproc
Enddefine
Define Class myTextBox As TextBox
Calculation = ""
Procedure Init
Lparameters tcCalculationPairs
This.Calculation = m.tcCalculationPairs
Endproc
Procedure GotFocus
This.Tag = Transform(This.Value)
Endproc
Procedure LostFocus
Local lnDelta,ix
Local Array aCalculation[1]
lnDelta = Evaluate(This.ControlSource) - Val(This.Tag)
For ix=1 To Alines(aCalculation,This.Calculation,.T.,',')-1 Step 2
With Evaluate(aCalculation[m.ix])
.Value = .Value + ;
Evaluate( Stuff(aCalculation[m.ix+1],;
Atc('%%delta%%',aCalculation[m.ix+1]),Len('%%delta%%'),'m.lnDelta') )
Endwith
Endfor
Endproc
Enddefine
Cetin Basoz
is, with the changes in VFP data engine _tally wouldn't be 0 when no matching
records.
Comments are in Turkish and here is an explanation on what it is doing:
There are parent (fatParent - like OrderHeader) and child (fatChild - like
OrderDetails) data (created as cursors for the demo in load and filled in
with random data).
The data is tablebuffered.
When 'parent' record changes like in real world applications a tablerevert
or tableupdate is executed. This allows Select SQL to return correct results
(Select SQL returns data from on disk - unless using BUFFERING ooption in
VFP9). And more importantly you need to sum a 'parent's data only once when
parent changes .
The calculation is defined in a custom textbox (myTextBox) that gets the
calculation expression and saves to its tag. In expression %%delta%% is a
place holder for that column's value.
When you edit data (either Unit_price or Quantity in sample) the textbox's
calculation is applied in such a manner that it only adds:
-OldValue + NewValue
to SUM. Just like we slow humans do on inventory books (rather than summing
every time, we just update the page total with adjusting the current page
total with a 'delta' value).
PS: ExtendedPrice is a sample to Calculated column.