| Subject | Re: Populate parent with child value |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Wed, 27 Apr 2016 12:01:34 +0200 |
| Newsgroups | dbase.getting-started |
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. | |