Return to Blog

Creating Filtered Ordered DropDownBox Lists

By Steve Workings - June 12, 2012

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 your list (and to a lesser degree order) 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()