Creating custom-limited droplists: an example

Attached to this post is a little demonstration of how to provide customized dropdown lists.

A common requirement is that a dropdown list needs to be limited based on a previous choice on a form. Sometimes that previous choice corresponds to a column in the table that provides the drop-down info. In the attached example the dropdown info is contained in a table called DISTRICT. That table contains a column called REGION_ID, so the top option box and list shows how to limit the choices of districts to the ones in the previously-chosen region. Very standard: on the event accepted on the Option control where the region is chosen, you call the ResetQueue method of the dropdown list.

Behind the scenes, at the top of the ResetQueue method, there is a called to the Reset method, and that is actually a call to the Parent ViewManager class method. That does a SET(self.view,0) which sets the view with the current range limit, which is set up in the browse’s range limit as DIS:region_ID = region_choice (a single value limit set up in the Option control).

The above is important, because if you want to do something fancy, like use PROP:SQL to limit your choices in the dropdown, then your PROP:SQL statement needs to replace the SET statement.

The second example, further down the window has an option box that will limit your choices in the dropdown to a set of rows determined by PROP:SQL. There are two example options: show only districts where the district name begins with ‘G’, and show only districts where the address is a PO Box. To get this to work we basically need to completely replace the Reset method of the dropdown. In the embeds before the parent call for the Reset method we enter the following:

    self.Open  ! that's the view that is being opened
    if special_options = 1		! this is one of the choices from the Option control
        FDB6::View:FileDrop{PROP:SQL} = 'select district_id,district_name from district where district_name like ''G%'''
    elsif special_options = 2
        FDB6::View:FileDrop{PROP:SQL} = 'select district_id,district_name from district where address1 like ''P O Box%'''            
    end ! if
    if errorcode()

This code duplicates the Reset method, but replaces the SET(View) with a PROP:SQL and, importantly, terminates with a Return so the parent method is never called. Note that the PROP:SQL is on the view declared by the FileDrop template.

Of course, the demo doesn’t do anything useful. The Window doesn’t save a record using the dropdown values. But if you need to limit dropdowns (or even browses) based on some prior choice made, this should help you figure out how to go about it. The key is replacing the SET() with your prop:SQL and making sure the SET() is not called afterwards.

To actually run the demo:
Load the data in the district.csv file into whatever database you use
Change the owner and path name in the clarion dictionary to point to where you loaded the data
Compile and run the APP (39.3 KB)