Friday, March 13, 2015

How To Tie Two Dropdowns Together Dynamically Via SQL In Action Form

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)
1	Alfa Romeo
2	AMC

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)
1	1	Alfetta
1	2	GT
1	3	GT3
2	4	Ambassador
2	5	Concord
2	6	Gremlin

** 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:

Brands Dropdown

Select BName from Brands

Models Dropdown

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 jmattox@genconsult.com or on DNN Sharps’s Support Forum

Author: Jerry Mattox

0 Comments

Rate this article:

5.0

Categories: Action Form, Tip Of The WeekNumber of views: 6129

Tags: action form tips Action Form

Jerry MattoxJerry Mattox

About Us

DNN Sharp is a leading provider with a proven track record in defining, designing and developing DNN Modules catering for a passionate community of thousands of users.

While our core focus is on DNN modules, our mission is to provide top quality products complemented by fast and reliable Customer support. We listen to our Customers and produce a variety of solutions to meet the complex needs of our global audience.