Multiple Combobox Behaviors

[stanlyn]

Hi,

Using a combobox control with a rowsourcetype = 3 and a sql statement into cursor holds the list values to be selected. With the controlsource representing the destination table.field, I need for it to:

1. accept a new value from the user and if value is in the list, position it to the point that matches. IE. The underlying table has a value of "ABC Autos" and the user types "ABC" they are jumped to the opened list with "ABC" Autos" highlighted. So, it must be type=0 (combo and not list)

2. If there is no match, then position the selection to the next higher value as displaying nothing doesn't give the user a clue and doesn't help the user find the correct one. IE. The list maybe "A B C Autos" Positioning the selector within the list near where the entered text should be located, whether found or not, is important as it helps the user find the correct one faster.

3. I'm creating the cursor in the dropdown code. What would that statement look like that will land the user on the next item in the list if the entered text was not found?

4. Do I have to code and test against both the combo's built-in dropdown arrow and built-in textbox? I think yes... Two ways, one by not opening the listbox portion and another staying in the textbox portion only? Appears as too much juggling to do here as a user clicks in textbox and enters text and presses enter or clicks the downarrow, where and when to validate, and how to esc which reverts back to the "before selected value"

So far, I have not yet achieved the expected results and have not had a chance to tryout some theories as discussed in Tamar's excellent article "Combos and Lists-The Forgotten Controls" and has helped in the understanding.

So for now, I have no "what isn't working" question for you experts to answer. Instead, I'm asking for ideas on how to achieve the above goals. Most articles say it cannot be done natively and need a full custom control. What methods should be used? I found Tamar's article while searching for the firing order of the combobox and really never found a conclusive list, however Tamara partially addressed it. I don't remember the details now and will have to revisit.

How is everyone doing this or ideas on how to do this?

Thanks, Stanley

 

[Chris Miller]

One question, Stanlyn,

What is in the grid column that's having the combobox? Is it a foreign key field you eventually want to maintain by the combobox in that grid column, or is it a char field, which you want to set to the final displayvalue of the combobox?

Chriss

 

[stanlyn]

Chris,

is it a char field, which you want to set to the final displayvalue of the combobox?

Yes, as the grid column has 2 objects, one is the header and the other is a combobox where its displayvalue is the underlying table. I removed the default textbox for that cell.

The grid contains customer records from the customer table and the combobox is bound to the zipcode field from the customer table. The lookup table is "zipcode"

Hope that explains it better.

Thanks,
Stanley

 

[stanlyn]

(The internet uses slashes, not backslashes).

Yes, and I knew that... Unix also uses slashes, and at times I mix them up as well when working between them. What is it when you are looking straight at the problem and don't see it.

Actually, the url works with the backslashes in Edge, Chrome and Firefox after they or IIS auto converts them.

Good catch though...

Stanley

 

[stanlyn]

I never said you you'd not use an alias. You put restrictions in my mouth I never told you about.

Thats the problem, you went against SQL query and never said what you would use, leaving me to only speculate.

I just said I never would use a SQL query as rowsourcetype. Because you don't want to repeatedly do SQL potentially in every Interactivechange that does a requery of the cobobox items. What you should aim for is letting the interactivechange navigate the list of items you got once initially.

I agree.

Your video also shows that the list coming up is full and just positioned on the best match. And as far as I understand your demand that item list is a full table, so alias as rowsourcetype is the case here.

Yes, it is a full list of all rows in the database.

Same with an autocomplete textbox, you'd specify an autocomplete table with fully populated data column. Let's just put this aside a bit for now. I find it already is a solution, but you haven't even looked into it yet.

I'll do a test on this autocomplete table with the textbox, but I see an immediate issue with anything other than a combo. Within the grid cell, what do you plan to use for the data entry part and for the other list part?

Can we use a textbox as another control within the cell along side the combo and trigger the combobox? Doing so would mean that we would lose the visual downarrow component of a combobox and not the preferred solution. Doing so would involve toggling the column's selected control and may not be an issue.

If this was on a form, I would create a textbox and combobox and use them together, like setting the textbox.value to the value of the combo.DisplayValue after trapping for ESC. The textbox.controlsource property is not set programmatically. I do not think this form idea will work in the grid. Your thoughts?

Stanley

 

[stanlyn]

the textbox portion is not just free from influence of the combobox items list.
When you set a controlsource the displaytext of the textbox will be the first column of the items list, that is the first column of the rowsource of the combobox.
So you're not starting empty in that textbox portion and can use it for input of the partial name you want to incrementally search for in the items list. That makes the combobox not usable for our idea. No matter how much we differ about details. The textbox is not independent from the items list. And that dependency is strong within a grid, as a gridcolumn always has a controlsource the combobox also inherits. Even with BoundTo = .F. you don't escape that dependency and influence, you don't have the textbox portion for your own usage only, anyway.

