Subject Re: Lookuprowset
From Peter <phb2020@hotmail.com>
Date Mon, 01 May 2023 11:56:54 -0400
Newsgroups dbase.getting-started

This certainly works.
My lookin dbf (ins_info.dbf) has 45 rows.  A bit too much for a ComboBox.  Instead of ComboBox, I use EntryField1, 2 characters wide.  Assuming ins_code that the user inserts into this EntryField1 is found in Ins_info.dbf (valid function assures that), with OnLostFocus from this EntryField1, the Lookupsql is meant to look in Ins_info.dbf for name of the Insurance company with that ins_code and enter field["ins_name"] into next EntryField2 (which cannot get focus).
I notice that a second dbf is created (test_lookup2.dbf).  Is that the only way to get the lookup?  I guess I am used to lookup() from my earlier non-dBase Plus program.
I am appending the form below that shows those 2 entryfields. They are on a separate main form that has all the necessary patient information

   this.ENTRYFIELDINS1ID = new ENTRYFIELD(this)
   with (this.ENTRYFIELDINS1ID)
      //onRightMouseDown = class::ENTRYFIELDINS1ID_ONRMD
      //onLostFocus = class::ENTRYFIELDIns1ID_OLF
      dataLink = form.query1.rowset.fields["ins1"]
      height = 26.0
      left = 82.0
      top = 275.0
      width = 33.0
      fontName = "Courier"
      fontSize = 12.0
   endwith

   this.ENTRYFIELDINS1NAME = new ENTRYFIELD(this)
   with (this.ENTRYFIELDINS1NAME)
      height = 26.0
      left = 126.0
      top = 275.0
      width = 370.0
      fontName = "Courier"
      fontSize = 12.0
      //value = from the lookupsql
      maxLength = 40
   endwith

> 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
>