Thanks Ken Mayer
Thanks Mervyn Bick
Mervyn Bick seems to be more standard/best solution.
But
A more simpler solution it seems to be necessary,
like using in Value property of a total field/object, a expression like in
others app:
....Value ="SUM <col/field>...."
and nothing else
"Mervyn Bick" escreveu na mensagem
news:SjllQyvsSHA.2028@ip-AC1E04A7...
On 2017-04-10 11:01 PM, Ken Mayer wrote:
> See code below. Save to a form (CustomerOrders.wfm). Note the
custom
> field for "LineTotal" ... navigate through some of the customers
and
> notice that the line items table updates the calculated field
correctly.
> The trick is that you have to add the calculated field in something
like
> the rowset's onOpen event handler (or in a data module in the same
way),
> then save the form (if you do it in the form designer), and exit
the
> designer (the custom field doesn't yet exist, until you re-open the
form
> in the designer ...). Then if you go to add the column to the grid,
it
> will be in the list of fields ...
To take this a step further it is not too much extra work to show the
the total value of each order in the grid showing the orders for each
customer. A little bit more work and one can show the total value of
orders placed for each customer.
I've used masterSource instead of masterRowset/masterFields. As the
calculated fields are created in the queries' SQL properties the form
designer sees them straight away. It is, however, important for the
constructor code for a query used as a masterSource to be before the
constructor code for a "child" query in the source code.
****** Start of example code *******
/*
The designer streams out sql string as one
long line which can
make it hard to follow.
Copies here for easy reference
orders1
sql = "select
o.orderid,o.customerid,o.orderdate,o.shipdate,oi.orderid,sum(oi.price *
oi.quantity) as Cost from orderitems oi "
sql +="inner join orders o on
o.orderid = oi.orderid and
o.customerid = :customerid "
sql +="group by
o.orderid,o.customerid,o.orderdate,o.shipdate,oi.orderid order by
o.orderid"
customers1
sql = 'select
c.customerid,c.company,c.lastname,c.phone,c.zip,Sum(oi.price *
oi.quantity) As c."Total Orders" '
sql +='from orderitems oi '
sql +='full outer join orders o On
oi.orderid = o.orderid '
sql +='full outer join customers c On
o.customerid = c.customerid '
sql +='group By
c.customerid,c.company,c.lastname,c.phone,c.zip
order by c.company '
*/
** END HEADER -- do not remove this line
//
// Generated on 2017-04-11
//
parameter bModal
local f
f = new customerOrders1Form()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif
class customerOrders1Form of FORM
with (this)
metric = 6 // Pixels
height = 612.0
left = 179.0
top = 177.0
width = 892.0
text = "Customer Orders"
endwith
this.DBASESAMPLES1 = new DATABASE(this)
with (this.DBASESAMPLES1)
top = 1.0
width = 78.0
height = 37.0
databaseName = "DBASESAMPLES"
active = true
endwith
this.CUSTOMERS1 = new QUERY(this)
with (this.CUSTOMERS1)
left = 10.0
top = 1.0
width = 63.0
height = 37.0
database = form.dbasesamples1
sql = 'select
c.customerid,c.company,c.lastname,c.phone,c.zip,Sum(oi.price *
oi.quantity) As c."Total Orders" '
sql +='from orderitems oi '
sql +='full outer join orders o On
oi.orderid = o.orderid '
sql +='full outer join customers c On
o.customerid = c.customerid '
sql +='group By
c.customerid,c.company,c.lastname,c.phone,c.zip
order by c.company '
active = true
endwith
this.ORDERS1 = new QUERY(this)
with (this.ORDERS1)
left = 24.0
top = 1.0
width = 45.0
height = 37.0
database = form.dbasesamples1
sql = "select
o.orderid,o.customerid,o.orderdate,o.shipdate,oi.orderid,sum(oi.price *
oi.quantity) as Cost from orderitems oi "
sql +="inner join orders o on
o.orderid = oi.orderid and
o.customerid = :customerid "
sql +="group by
o.orderid,o.customerid,o.orderdate,o.shipdate,oi.orderid order by
o.orderid"
params["customerid"] = 1
masterSource =
form.customers1.rowset
active = true
endwith
this.ORDERITEMS1 = new QUERY(this)
with (this.ORDERITEMS1)
left = 35.0
top = 1.0
width = 66.0
height = 37.0
database = form.dbasesamples1
sql = "select oi.*,quantity*price
linecost from ORDERITEMS oi
where orderID = :orderID"
params["orderID"] = 1
masterSource =
form.orders1.rowset
active = true
endwith
with (this.ORDERITEMS1.rowset)
with (fields["PartID"])
lookupSQL = "select
partid,description from parts"
endwith
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink =
form.customers1.rowset
columns["Column1"] = new
GRIDCOLUMN(form.GRID1)
with (columns["Column1"])
dataLink =
form.customers1.rowset.fields["company"]
editorType = 1 //
EntryField
width = 199.0
endwith
columns["Column2"] = new
GRIDCOLUMN(form.GRID1)
with (columns["Column2"])
dataLink =
form.customers1.rowset.fields["lastname"]
editorType = 1 //
EntryField
width = 150.0
endwith
columns["Column3"] = new
GRIDCOLUMN(form.GRID1)
with (columns["Column3"])
dataLink =
form.customers1.rowset.fields["phone"]
editorType = 1 //
EntryField
width = 141.0
endwith
columns["Column4"] = new
GRIDCOLUMN(form.GRID1)
with (columns["Column4"])
dataLink =
form.customers1.rowset.fields["zip"]
editorType = 1 //
EntryField
width = 100.0
endwith
columns["Column5"] = new
GRIDCOLUMN(form.GRID1)
with (columns["Column5"])
dataLink =
form.customers1.rowset.fields["total orders"]
editorType = 3 //
SpinBox
width = 220.0
endwith
with
(columns["Column1"].headingControl)
value =
"company"
endwith
with
(columns["Column2"].headingControl)
value =
"lastname"
endwith
with
(columns["Column3"].headingControl)
value =
"phone"
endwith
with
(columns["Column4"].headingControl)
value = "zip"
endwith
with
(columns["Column5"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["Column5"].headingControl)
value = "Total
Orders"
endwith
cellHeight = 22.0
height = 137.0
left = 12.0
top = 33.0
width = 856.0
endwith
this.GRID2 = new GRID(this)
with (this.GRID2)
dataLink = form.orders1.rowset
columns["COLUMN1"] = new
GRIDCOLUMN(form.GRID2)
with (columns["COLUMN1"])
dataLink =
form.orders1.rowset.fields["orderid"]
editorType = 3 //
SpinBox
width = 130.0
endwith
columns["COLUMN2"] = new
GRIDCOLUMN(form.GRID2)
with (columns["COLUMN2"])
dataLink =
form.orders1.rowset.fields["orderdate"]
editorType = 3 //
SpinBox
width = 120.0
endwith
columns["COLUMN3"] = new
GRIDCOLUMN(form.GRID2)
with (columns["COLUMN3"])
dataLink =
form.orders1.rowset.fields["shipdate"]
editorType = 3 //
SpinBox
width = 120.0
endwith
columns["COLUMN4"] = new
GRIDCOLUMN(form.GRID2)
with (columns["COLUMN4"])
dataLink =
form.orders1.rowset.fields["cost"]
editorType = 3 //
SpinBox
width = 220.0
endwith
with
(columns["COLUMN1"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["COLUMN1"].headingControl)
value =
"orderid"
endwith
with
(columns["COLUMN2"].editorControl)
rangeMax =
{2000-12-06}
rangeMin =
{2000-08-28}
endwith
with
(columns["COLUMN2"].headingControl)
value =
"orderdate"
endwith
with
(columns["COLUMN3"].editorControl)
rangeMax =
{2000-12-06}
rangeMin =
{2000-08-28}
endwith
with
(columns["COLUMN3"].headingControl)
value =
"shipdate"
endwith
with
(columns["COLUMN4"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["COLUMN4"].headingControl)
value = "Cost"
endwith
cellHeight = 22.0
height = 187.0
left = 63.0
top = 198.0
width = 644.0
endwith
this.GRID3 = new GRID(this)
with (this.GRID3)
dataLink =
form.orderitems1.rowset
columns["COLUMN1"] = new
GRIDCOLUMN(form.GRID3)
with (columns["COLUMN1"])
dataLink =
form.orderitems1.rowset.fields["itemno"]
editorType = 3 //
SpinBox
width = 60.0
endwith
columns["COLUMN2"] = new
GRIDCOLUMN(form.GRID3)
with (columns["COLUMN2"])
dataLink =
form.orderitems1.rowset.fields["partid"]
editorType = 1 //
EntryField
width = 130.0
endwith
columns["COLUMN3"] = new
GRIDCOLUMN(form.GRID3)
with (columns["COLUMN3"])
dataLink =
form.orderitems1.rowset.fields["quantity"]
editorType = 3 //
SpinBox
width = 80.0
endwith
columns["COLUMN4"] = new
GRIDCOLUMN(form.GRID3)
with (columns["COLUMN4"])
dataLink =
form.orderitems1.rowset.fields["price"]
editorType = 3 //
SpinBox
width = 120.0
endwith
columns["COLUMN5"] = new
GRIDCOLUMN(form.GRID3)
with (columns["COLUMN5"])
dataLink =
form.orderitems1.rowset.fields["linecost"]
editorType = 1 //
EntryField
width = 200.0
endwith
with
(columns["COLUMN1"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["COLUMN1"].headingControl)
value =
"ItemNo"
endwith
with
(columns["COLUMN2"].headingControl)
value =
"PartID"
endwith
with
(columns["COLUMN3"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["COLUMN3"].headingControl)
value =
"Quantity"
endwith
with
(columns["COLUMN4"].editorControl)
rangeMax = 100
rangeMin = 1
endwith
with
(columns["COLUMN4"].headingControl)
value =
"Price"
endwith
with
(columns["COLUMN5"].headingControl)
value =
"linecost"
endwith
cellHeight = 22.0
height = 176.0
left = 105.0
top = 418.0
width = 644.0
endwith
this.rowset = this.customers1.rowset
endclass
******* End of example code *******
Mervyn.