How do I build a Double Drop-down in CFv5

A dynamic dropdown is a normal select drop-down where the option list changes when another drop-down in the form changes. An example might be a list of states or counties that changes when a 'country' select drop-down changes. This FAQ show some ways to build these.

In each case in this FAQ our form will have two select drops-downs with the names and ids dropdown1 and dropdown2. I recommend that in your form you replace these with more meaningful names e.g. country and state

We will also use very simple options lists to keep the examples clean and clear. Your options lists may be much longer and more complex.

Note for CFv4 please see this FAQ

Using static options

Static options lists are lists that are know before the form is created and so do not require a set of results to be looked up from a database table. 

To set this up open dropdown 1, and, on the General tab add the options you need to the Options box. I will keep the default 0=No, 1=Yes options.

Now click the Events tab click 'Add New Event' to add a second event and we will add the settings in the image below.

The first box in each event labelled 'On' sets the condition for this event; the second 'Value selected' box sets the value to check for.

Because checking for a 0 value can cause problems I'm only checking here for = 1 and != 1

The 'Action' box sets the action to be taken when the condition is met. Here we want to use 'Set Options'. (Other options on the list let you show/hide or enable/disable elements in your form.)

Next, the 'field id, fn() or event' box lets us enter the 'target' for the event. We want to set the options in dropdown2 so that is what goes here. Note: that we are using the id here not the name - I recommend that you set ids on every element and make them the same as the name wherever possible.

Lastly, the 'Options list or ajax event' box lets us add a set of options for each event. Here I've just added a set of letters in one case and numbers in the other so that I can clearly see the difference.

Save and Close the element; Apply the Form and click Test Form to see if the dynamic drop-down is working OK.

It should work, if not check the settings and check for any JavaScript errors on the page.

If you have more than two options you can add more events and more option lists - but this approach gets less tidy if you have more than 5 or 6 options lists to manage.

That is all that is needed to build dynamic dropdowns using static option lists. Note that we made no changes at all to the second, 'target', drop-down to get this to work - though you will want to add a Label, default settings, etc. 

Using Dynamic Options

In this example we'll go one step more complex, keeping the first dropdown static but getting the second set of options using Ajax to get them from a database query on the server. 

I'll use the same 'dropdown1' and 'dropdown2' select dropdown elements in the form. In the first dropdown I'll add some article categories and use the selection to set a list of article titles in the second dropdown.

The dropdown1 element

In dropdown1 I will add these options that match article categories in my site. These take the form of ID=option text

16=Big dogs
17=Small dogs

Then in the events tab we need two events again with the settings in the image below.

Apart from the 'target' (field id, fn(), or event) these settings are different from the previous set. 

The On box and value boxes are set to ='' (nothing selected) and !='' (something selected) so that they cover all possibilities.

If nothing is selected then we use the Set Options Action to add a 'Please choose' option to the second drop-down.

If something is selected then we use the Set Dynamic Options Action and use Ajax to link to a different event in the form. Here I've called this event ajax. 

That is all that is needed here, except to add an Empty Option on the General tab so that we can test that setting. 

The ajax form event

In the form Setup tab we need to create the new ajax event by clicking the Add New Event button and setting the name to ajax.

We will use two actions in the event; a DB Read to load the requested data from the database and a Custom Code action to format it to be returned to the form.

The DB Read action needs settings like this:

Enabled : Yes
Table name : xxx_content // your prefix will be different
Multi read : Yes
Enable Model ID : Yes
Model ID : Data
Fields : id,title // you may want to get different columns
. . .
Conditions :
  <?php
  return array( 'catid' => $form->data['dropdown1'] );
  ?> 

The code in the Conditions box is saying 'get all the records where the column catid is equal to the value selected in dropdown1.

Save and Close the DB Read and then add code like this to the Custom Code action:

<?php
$options = array();
if ( !$form->data['Data'] || count($form->data['Data']) < 1 ) {
  // no result was found
  $options[] = 'Please select a category';
} else {
  foreach ( $form->data['Data'] as $d ) {
    $options[$d['id']] =  $d['title'];
  }
}
echo json_encode($options);
?>

The first part of this code handles the case when no records are found and sends back just a 'Please choose' option.

The second part takes the options that have been found and re-formats them to send back as a JSON encoded array to be converted into an option list for the target dropdown2.

Note: A DB Read action with the Model ID set returns an array of results like this:

$form->data['Data'] => array(
  [0] => array(
    [id] => '16',
    [title] => 'Some title'
  )
  [1] => array(
    [id] => '18',
    [title] => 'Some other title'
  )
  . . .

The PHP reformats this to:

$options = array (
  '16' => 'Some title',
  '18' => 'Some other title',
  . . .
)