Subject Re: Populate parent with child value From Randy Waldman <randy@houstonfurniturebank.org> Date Wed, 27 Apr 2016 07:43:51 -0400 Newsgroups dbase.getting-started Mervyn - wow!  The Lookup() comes from my 30 years of FoxPro.  Thanks for your very detailed help.  Randy Mervyn Bick Wrote: > On 26/04/2016 13:09, Randall Waldman wrote: > > Mervyn - thanks for your reply!  Perhaps the lookup() function would do with a sql  sum() in the main table? > > The LOOKUP() function is XDML and I literally haven't used XDML for > about 15 years for anything other than "quick and dirty" code to set up > tables for examples.  Even then, I can't recall ever using the LOOKUP() > function.  I did, however, have a quick peek in the help file and it > does seem to be the answer to what I think you want to do. > > If you don't want to use the OODML code I posted previously to save the > calculated value to your main table (I still prefer calculating the > value as needed rather than saving it) you can try the following.  The > code combines XDML and localSQL using the tables the OODML example > created.  Delete values in the inv_cost field so that you can see that > the code does actually work. :-) > > An alternative is to do everything in one localSQL statement as the > SELECT command has a "save to..." option.  This does, however, > necessitate using a JOIN but as I don't have the structure of your > tables I can't give you a working example. > > > try >    alter table mbinvoice add Inv_cost numeric(10,2) > catch(exception e) >    //try..catch...endtry to avoid an error if the field exists. > endtry > select 1 > select * from mbinvoice > select 2 > select inv_no,sum(qty*price) as cost from mborderitems group by inv_no > select 1 > scan >    replace mbinvoice->inv_cost with > lookup(SQL_2->cost,mbinvoice->inv_no,SQL_2->inv_no) > endscan > use > select 2 > use > > This code uses localSql to create the rowsets in the workareas instead > of the more usual USE command.  The rest is plain XDML. > > Note that localSQL uses the tablename as the alias in the first workarea > but uses SQL_2 as the alias in the second workarea. > > The code should work without problem on your tables if you simply change > the table and field names.  You should, of course, NEVER try new code > unless you have a backup of the tables safely tucked away. > > Mervyn. > > > >