Subject Re: Grid for invoice line items detail and totals - A lot of code / work
From Carlos A. Pereira [APKomp] <casap@apkomp.pt>
Date Mon, 17 Apr 2017 16:19:37 +0100
Newsgroups dbase.getting-started
Attachment(s) Unnamed File 1wlEmoticon-thinkingsmile[1].png

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.

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 Pensativo
 
 
"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.