I saw the same behavior. I may be getting closer by creating a container that contains a textbox and a combobox reduced to the width of the downarrow, side-by-side. Then using the container within the cell. The combo has no controlsource, only rowsource and type. The textbox component has the controlsource.

Stanley

 

[stanlyn]

In your video you show the data comes from a multiple field table. If you make the Firstname column of a combobox to pick from and allow entering a new firstname, well, what will the rest of the data empty. What about that.

Yes, the table has multiple fields and I can define what I want to show the user when they do a F6 lookup. The rest of the data could be empty, it doesn't matter as it is in the lookup table and shown if it exists. It helps the user determine if its possibly the same record and act accordingly.

If actually a new Firstname is entered that isn't in the list to pick from, then you have other fields, most of them will be mandatory and not optional to know, won't they? So what about that, Stanlyn?

You keep forgetting this is lookup only. Nothing about the lookup record other than it is either there or not, and just position the cursor on the nearest match, ready for an "ENTER" (chr(13)), "ESC" (chr(27)), or nav arrows. We are not adding or updating the record in the lookup table. If ENTER is pressed with the lookup record selected, then we update the grid table, not the lookup table.

Even if you have no field rules and a mostly empty record is allowed you're introducing sparse data that later needs to be cared for, if you allow new records to be entered by the combobox. If the grid list is not that table but the person column is just about maintaining a foreign key of a person, then you also don't have the other fields of the person record in the grid to enter that. And if you even would have the person list in the grid, then you would have the firstname column in it without a combobox. The navigation to a person then is best done with textboxes under or over the grid.

Lets try this another way using a customer table that we are editing and a postalcode table which is the lookup table...
At the zipcode field in the grid for the customer table, user types part of the zipcode and does a postalcode table lookup and positions the selector on nearest match, whether in the table or not. Pressing ESC cancels and takes us back to the textbox with its value still set to what it was when the lookup was initiated. Pressing ENTER will update the textbox value to the selected value and move focus to next control in tab path.

Hope it explains it a bit better.

Stanley

 

[stanlyn]

There's a case you enter Rickeyzzzz at about 2 minutes and the result of that is that the navigation is at Rickie directly under Rickey. The only real advantage about this near search is that you're not nowhere or at EOF, but surely not that you're at one record below the last match.

The main advantage is in the eyes of the user, where they can clearly see a match, or a NO match, and if no match, they see what the area around where it should be looks like, instead of just being told there is no match. This allows somewhat a 3d view of the data with respect of what they are typing.

Don't you hate it when you are told a problem exists and no other explanation or indicators. This sort of addresses that.

If the user expects a match and doesn't get one, then they can try a different way. If we were doing this on a name field and user types in first name and the other displayed fields indicates the name is not this person, the user can try last name, and if still no match, the user can try company name and on and on. Extremely useful feature.

Thanks,
Stanley

 

[Chris Miller]

That problem is also addressed when they type Rickey and while they type, the list already displays. Then they likely already see Rickey after typing Ri or Ric. There is no need for the entry of Rickeyzzz to go to Rickie, if that's all more interactive than first typing a part and then needing to press F6 to search. If the user can interactively see where he is in the list while typing, ater reaching Rickey they already see the next entry is Rickie. And if the then type Rickeyz that should not get them to EOF. But it should also keep them at the better match Rickey and not put them to Rickie, that's what's not optimal at your idea. And I think you don't see that, as you don't think of the interaction of seeing the list of items while you type.

A combobox could theoretically expand while you still type in the textbox portion. The crux is just is that expanding the list already picks an item. That overwrites the textbox portion and you're interrupted in your typing. So that needs to be avoided or corrected.

Chriss

 

[Chris Miller]

I don't get an interactive list display to work, so I get back to something simpler and less useful, but fitting your needs.

A combobox in combo mode that allows you to enter a partial value - lastname in this demo - and then press END (you can adapt what key to use for the search this in the keypress event) to then SEEK with NEAR ON. Pressing ESC resets to the value memorized at GotFocus() event and ENTER enters the entered name, if it's not found in the list.

The prerequisites of this are mentioned in the code, but for sake of explaining it all in advance:
I intentionally describe what needs to be set by having all that in the Init() code of the combo. You could also set properties more directly, but what you can't set in properties is the prerequisite that the alis for the items needs to be sorted by an index that is also used to SEEK for searching. The combobox must also be used with rowsourcetype 6 (Fields), which determines which fields to see from an alias when the list is expanded, and since that also varies I put this into the init code of the combo1 combobox, too. Notice while the table has 3 columns, setting ColumnCount=2 is only for the display f items to not display the ID column, not even using a width 0. The BoundColumn can still be 3 as that's just about the number of the field in the Rowsource value, not the tables field number nor is it limited by Columncount.

