Subject Re: Populate parent with child value From Mervyn Bick <invalid@invalid.invalid> Date Tue, 26 Apr 2016 10:38:57 +0200 Newsgroups dbase.getting-started Attachment(s) masterrowset2.wfm On 26/04/2016 05:23, Randy Waldman wrote: > Hi. - 10.3 Plus user. I am updating a sku table from an inventory table use the relation feature built into the form.   I want populate a field in the parent table with a field in the child table.  How do I do this and where do I put the code?  My purposes is to simply  "sum the order" (qty * price). > Thanks, Randy > > ps: does anyone know of a simple, open source point of sale system in dBase? > If you simply need the value calculated from fields in a child table displayed as a virtual field in its parent table it can be done easily enough by JOINing the two tables. The attached example calculates the cost of each entry in the mborderitems table.  This caost is not saved to the table. The cost for each invoice is calculated by INNER JOINing a calculated value from the mborderitems table to the mbinvoice table. To calculate the totals for each customer both the invoice and order items are joined to the customer able using FULL OUTER JOINs.  These are needed to ensure that customers with no invoices are shown in the list.   If you only wanted customers with invoices you would use INNER JOINS. If you want the calculated value stored in the parent table it requires a bit more work.  It can't be done with joined tables as the join makes the rowset read-only. It can be done using tables where the relationship has been set but it means selecting each parent and looping through the child records to create a total.  To my mind this results in rather messy code.  Probably the easiest way to do this is to use a separate program.  (That's the Bick way. Now we wait for someone to post the Better way. :-) ) alter table mbinvoice add Inv_cost numeric(10,2) q = new query() q.sql = 'select inv_no,inv_cost from mbinvoice' q.active = true q1 = new query() q1.sql = 'select inv_no,sum(qty*price) as cost from mborderitems group by inv_no' q1.active = true do while not q.rowset.endofset     q1.rowset.applyLocate( "inv_no = '" + q.rowset.fields['inv_no'].value + "'" )     q.rowset.fields['inv_cost'].value = q1.rowset.fields['cost'].value     q.rowset.save()     q.rowset.next() enddo q.active = false q1.active = false 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 "                                   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 2016/03/26 // 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 = 26.5714       top = -0.2727       width = 141.8571       text = ""    endwith    this.MBCUSTOMERS1 = new QUERY(this)    with (this.MBCUSTOMERS1)       left = 63.0       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 '       active = true    endwith    this.MBINVOICE1 = new QUERY(this)    with (this.MBINVOICE1)       left = 76.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 "       params["id"] = ""       masterSource = form.mbcustomers1.rowset       active = true    endwith    this.MBORDERITEMS1 = new QUERY(this)    with (this.MBORDERITEMS1)       left = 86.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