| Subject | Re: SUM IN GRID |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Mon, 22 Jan 2018 10:43:56 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | masterrowset2.wfm |
On 2018-01-22 9:54 AM, Mustansir Ghor wrote: > Dear All > > I have parent/child rowsets. The child rowset is datalink to a grid. Is there a method to sum a field for the child rowset and store value in a entryfiled to be displayed as total below grid in a form. > > Best Regards > Mustansir > > Yes. :-) It does, however require a bit of SQL coding. I prefer to display the value in the grid in the relevant record. If you specifically want the value shown in an entryfield use the grid's columns property to exclude the field from the grid and datalink the entryfield to the calculated field in the relevant query. A little example is attached. Mervyn. | |
if file('mbcustomers.dbf') drop table mbcustomers endif if not file('mbcustomers.dbf') create table mbcustomers (id autoinc,Name character(15),city character(15)) insert into mbcustomers (Name,city) values ("Abel","Johnnesburg") insert into mbcustomers (Name,city) values ("Baker","Cape Town") insert into mbcustomers (Name,city) values ("Charlie","Bloemfontein") insert into mbcustomers (Name,city) values ("David","Durban") insert into mbcustomers (Name,city) values ("Edward","Pretoria") endif if file('mbinvoice.dbf') drop table mbinvoice endif if not file('mbinvoice.dbf') create table mbinvoice (id autoinc,Inv_no character(8),inv_date date,; cust_id integer) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("104",'01/22/2016',1.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("105",'01/23/2016',1.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("106",'01/23/2016',3.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("107",'01/23/2016',5.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("108",'02/01/2016',3.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("109",'02/01/2016',2.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("110",'02/01/2016',1.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("111",'02/02/2016',2.00) insert into mbinvoice (Inv_no,inv_date,cust_id) values ("112",'02/03/2016',1.00) endif if file('mborderitems.dbf') drop table mborderitems endif if not file('mborderitems.dbf') create table mborderitems (id autoinc,inv_no character(8),item character(15),; qty numeric(10,2),price numeric(10,2)) insert into mborderitems (inv_no,item,qty,price) values ("104","Widget 1",1.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("104","Widget 2",4.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("104","Widget 3",2.00,112.00) insert into mborderitems (inv_no,item,qty,price) values ("105","Widget 4",1.00,22.00) insert into mborderitems (inv_no,item,qty,price) values ("105","Widget 1",4.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("106","Widget 2",2.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("107","Widget 4",3.00,22.00) insert into mborderitems (inv_no,item,qty,price) values ("107","Widget 1",3.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("108","Widget 1",1.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("108","Widget 3",6.00,112.00) insert into mborderitems (inv_no,item,qty,price) values ("108","Widget 2",2.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("108","Widget 4",7.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("109","Widget 1",20.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("110","Widget 2",2.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("111","Widget 2",7.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("111","Widget 4",2.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("112","Widget 1",1.00,12.34) insert into mborderitems (inv_no,item,qty,price) values ("112","Widget 2",4.00,32.22) insert into mborderitems (inv_no,item,qty,price) values ("112","Widget 3",2.00,112.00) insert into mborderitems (inv_no,item,qty,price) values ("112","Widget 4",1.00,22.00) endif /* The designer streams out sql string as one long line which can make it hard to follow. Copies here for easy reference mbinvoice1 sql = "select i.cust_id,i.inv_date,o.inv_no,sum(o.price * o.qty) as Cost from mborderitems o " sql +="inner join mbinvoice i " sql +="on o.inv_no = i.Inv_no and i.cust_id = :id " sql +="group by i.cust_id,i.inv_date,o.inv_no " sql +="order by i.inv_no" Note that normally cust_id = :id would be in a WHERE clause to link to the masterSource rowset. In this case it is in the ON clause as there is a Join in the SQL statement. mbcustomers1 sql = 'select c.id,c.Name,c.city,Sum(o.price * o.qty) As Total ' sql +='from mborderitems o ' sql +='full outer join mbinvoice i On o.inv_no = i.Inv_no ' sql +='full outer join mbcustomers c On i.cust_id = c.id ' sql +='group By c.id,c.Name,c.city ' */ ** END HEADER -- do not remove this line // // Generated on 2017-07-04 // parameter bModal local f f = new masterrowset2Form() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class masterrowset2Form of FORM with (this) height = 35.1364 left = -2.5714 top = 1.4545 width = 146.4286 text = "" endwith this.MBCUSTOMERS1 = new QUERY(this) with (this.MBCUSTOMERS1) left = 63.0 width = 10.0 height = 1.0 sql = "select c.id,c.Name,c.city,Sum(o.price * o.qty) As Total from mborderitems o full outer join mbinvoice i On o.inv_no = i.Inv_no full outer join mbcustomers c On i.cust_id = c.id group By c.id,c.Name,c.city " active = true endwith this.MBINVOICE1 = new QUERY(this) with (this.MBINVOICE1) left = 76.0 width = 8.0 height = 1.0 sql = "select i.cust_id,i.inv_date,o.inv_no,sum(o.price * o.qty) as Cost from mborderitems o inner join mbinvoice i on o.inv_no = i.Inv_no and i.cust_id = :id group by i.cust_id,i.inv_date,o.inv_no order by o.inv_no" params["id"] = "" masterSource = form.mbcustomers1.rowset active = true endwith this.MBORDERITEMS1 = new QUERY(this) with (this.MBORDERITEMS1) left = 86.0 width = 11.0 height = 1.0 sql = "select o.*,qty*price as Cost from mborderitems o where inv_no = :inv_no" params["inv_no"] = "" masterSource = form.mbinvoice1.rowset active = true endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.mbcustomers1.rowset height = 7.0 left = 7.0 top = 3.0 width = 93.0 endwith this.GRID2 = new GRID(this) with (this.GRID2) dataLink = form.mbinvoice1.rowset height = 6.0 left = 7.0 top = 14.5 width = 84.0 endwith this.GRID3 = new GRID(this) with (this.GRID3) dataLink = form.mborderitems1.rowset height = 7.5 left = 7.0 top = 24.0 width = 133.0 endwith this.TEXTLABEL1 = new TEXTLABEL(this) with (this.TEXTLABEL1) height = 1.0 left = 7.0 top = 22.0 width = 58.0 text = "Items for selected Invoice" endwith this.TEXTLABEL2 = new TEXTLABEL(this) with (this.TEXTLABEL2) height = 1.0 left = 7.0 top = 12.0 width = 75.0 text = "Invoices for selected Customer. Click on Invoice to see its items." endwith this.TEXTLABEL3 = new TEXTLABEL(this) with (this.TEXTLABEL3) height = 1.0 left = 7.0 top = 1.0 width = 69.0 text = "Select Customer to see the invoices for the customer" endwith this.rowset = this.mbcustomers1.rowset endclass | |