Since the style is combo you can type into the textbox that's initially empty when the controlsource isn't picking a record by ID. I initialized the form property idPerson to 0, too, to ensure that. USed in a grid column you'll alwas have the column.controlsource inherited by the combobox, there's no way around that, the grid column will always override the controlsource of the currentcontrol. But even if there is a record selected, the Format='K' setting means the text portion is selected, so you can type, if you want to search another name, and that overwrites the current selection.

What I first intended and gave up is that typing incrementally finds the item in the list. Instead, you have to activate the search with a key, I picked the END key in this demo, as it's just at the opposite extreme position to ESC on my keyboard, the last key in the first row of keys. The nKeycode for that is 6, the nKeycode for F6 is -5, for example, and you find other key codes in the help of the INKEY() function.

The code also depends on the Rowsource starting with the alias name of the items of the dropdown lilst portion, if you don't specify the first field with its full name alias.field but only use the fieldname, that will fail miserably. It's one of several things I did to have code that will generally work and not only for this Persons table as the rowsource. But there are some things that nevertheless need the knowledge of the specific case, not only in the Init(). The case for ENTERing new records needs to know what field to populate with the Displayvalue entered. I therefore have foreseen a separate Method combo1.NewRecord() that will have the necessary Insert-SQL.

The rest of the combo1.code could also be class code that generally works, so specifics about a combobox instance are in the init or the properties you set and in the NewRcord() method. Just one more thing to pay attention, the name of the ID column to which the combobox is boundto by BoundColumn number is used to also find this field name by Getwordnum(This.RowSource,This.BoundColumn,','). It could be necessary to expand that to aliasüfieldname in case the fieldname only is also present in another workarea active at the moment. Within a grid the grid workarea is not the list for the combobox items, just take care about that and keep it in mind.

The rest is explainied in the code. The major "magicc" happens in the Keypress event and is pretty much what I told you to use: a SEEK in SET NEAR ON mode. Even that can get to EOF, if you enter something that's even after the last record. I amend this situaation by SKIP -1 in that case, so you get to the last item, if you search for Z, for example, I don'T mean Rickeyzzz, but really just plain Z. The alternative would be that the expanded list would have the first name selected and that's far off Z, obviously.

You can make a class of it by taking the comobo1 procedures and make them class method code. Then you need to individually write the init code or set properties up at designtime and pay attention to some specifics I explained, like the NewRecord method. That should be empty in the class and must be programmed for each individual case. You could generally write in APPEND BLANK and REPLACE the one field you SEEK in with the Displayyvalue, but depending on field rules, foreign key realations etc. that might nort work in general, so I rather don't generalize this but make it a method to be programmed for each special case.

Local loForm

loForm =Newobject("searchablecombo")
loForm.Show
Read Events

