| Subject | Re: Populate parent with child value |
| From | Randall Waldman <randwald@comcast.net> |
| Date | Tue, 26 Apr 2016 07:09:23 -0400 |
| Newsgroups | dbase.getting-started |
Mervyn - thanks for your reply! Perhaps the lookup() function would do with a sql sum() in the main table? Thanks, Randy Mervyn Bick Wrote: > 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 > > | |