Monday, June 29, 2015

Combobox with Filtering Capability

Here is a combobox that can filter the items in the dropdown list based on what has been typed so far.  It is a good candidate for replacing my xBox class if you don't need a Quickfill/Auto-Complete or if you wanted only a dropdown that is being filtered.  This uses SQL SELECT as RowSourceType.

Before running the sample, there are two things you should know outright:
  • Double-clicking on the textbox portion will clear DisplayValue and will restore the original SQL SELECT
  • I used Ctrl+Enter to trigger the dropdown section
Here are the codes:

* Combobox with Filtering Capability
* Jun Tangunan, June 29, 2015

Public oForm
oForm = Createobject("cboFilter")

Define Class cboFilter As Form
      DoCreate = .T.
      Caption = "Combobox with Filtering Capability"
      ShowTips = .T.
      AutoCenter = .T.

      Add Object combo1 As ComboBox With ;
            Left = 30, ;
            Top = 20, ;
            Width = 300,;
            ToolTipText = 'Press Ctrl+End to show dropdown list, double-click to Clear'

      Add Object command1 As CommandButton With;
            left = 30,;
            Top = 80,;
            Caption = 'Show Values',;
            Width = 100

      Procedure Load
            Close Databases All
            Use Addbs(Home(2))+'Data\Customer' Alias customer Shared

      Procedure combo1.Init
            This.AddProperty('MySQL','Select company,country, cust_id from customer '+;
                  'order by 1 into cursor junkcust nofilter')

            With This
                  .RowSourceType= 3
                  .RowSource = This.MySQL
                  .BoundColumn = 3
                  .ColumnCount= 2
                  .ColumnWidths = '300,100'

      Procedure combo1.KeyPress
            Lparameters nKeyCode, nShiftAltCtrl
            Do Case
                  Case nKeyCode = 10  && Ctrl+Enter
                        Keyboard "{ALT+DNARROW}" Plain Clear
                  Case Inlist(nKeyCode,24,5,13)
                        This.MySQL = 'Select company,country, cust_id from customer WHERE  '+;
                              'ALLTRIM(this.DisplayValue) $ company order by 1 into cursor junkcust nofilter'
                        This.RowSource = This.MySQL

      Procedure combo1.DropDown
            If Empty(This.DisplayValue)
                  This.RowSource = This.BaseSQL
                  This.RowSource = This.MySQL

      Procedure combo1.DblClick

      Procedure command1.Click
            Messagebox('Value      :'+Thisform.combo1.Value+Chr(13)+;
                  'DisplayValue: '+Thisform.combo1.DisplayValue)



xBox - Basic
  • Has Quickfill or Auto-Complete (optional)
  • If dropdown feature is used (_SetGrid), then dropdown grid will appear immediately and gets filtered as you type (optional)
  • It can have a combination of Quickfill and dropdown
  • It can return two values from two fields, i.e., Value which is what is on the textbox and ExtraValue which is hidden 
Combobox with Filter
  • I have not introduced Quickfill here though I believe it can be done
  • Dropdown list won't appear as you type like xBox.  You have to either press Ctrl+Enter or click on the dropdown arrow for you to see the dropdown list with filtered result.  
  • Sample shows how we can return two values based on Value and DisplayValue properties (via BoundTo and BoundColumn).
  • Shows how to hide the last column where Value comes from.  It  is easy enough.  Just don't include it on the ColumnCount.
Anyway, some users may not be comfortable with the way xBox works, that is why I show here how a Combobox can be used that will work like an xBox.


No comments:

Post a Comment