How To Create Filtered, Ordered DropDownBox Lists

Author: Steve Workings

Have you used the DropdownBox control for any of your fields yet? If you haven't, then you should consider trying it where appropriate. If you have, you may have run into a limitation in the point & click interface. The limitation is that there's no way to filter or order the records using the genies. Let's see what you can do about this.

Image 1 shows a typical developer's first attempt to populate a DropDown list using a SQL data source. But if you need to filter (and to a lesser degree order) your list based on variable values or other considerations, you'll quickly run out of road.

The solution lies in setting the radio button at the top of the dialog to "Variable" as shown in Image 2. You'll also note that Image 2 shows that the list will be derived from a variable named vUser_List. 

The remaining question then, is how to create vUser_List. 

If you look through the Grid events, you'll find the OnGridInitialize event, with a helpful hint in the Default Function Prototype that includes this commented snippet:

'Example:

'Define a list for a drop-down box

'e.rtc.list1 = comma_to_crlf("Red,Green,Blue")

It doesn't take much imagination to realize you can take this further.

Below is some code modeled from one of my multi-tenant applications. In this application, the vUser_List values must be different based upon the logged in ClientID. I've added a lot of comment to the code to help explain as well as I can. Here's a simplified but fully functional version of the Xbasic code:

'DIM a connection variable

DIM cn as SQL::Connection

dim flagResult as l

flagResult = cn.open("::Name::MyConnectionString")

if flagResult = .f. then

          ' ErrorMsg is my own User-Defined Function

          ' -------------------

          ErrorMsg(cn.CallResult.text)       

          end

end if

'Specify Portable SQL

' -------------------

cn.PortableSQLEnabled = .t.

' In this application, the User List must be filtered by the vClientID.

' The vClientID is actually held in session.__protected__ClientID, but

' is hard-coded here for demonstration purposes.

' -------------------

DIM vClientID as c = "12345"

DIM args as sql::arguments

args.add("ClientID",vClientID)

' The SQL statement, and further code below, is structured to

' display one value, but store another. The format is:

' DisplayedValue | StoredValue

' -------------------

dim sqlStatement as c

sqlStatement = <<%sql%

SELECT Concatenate(LASTNAME, ', ', FIRSTNAME, '|',ID)  AS Expr1

FROM users

WHERE CLIENT_ID = :ClientID  

ORDER BY LASTNAME

%sql%

'Execute the Query

' -------------------

flagResult = cn.Execute(sqlStatement, args)

if flagResult = .f. then

          ErrorMsg(cn.CallResult.text)

          end

end if

dim rs as sql::resultset

rs = cn.ResultSet

'Now, dump the contents of the ResultSet to a variable

' -------------------

dim vUser_List as c

vUser_List = rs.ToString()

' Add in a "not selected" choice of a hyphen at the top of the list

' -------------------

e.rtc.vUser_List = "-|-" + crlf() + vUser_List

'Close the connection

' -------------------

cn.close()