Subject Re: on open problem
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 13 Jun 2018 19:55:23 +0200
Newsgroups dbase.getting-started
Attachment(s) test_sqlsum.wfm

On 2018-06-13 2:11 AM, Charlie wrote:
> Hi Mervyn... OK I understand this up to a point.  I need a filter in the query.  I know that the word should be 'where'.  But I'm having trouble getting it to work.  What if the filter I want is talname = lname.  Can I put the filter in the taldata2 properties?  Rather than using a variable for a filter is it possible to use a field in another table?  Just curious on this.  Thanks!
 >

There are three different ways of doing this.  The first is to use a
parameter driven query which uses a WHERE clause in the select
statement.  A requery fetches just the required record(s) from the table
when you change the parameter.

The second option is to filter the totals rowset already in memory.
This is less flexible than using the parameter driven query but is a bir
easier to implement.

The third option is to use the rowset's applyLocate() method to fetch
the totals for a specified vendor from the totals table

The attached example uses the third option to pick the totals for a
given vendor from the table of totals.

The first query and grid are just there to show you what you are working
with.


I've used the second query to pick the names out of the data file but
this could just as well be a different file.  You could just as easily
get the names from a different table.

The third grid is just to show you the totals rowset.  In practice you
wouldn't normally show the rowset on the form.  All you're really
interested in is the totals for a given vendor and these can be shown in
entryfields.

Mervyn.




if file('test_sqlsum.dbf')
// drop table test_sqlsum
endif

if not file('test_sqlsum.dbf')
   create table test_sqlsum  (id autoinc,talname character(15),item character(15),;
     qty numeric(10,2),cost numeric(10,2),sell numeric(10,2))


   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Charlie","a",3.00,10.00,15.00)
   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Charlie","b",2.00,2.00,4.00)
   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Baker","a",2.00,10.00,15.00)
   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Abel","a",1.00,10.00,15.00)
   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Delta","b",5.00,2.00,4.00)
   insert into test_sqlsum  (talname,item,qty,cost,sell) values ("Abel","b",7.00,2.00,4.00)
endif

** END HEADER -- do not remove this line
//
// Generated on 2018-06-13
//
parameter bModal
local f
f = new test_sqlsumForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class test_sqlsumForm of FORM
   with (this)
      height = 31.6818
      left = 23.0
      top = 1.5909
      width = 138.2857
      text = ""
   endwith

   this.TEST_SQLSUM2 = new QUERY(this)
   with (this.TEST_SQLSUM2)
      left = 14.0
      width = 10.0
      height = 1.0
      sql = 'select distinct talname from "test_sqlsum.DBF"'
      active = true
   endwith

   this.TEST_SQLSUM3 = new QUERY(this)
   with (this.TEST_SQLSUM3)
      left = 25.0
      width = 10.0
      height = 1.0
      sql = 'select talname,sum(qty) as nCount,sum(qty*cost) as totcost,sum(qty*sell) as totsell from "test_sqlsum.DBF" group by talname'
      active = true
   endwith

   this.TEST_SQLSUM1 = new QUERY(this)
   with (this.TEST_SQLSUM1)
      left = 3.0
      width = 10.0
      height = 1.0
      sql = 'select * from "test_sqlsum.DBF"'
      active = true
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.test_sqlsum1.rowset
      height = 6.0
      left = 4.0
      top = 3.0
      width = 118.0
   endwith

   this.GRID2 = new GRID(this)
   with (this.GRID2)
      dataLink = form.test_sqlsum2.rowset
      height = 5.5
      left = 3.0
      top = 11.5
      width = 27.0
   endwith

   this.GRID3 = new GRID(this)
   with (this.GRID3)
      dataLink = form.test_sqlsum3.rowset
      allowEditing = false
      height = 5.5
      left = 3.0
      top = 19.0
      width = 121.0
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.0909
      left = 12.0
      top = 26.0
      width = 15.2857
      text = "Get totals"
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      dataLink = form.test_sqlsum3.rowset.fields["talname"]
      height = 1.0
      left = 11.0
      top = 28.5
      width = 8.0
   endwith

   this.ENTRYFIELD2 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD2)
      dataLink = form.test_sqlsum3.rowset.fields["ncount"]
      height = 1.0
      left = 40.0
      top = 28.5
      width = 8.0
   endwith

   this.ENTRYFIELD3 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD3)
      dataLink = form.test_sqlsum3.rowset.fields["totcost"]
      height = 1.0
      left = 69.0
      top = 28.5
      width = 8.0
   endwith

   this.ENTRYFIELD4 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD4)
      dataLink = form.test_sqlsum3.rowset.fields["totsell"]
      height = 1.0
      left = 97.0
      top = 28.5
      width = 8.0
   endwith

   this.TEXTLABEL1 = new TEXTLABEL(this)
   with (this.TEXTLABEL1)
      height = 1.0
      left = 5.0
      top = 1.5
      width = 12.0
      text = "Data table"
   endwith

   this.TEXTLABEL2 = new TEXTLABEL(this)
   with (this.TEXTLABEL2)
      height = 1.0
      left = 3.0
      top = 17.5
      width = 14.0
      text = "Summed values"
   endwith

   this.TEXTLABEL3 = new TEXTLABEL(this)
   with (this.TEXTLABEL3)
      height = 1.0
      left = 4.0
      top = 10.0
      width = 12.0
      text = "Names"
   endwith

   this.TEXTLABEL4 = new TEXTLABEL(this)
   with (this.TEXTLABEL4)
      height = 1.0
      left = 39.0
      top = 13.5
      width = 25.0
      text = "Select name"
   endwith

   this.rowset = this.test_sqlsum1.rowset

   function PUSHBUTTON1_onClick()
          form.test_sqlsum3.rowset.applyLocate([talname = ']+form.test_sqlsum2.rowset.fields['talname'].value+['])
//          form.rowset.applyLocate( "CITY = '" + form.cityText.value + "'" )

      return


endclass