Subject Re: Lookuprowset
From Lee Grant <camilee@nospam.comcast.net>
Date Sun, 30 Apr 2023 19:07:52 -0400
Newsgroups dbase.getting-started
Attachment(s) lookupsample.wfm

Peter,

I can't remember for sure, but I think the code I'm including, may have
been a code sample written by Mervyn, that shows how to setup and use
lookupsql for use in a combobox.

Just study the structure, and you should be able to figure out how to
use it for your needs. The descriptions in the help and Ken's books help
a lot, and I hope you have the PDF versions of his books, as there is a
real simple example in his book of using it also.

Lee

On 4/30/2023 4:11 PM, Peter wrote:
> Frustration abounds in changing to my dBase 11 from XDML in dBase IV  and Visual dBase 7.01.  Goes back to early 1990’s when I started.
 > I was used to lookup(). What I’m trying to do is lookup the value of a field (ins_name) in ins_info.dbf indexed on ins_code.
 > The ins_code Is a 2 character field, and ins_name is the full name of an insurance company. For instance, “UH” is the ins_code for “United Healthcare”, or “MC” for “Medicare” etc.
 > The main form has an entryfield for the user to enter the 2 letter code. I have validated existence of that 2 letter code. No eof() to worry about. The next field (EFIns1Name) is the full name of the Insurance Company, which cannot get focus, but I want a “lookup” of the ins_code in the ins_info.dbf to return the value of the ins_name and put that value as the full Insurance company name in that second entryfield (EFIns1Name)
 > Most examples in these news groups are too complicated for me to adapt. So some specific code would be appreciated.
 > I’ve been struggling with Lookuprowset and lookupsql to no avail.
 > Thanks in advance for your help.
 > Peter


******** Start of example code *********
******* Watch for line wrap ***********
if file('test_lookup.dbf')
  // drop table test_lookup
endif

if not file('test_lookup.dbf')
   create table test_lookup  (id autoinc,data1 character(15),data2 character(15))
//endif

   insert into test_lookup  (data1,data2) values ("Alpha","First")
   insert into test_lookup  (data1,data2) values ("Baker","Second")
   insert into test_lookup  (data1,data2) values ("Charlie","Third")
   insert into test_lookup  (data1,data2) values ("Delta","Fourth")
endif
if file('test_lookup1.dbf')
  // drop table test_lookup1
endif

if not file('test_lookup1.dbf')
   create table test_lookup1  (id autoinc,name numeric(10,0))
//endif

   insert into test_lookup1  (name) values (2.00)
   insert into test_lookup1  (name) values (1.00)
   insert into test_lookup1  (name) values (4.00)
   insert into test_lookup1  (name) values (3.00)
endif

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

class test_lookupForm of FORM
   with (this)
      height = 16.4545
      left = 41.5714
      top = 4.8182
      width = 57.2857
      text = ""
   endwith

   this.TEST_LOOKUP11 = new QUERY(this)
   with (this.TEST_LOOKUP11)
      left = 19.0
      top = 1.0
      width = 10.0
      height = 1.0
      sql = 'select * from "test_lookup1.DBF"'
      active = true
   endwith

   with (this.TEST_LOOKUP11.rowset)
      with (fields["name"])
         lookupSQL = "select * from  test_lookup"
      endwith
   endwith

   this.COMBOBOX1 = new COMBOBOX(this)
   with (this.COMBOBOX1)
      onChange = class::COMBOBOX1_ONCHANGE
      onOpen = class::COMBOBOX1_ONOPEN
      dataLink = form.test_lookup11.rowset.fields["name"]
      height = 1.0
      left = 3.1429
      top = 6.0
      width = 21.2857
      style = 1        // DropDown
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      height = 1.0
      left = 28.1429
      top = 5.9545
      width = 14.8571
      value = ""
   endwith


   function COMBOBOX1_onChange
      form.entryfield1.value = form.test_lookup11.rowset.fields["name"].lookuprowset.fields["data2"].value
      return

   function COMBOBOX1_onOpen
      this .value = "Pick a name"
      return

endclass

*************** End of example code