| Subject | Re: Lookuprowset |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Mon, 1 May 2023 17:37:05 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | lookup_test.wfm, states_lookup.DBF |
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 | |