Subject Re: Lookuprowset
From Peter <phb2020@hotmail.com>
Date Tue, 02 May 2023 18:00:15 -0400
Newsgroups dbase.getting-started

Mervyn Bick Wrote:

> On 2023/04/30 22:11, 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
 >
> Lookup() is an XDML function and, unfortunately, there isn't an exact
> equivalent in OODML.  LookupRowset and lookupSQL do something similar
> but they are used in a quite different manner.  If you have a table with
> the ins_code in a field and you want to display the contents of the
> table in a grid you would use look lookupSql or lookupRowset to change
> the code to the name in the entire rowset.  To the user, it would look
> as if the table contained the name and not the code.
 >
> If you want to emulate the lookup() function in OODML I'm afraid it
> means DIY.
 >
> With DML you would open the lookup table in a work area.  In OODML you
> need to create a query object.  To actually navigate to the correct
> record you could use the rowset's applyLocate() method but this means
> that dBASE has to start from the top of the table and scroll down until
> it finds the record each time.  An alternative is to use a parameter
> driven query.  Change the parameter and requery the query and dBASE will
> fetch the required record.  This is a far more efficient way of finding
> the record and it is the method I've used in the attached example.
 >
> I've used a table of USA States.  The stateID is two uppercase
> characters.  To make the form case-insensitive  I've used [.... where
> stateID = upper(:stateID)] in the query's SELECT statement.  The user
> can enter CA or ca and the program will still find California.
 >
> I've used the entryfield's onKey event handler to do the work.  Each
> line is commented so you should be able to see what has been done.
 >
> Mervyn.
 >
>
> ** END HEADER -- do not remove this line
 > //
 > // Generated on 2023-05-01
 > //
 > parameter bModal
 > local f
 > f = new lookup_testForm()
 > if (bModal)
 >    f.mdi = false // ensure not MDI
 >    f.readModal()
 > else
 >    f.open()
 > endif
 >
> class lookup_testForm of FORM
 >    with (this)
 >       onOpen = class::FORM_ONOPEN
 >       height = 21.5455
 >       left = 27.1429
 >       top = 0.0455
 >       width = 60.2857
 >       text = ""
 >    endwith
 >
>    this.STATES_LOOKUP1 = new QUERY(this)
 >    with (this.STATES_LOOKUP1)
 >       left = 36.0
 >       width = 11.0
 >       height = 1.0
 >       sql = "select * from states_lookup.DBF where stateID = upper( :stateID)"
 >       params["stateID"] = ""
 >       active = true
 >    endwith
 >
>    this.ENTRYFIELD2 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD2)
 >       when = {||false}
 >       height = 1.0
 >       left = 24.0
 >       top = 6.3636
 >       width = 29.0
 >       value = ""
 >    endwith
 >
>    this.TEXT1 = new TEXT(this)
 >    with (this.TEXT1)
 >       height = 0.7727
 >       left = 3.1429
 >       top = 4.9545
 >       width = 14.5714
 >       text = "Enter State ID "
 >    endwith
 >
>    this.ENTRYFIELD1 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD1)
 >       onKey = class::ENTRYFIELD1_ONKEY
 >       height = 1.0
 >       left = 4.4286
 >       top = 6.4545
 >       width = 8.0
 >       value = ""
 >    endwith
 >
>    this.ENTRYFIELD3 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD3)
 >       height = 1.0
 >       left = 4.4286
 >       top = 9.0455
 >       width = 25.7143
 >       value = "dummy to accept focus"
 >    endwith
 >
>    this.TEXT2 = new TEXT(this)
 >    with (this.TEXT2)
 >       height = 1.7273
 >       left = 5.0
 >       top = 11.1818
 >       width = 48.4286
 >       text = "Entering a valid two character stateID will look up the State name and dispay it in entryfield2."
 >    endwith
 >
>    this.TEXT3 = new TEXT(this)
 >    with (this.TEXT3)
 >       height = 1.5
 >       left = 5.0
 >       top = 13.2727
 >       width = 48.1429
 >       text = "Entryfield2 can never receive focus as its WHEN event handler has been set {||false}"
 >    endwith
 >
>    this.TEXT4 = new TEXT(this)
 >    with (this.TEXT4)
 >       height = 2.2727
 >       left = 5.0
 >       top = 16.1818
 >       width = 48.1429
 >       text = "A valid stateId will lookup the State name and display it in entryfield2.  Focus will move to the next object in the z_order."
 >    endwith
 >
>    this.TEXT5 = new TEXT(this)
 >    with (this.TEXT5)
 >       height = 1.5
 >       left = 5.0
 >       top = 19.2273
 >       width = 48.1429
 >       text = "An invalid stateID will blank the entry in entryfield1 and wat for a new value."
 >    endwith
 >
>    this.TEXT6 = new TEXT(this)
 >    with (this.TEXT6)
 >       height = 1.5
 >       left = 4.2857
 >       top = 0.6818
 >       width = 30.7143
 >       text = "Mimic the DML LOOKUP() function in OODML"
 >    endwith
 >
>
>    function ENTRYFIELD1_OnKey(nChar, nPosition,bShift,bControl)
 >       if  len(this.value) >1  // Two characters entered
>          form.states_lookup1.params['stateID'] = this .value //pass value in entryfield to query parameter
 >          form.states_lookup1.requery()  // fetch state name for the given stateID
 >          //Test to see if a valid stateID was entered
 >          if form.states_lookup1.rowset.count() = 1 //found the state
 >             form.entryfield2.value = form.states_lookup1.rowset.fields['state'].value
 >             this.before.setfocus()  //Move focus to next object in z-order
 >          else // stateId not found  
>             form.entryfield2.value = 'Invalid StateID'
 >             form.entryfield1.value = '' //Empty entryfield
 >             form.entryfield1.setfocus() //Back to entryfield to try again
 >          endif  
 >       endif  
>       return
 >
>    function form_onOpen()
 >       form.entryfield1.setfocus()
 >       return
 >
> endclass
 >

I haven't forgotten about you, Mervyn.
I am trying to change your form to use the actual DBF names that I have.
If I get stumped I will paste what I have changed and resend to you.
Peter