In Action Form there are many times when we need to have one drop down change based on the selection of a previous drop down. Action Form has a very easy way to do this and is described in Bogdan’s video below.
In his video he will show you how to enter the values (Hard Coded) into each drop down and connect them together. Works perfect until the day comes you want these drop downs to be dynamic and loaded from a SQL Server select statement.
Today I am going to teach you that it is not only possible but very easy to tie two dynamically created drop downs via SQL that depend on each other for what values will appear. Let continue to use Bogdan’s example of Car Brands and Models illustrated in his video. (Please if you have not watched this video, now would be a great time).
Let’s first pretend that your SQL Data Structures consist of two tables.
The first table called (Brands) holds the brands of cars and looks like this.
|BID (Integer Brand ID)
||BName (varchar Brand Name)
The second table called (Models) holds the model of the cars and looks like this.
| BID (Integer Brand ID) **
||MID (integer ModelID)
||MName (Model Name)
** The ID of the Brand this Model belongs to
Now that we have data this is SQL select we use to load the first drop down of Brands:
Select BName,BID from Brands
*** This is the standard syntax Action Form uses to load a Text and value to a dropdown. There is no need for any varchar conversions or adding the ‘|’ to separate the Text from the Value.
The next drop down Models will be based on the value of this drop down and is where the magic happens. We are simply going to emulate what Bogdan hard codes in his video with a SQL Select that looks like this.
Select convert(varchar,BID)+’/’+MName+’|’+convert(varchar,MID) from Models
Let’s take a look at the query. But first let’s remind ourselves of the syntax Action Form requires. We must place the value of the selected item from the Brands drop down in front of a forward slash. Then the text we wish to display (Models). Next we use a pipe to separate what is shown in the dropdown, from the actual value of the drop down.
I simple built a string to do exactly that. I first convert the BID to varchar so I can concatenate the integer ID to the ‘/’ and text coming from Model name (MName). Next I added the ‘|’ and the converted MID to varchar.
It really is that easy, but it could be even easier. Please note I illustrate the method upon where the Drop down Text and Value are two different things. My programming techniques usually refers to joined tables where the ID is stored not the actually text. HOWEVER, we could have achieved the exact same thing Bogdan does by simplifying the queries even further. Bogdan’s example removes the value from the equation and allows the Text of the dropdown to be the Value. This is a programming choice made by you and how you store your data. Here are the simple queries:
Select BName from Brands
Select BName+’/’+MName from Models join Brands on Brands.BID=Models.BID
In conclusion this will help you design better forms for your users where the data in the dropdowns will be changing via your software. Please note that my table illustrations are just one of many ways your data may be stored. Please retrieve your data and build the select statement to the syntax above and this will work.
I hope this helps and as always should you have any questions about this topic or others I can be reached at firstname.lastname@example.org or on DNN Sharps’s Support Forum