Subject Re: lookupsql and Combobox ; where is the missing link
From Mervyn Bick <invalid@invalid.invalid>
Date Sat, 30 May 2020 11:03:23 +0200
Newsgroups dbase.getting-started
Attachment(s) test_lookupsql.wfm

On 29/05/2020 22:26, Dirk wrote:
> Hello,
 >
> i changes some *dbf files, using lookupsql
 >
>   as far is all ok, append ad edit all good
 >
> but im struggling on filter in a form by country
 >
>
> by datalink i haven the dat in the combobox
 >
> when i do the following
> a=this.parent.rowset.fields["country"].lookupRowset.fields[1].value
 >
> i get the value a = B of Belgium
 >
> changing the field to  2  i get Belgium
 >
>
> now when i try   form.rowset.filter=[country=']+"A"+[']
 >
> i get filtered by Austria
 >
> when i try to use the varaible a i get a warning
 >
> of course when i change teh value of the country, whatever  belgium or
> germany ... i get of course the the value of variable A
 >
> my question is when i make a choice of a country in the combobox,
 > how it's filtering by chosen country
When you append or edit a field which has it's lookupsql property set
you use the "looked up" value.  In other words you type in Belgium and
dBASE will do the reverse lookup and save B in the field.

To set the filter you need to use the actual value saved in the table
i.e B for Belgium.  The combobox shows the "looked up" value so you need
to use the lookupsqlrowset's applyLocate() method to find the equivalent
code value in the lookuprowset.  In the attached example I've used lur
(which in my mind stands for look up rowset :-) )  to make the rest of
the code in the combobox's onChange event handler less cumbersome.

Mervyn.







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

if not file('mb_country.dbf')
   create table mb_country  (code character(2),country character(20))

   insert into mb_country  (code,country) values ("A","Argentina")
   insert into mb_country  (code,country) values ("B1","Belgium")
   insert into mb_country  (code,country) values ("B2","Brazil")
   insert into mb_country  (code,country) values ("C","Canada")
endif
if file('mb_data.dbf')
// drop table mb_data
endif

if not file('mb_data.dbf')
   create table mb_data  (id autoinc,data character(15),c_code character(2))

   insert into mb_data  (data,c_code) values ("sffdggfdfg","B1")
   insert into mb_data  (data,c_code) values ("hhhhlh","C")
   insert into mb_data  (data,c_code) values ("jhgghgj","A")
   insert into mb_data  (data,c_code) values ("kjhjjkjkk","B2")
   insert into mb_data  (data,c_code) values ("sffdggfdfg","B1")
   insert into mb_data  (data,c_code) values ("hhhhlh","C")
   insert into mb_data  (data,c_code) values ("jhgghgj","A")
   insert into mb_data  (data,c_code) values ("kjhjjkjkk","B2")
   insert into mb_data  (data,c_code) values ("sffdggfdfg","B1")
   insert into mb_data  (data,c_code) values ("hhhhlh","C")
   insert into mb_data  (data,c_code) values ("jhgghgj","A")
   insert into mb_data  (data,c_code) values ("kjhjjkjkk","B2")
   insert into mb_data  (data,c_code) values ("sffdggfdfg","B1")
   insert into mb_data  (data,c_code) values ("hhhhlh","C")
   insert into mb_data  (data,c_code) values ("jhgghgj","A")
   insert into mb_data  (data,c_code) values ("kjhjjkjkk","B2")
   insert into mb_data  (data,c_code) values ("sffdggfdfg","B1")
   insert into mb_data  (data,c_code) values ("hhhhlh","C")
   insert into mb_data  (data,c_code) values ("jhgghgj","A")
   insert into mb_data  (data,c_code) values ("kjhjjkjkk","B2")
endif

** END HEADER -- do not remove this line
//
// Generated on 2020-05-30
//
parameter bModal
local f
f = new test_lookupsqlForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class test_lookupsqlForm of FORM
   with (this)
      height = 16.5455
      left = 9.2857
      top = 5.0455
      width = 89.0
      text = ""
   endwith

   this.MB_DATA1 = new QUERY(this)
   with (this.MB_DATA1)
      left = 4.0
      width = 6.0
      height = 1.0
      sql = 'select * from "mb_data.DBF"'
      active = true
   endwith

   with (this.MB_DATA1.rowset)
      with (fields["c_code"])
         lookupSQL = "select * from mb_country"
      endwith
   endwith

   this.MB_COUNTRY1 = new QUERY(this)
   with (this.MB_COUNTRY1)
      left = 15.0
      width = 10.0
      height = 1.0
      sql = 'select * from "mb_country.DBF"'
      active = true
   endwith

   this.PUSHBUTTON1 = new PUSHBUTTON(this)
   with (this.PUSHBUTTON1)
      onClick = class::PUSHBUTTON1_ONCLICK
      height = 1.0909
      left = 36.0
      top = 12.7727
      width = 15.2857
      text = "Clear Filter"
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.mb_data1.rowset
      integralHeight = true
      allowEditing = false
      height = 8.9091
      left = 4.2857
      top = 1.5909
      width = 75.5714
   endwith

   this.COMBOBOX1 = new COMBOBOX(this)
   with (this.COMBOBOX1)
      onChange = class::COMBOBOX1_ONCHANGE
      onOpen = class::COMBOBOX1_ONOPEN
      height = 1.0
      left = 4.0
      top = 12.7727
      width = 27.2857
      dataSource = form.mb_country1.rowset.fields["country"]
      style = 1        // DropDown
   endwith

   this.rowset = this.mb_country1.rowset

   function COMBOBOX1_onChange()
      lur = form.mb_data1.rowset.fields['c_code'].lookuprowset
      lur.applyLocate( "country = '" + this.value + "'" )
//      ? lur.fields[1].value,lur.fields[2].value
      form.mb_data1.rowset.filter = "c_code = '"+lur.fields[1].value+"'"
      return

   function COMBOBOX1_onOpen()
      this.value = 'Select Country'
      return

   function PUSHBUTTON1_onClick()
      form.combobox1.value = 'Select Country'
      form.mb_data1.rowset.filter = ''
      return
endclass