Subject Re: sql code not working From Charlie <tm@tc.com> Date Fri, 15 Jun 2018 14:43:56 -0400 Newsgroups dbase.getting-started OK thanks.  I will try again.  Does taldata2.rowset have to be indexed.  I tried to index this using the property indexname and there was no index offered.  taldata1.rowset is indexed property.   Mervyn Bick Wrote: > On 2018-06-15 4:42 PM, Charlie wrote: > > Hi Mervyn... OK I am still struggling with this but do understand more. > > > > I used the filter only because the sql statement didn't seem to work. > > > >   sql = 'select talname,sum(qty) as cnt, sum(qty*cost) as tcost, > > sum(qty*sell) as tsell from taldata group by talname' > > > > But possibly you didn't know exactly what I was doing.  And I didn't understamd tje group by name is actually a filter of it's own (I think) > > > > Talname is actually a variable so I changed that in the sql statement to lname which should work!!! > > > > I am not using this in the grid but instead to three entryfields which I linked to  the  appropriate fields in form.taldata2. > > > > I must be doing something wrong as I get blank data when I have this set up in each entryfield. > > > > At that point before I understood the 'group by' I assumed I needed a filter. > > > > Your first suggestion seems to be the easiest but I don't know what is wrong and why I'm not seeing the data in each entryfield. > > > > Hope this makes sense.  Please let me know if there is a resolve! > > If lname is the field name in the taldata table then > >    sql = 'select lname,sum(qty) as cnt, sum(qty*cost) as tcost, > sum(qty*sell) as tsell from taldata group by lname' > > That will create a rowset that has a single record for each name in the > lname field.  Each record has the fields lname, cnt, tcost and tsell > with the appropriate total values in the fields cnt, tcost and tsell > fields for each vendor.  It is a standard rowset with nothing special > about it and you treat it just like any other rowset. > > If you datalink entryfields to each field you will see the data for the > selected record.  You can either use oRowset.applyLocate() to move the > row pointer to the record you want or you can set the rowset's filter > > > form.taldata1.rowset.applyLocate([lname = ']+talname+[']) > > or > > form.taldata1.rowset.filter = [lname = ']+talname+['] > > > Whenever fieldnames and aggregating functions such as count() or sum() > are used together in a select statement the group by clause must be used > and it must include all the selected fieldnames in the list.  This is a > SQL requirement. > > > Mervyn.