Define Class searchablecombo As Form
   Top = 130
   Left = 198
   Height = 74
   Width = 348
   DoCreate = .T.
   Caption = "Searchable Combo"
   BindControls = .F.
   idperson = 0
   Name = "Form1"

   Add Object combo1 As ComboBox With ;
      Value = 0, ;
      Height = 24, ;
      IncrementalSearch = .F., ;
      Left = 24, ;
      TabIndex = 1, ;
      Top = 24, ;
      Width = 300, ;
      Format = "K", ;
      OldValue = '', ;
      Name = "Combo1"

   Procedure Load
      Close Tables All
      Erase Persons.*

      Create Table Persons(Id Integer Autoinc, LastName C(42), FirstName C(42))
      Insert Into Persons(LastName, FirstName) Values ("Meyer", "Corinne")
      Insert Into Persons(LastName, FirstName) Values ("Bogart", "Humphrey")
      Insert Into Persons(LastName, FirstName) Values ("Hepburn", "Katherine")
      Insert Into Persons(LastName, FirstName) Values ("Clooney", "George")
      Insert Into Persons(LastName, FirstName) Values ("Miller", "Chris")
      Insert Into Persons(LastName, FirstName) Values ("Lewis", "Mike")
      Insert Into Persons(LastName, FirstName) Values ("Smith", "Jenny")

      Index On FirstName+LastName Tag firstlast
      Index On LastName+FirstName Tag lastfirst
      Use
   EndProc
   
   Procedure Init()
      This.BindControls = .T.
   Endproc
   
   Procedure Destroy()
      Clear Events
   Endproc

   Procedure Combo1.Init()
      Use Persons In 0 Shared Order Tag lastfirst
      This.RowsourceType = 6
      This.Rowsource = 'Persons.Lastname, Firstname, Id'
      This.Columncount = 2
      This.ColumnWidths = '137,137'
      This.BoundTo = .t.
      This.BoundColumn = 3
      This.ControlSource = "Thisform.idPerson"
   EndProc 

   Procedure Combo1.GotFocus
      This.OldValue = Transform(This.Value)
   Endproc

   Procedure Combo1.KeyPress
      Lparameters nKeyCode, nShiftAltCtrl

      If nKeyCode = 27 And nShiftAltCtrl=0 && ESC
         This.Value = This.OldValue
         Return
      Endif

      Local lcAlias
      lcAlias = Juststem(This.RowSource) && depends on rowsouretype "Fields" and first field specified by Aliasname.Fieldname
      * Also, this combobox rowsouce must be sorted by index to SEEK in

      * remember NEAR setting
      Local llNearOn, llSetValue
      llNearOn = (Set('Near')=="ON")

      Select (lcAlias)
      If nKeyCode = 13 And nShiftAltCtrl=0 && ENTER
         Set Near Off
         If Not Seek(Alltrim(This.DisplayValue)) And Alltrim(FirstName)==Alltrim(This.DisplayValue)
            * name not found, create a new record...
            This.NewRecord()
         Endif
         * Name either found or added as a new record.
         * make it the selection
         llSetValue = .T.
      Endif

      If nKeyCode = 6 And nShiftAltCtrl=0 && END
         Set Near On
         *Local lcMessage
         *lcMessage = 'Seeking '+Alltrim(This.DisplayValue)
         * Sarch and correct EOF with last item.
         If Not Seek(Alltrim(This.DisplayValue))
            Skip -1
         Endif
         *lcMessage = lcMessage + ', finding '+Lastname
         *Set Message to lcMessage
         llSetValue = .T.
      Endif
      
      * Make the found or newly inserted record the selection
      If llSetValue
         This.Value = Transform(Evaluate(Getwordnum(This.RowSource,This.BoundColumn,',')))
         If nKeyCode = 6 And nShiftAltCtrl=0 && END
            Keyboard '{ALT+DNARROW}'
         EndIf
      EndIf
      
      * Restore NEAR setting as it was
      If llNearOn
         Set Near On
      Else
         Set Near Off
      Endif
   Endproc

   Procedure Combo1.NewRecord()
      * should be an empty method in a general searchable combo class to be specified
      * for a concrete instance of the class only, as there's no general way to know which fields
      * of the table need to be specified in the insert of a new record
      Insert Into Persons (Lastname) Values (This.DisplayValue)     
   Endproc 

Enddefine

Chriss

 

[Chris Miller]

One more thing:

I asked you whether the combo is used to feed a column of the grid that is a foreign key or just a character column. In this case the result of the combo is a persons ID, not a persons lastname, so it's for a grid column that has a personid foreign key, not for a column of the lastname field. I think you could change that by switching to boundcolumn=1, but have one disadvantage with that: If two persons with same lastname exist, your grid column wouldn't care about that. In fact, it wouldn't care about the other fields of the persons table anyway, as you then just store the lastname into the grid column and the field associated with it in the grid recordsource.

And the other question I considered to you is also unresolved: How do you take care of the other fields, the firstname in this case? The grid column and your search entry can only cover the lastname. You could change it so that an entry of "Tolkien, J.R.R." is split up at the comma and inserted into the persons dbf file, but it would only cover the case of ENTER, not the SEEK which only looks into the index on the lastname+firstname, not lastname+','+firstname. And even if it would, to find that by SEEK you would need to explicitly enter the comma at position 43 in the entered name, not just anywhere. no matter if you'd use char or varchar fields, an index will always store a constant length key, not a variable length key.

I solved your demand - by my own terms at least, let's see what you answer, but it's not really very simple to make use of, if the table for the combo rowsource is more complex in terms of the ENTER feature to add new records on the fly. I get back to why MarK just offered what he offered in the beginning, it makes more sense that way. You may not care about the new records not having a firstname, but in the end you will need to care. Or you can only use this with tables of the type that have two fields - one Keyfield with the record ID and one data field. Such as a table of productcategories or a table of citynames. But even such simple tables usually do have extra data in them. A City may be stored with a geolocation and a category may point to a parent category. You can't maintain such extra data with such a combobox, where you only search and therefore also only enter one of the data fields.

And it may also all be much more complex. You might have a database for events with a guestlist that you might want to expand on demand. But then you will perhaps have an extra person in your data already, just not added to the guest list for that event. So what you would like to maintain is the table of eventguests, not the table of persons. It all boils down to me to have little usage for the new record feature. That needs specific addressing depending on the case.

What remains as a use case would really just be something like an assisted entry of character fields, really just the lastname of a persons list, where you can pick the lastname from persons you already know, which would use the same table maintained in the overall grid as the rowsource for the combobox, too. Or you would have a special extra lastnames table for that matter. And that comes very close to what you get from an autocomplete textbox, too. You can make use of several autocomplete tables for each more specific case, you're not restricted to one autocomplete table for all autocomplete textboxes.

Chriss