DropDownList
A DropDownList is a Control Class that is used to put a drop-down list (a select in HTML) on a form, find page or query page in NetForum.DropDownList controls are defined in NetForum from the Column level, although they can be overridden on a case by case basis as described in Column.
The values in a DropDownList are populated based on the data in another Table (often called a Lookup Table) or a list of static values can be entered directly in the Column or Form Control definition (see Hard-Coded Values below).
Configuration
Values Populate by Table
- From Table - table(s) from which to pull data.
- From Value Column - value column that ultimately will be stored in the foreign key column.
- From Text Column - text column to display to user.
- Value Where - where clause.
- Value Where for Add - where clause for adding only; if left empty, uses above.
- Value Order By - sort order, typically same as From Text Column.
Hard-Coded Values
Go to the Admin module, click on Columns, then find the column for which you would like to edit the values. Enter a list of discrete values in the Value Where textarea, separated by semicolons. Spaces are permitted. They will appear in the same order as entered. Example:
North;South;East;West
or:
Bike;Bus;Car;Helicopter;Inline Skates;Segway;Skateboard;Subway
Note: If you need to change these values, for example to remove an existing value or change an existing value, but there are already records that have this value, then you'll need to run a mini data conversion to change the old value to the new value. Also, if you need to update a baseline column, be sure to lock the column definition so it doesn't get overwritten by an upgrade to a new build. See Update Hard-Coded DropDownList Values for a case study.
Value/Text Variation in Hard-Coded DropDownList
It is possible to have a display value for a hard-coded DropDownList, for example Y:Yes;N:No, where Yes and No are displayed, but the value Y or N is stored.
Note that there can be drawbacks with this approach. If you need to display this data in a report, for example, it will actually have "Y" or "N". This might or might not be acceptable. For example, if you had A:Accepted;F:Failed;P:Pending:W:Waiver as a value in a char(1) column, this will work fine in the front end of NetForum, but any reports must "translate" the A, F, P and W code into the "real" code. Therefore, use this approach when appropriate but understand the implications.
Best Practices
Foreign Key Column
Assuming the dropdownlist is populated by a lookup table, should the foreign key column that stores the primary value in the lookup table point to the Primary Key or a uniqueCode value?
If in doubt, always choose the Key. This is better data modeling, even if you make the Code column be a unique constraint column.
In baseline NetForum, you will see examples of each. For example, we store the code for Prefix or Suffix on Individual (this was considered to be overkill). We store the relationship code and not key in the co_individual_x_organization table. But for addresses, we store the co_customer_x_address.cxa_adt_key address type as a key and not a code. Despite the variations in these examples, when developing your own tables, we always encourage the use of guid foreign key columns for a more precise data model.
Populate by Table or Hard-Coded Definition
Here are some issues to consider when you want to have a dropdownlist, and whether to work with an underlying table, or hard-code the values in the Column definition in the sql where.
When to populate a DropDownList with hard-coded values:
- Small number of fixed, non-changing (or very rarely changing) values. Example: gender (ind_gender) (Male;Female) or North;South;East;West, or Yes;No;Undecided.
- Codes dictate certain application programming logic that depend on hard-coded "magic" codes that users must not change or add to. Example: CR;DR for credit and debit -- the program will simply work if you add another one
When to populate a DropDownList with a Table:
- Need a Table to define additional attributes (i.e. additional columns) for each code. Example: Relationship Type is not just a code, there are also additional checkboxes that define eWeb access and rights, and membership flow down.
- New codes are added frequently and users need to be able to add new codes.
- Dictate data types and implement a relational database model
In general, if you are unsure which to use, go with a Table. See also Toolkit Standards for a discussion on this topic.
Advantages of Table:
- More scalable
- Better data integrity
Advantages of Hard-Coded dropdownlist:
- Faster and easier to setup
- Do not need to add an extra table to the database
- Simpler reporting and querying without the need to join to a foreign table
Compound Value Text
If you want to have a compound Value Text Column that is composed of two more more columns, or of any special formatting, then do it this way to avoid SQL syntax errors in the NetForum engine:
The display text column uses an alias like this:
gla_code = gla_code +' - '+ gla_description
or
combo = gla_code +' - '+ gla_description
The key point is that you must alias the combined fields with a single alias for the dropdownlist to render. This will fail:
gla_code +' - '+ gla_description
but this will work
code = gla_code +' - '+ gla_description
and this will work:
z = gla_code +' - '+ gla_description
It does not matter what you call the alias, as long as you use it. In most cases, you can then enter the name of the alias in the order by as well.
Parsed Values in Value Where
You can include parsed values in the value where clause. The assumption is that you are parsing something else on the same form.Here is an example of setting a parsed value for the State column co_address.adr_state. If you look at address forms, you will see how this works. When the user chooses a country, the country form control will have a postback and when the form posts back, the states will be populated according to the country that the user chose.
You can do this at the Column level and the Form Control and Search Control levels.
Note that if you do this at the Column level, then this will only work in cases where the "parent" parsed value control will also be present.
Run this query in SQL and you will see many examples:
SELECT mdc_value_where,*FROM md_column (nolock)WHERE mdc_value_where LIKE'%{%}%'
Direct SQL
You can also type sql in the From Table and type a complete SQL statement into the Value Where. The SQL statement can be a Stored Procedure or ordinary T-SQL. You can parse values. The SQL statement you type here will create a recordset to populate the DropDownList, with columns from which you can set From Value Column and From Value Text.
The Value Column and Display Text Column entries must be returned in the recordset that will be produced by the SQL in the Value Where.
This feature is used when the recordset you want to return is too complicated for the conventional DropDownList setup. For example, you could use a stored procedure or type a more complicated statement with multiple joins, etc.
If you use the sql override, then the order by field is not used. Be sure to have an ORDER BY clause in your SQL to sort the records.
DropDownListAdd
Select this Control Class to place an add icon to the right of a DropDownList. Clicking the icon will launch a new modal window allowing the user to add a new value on the fly. Once the new record is added, the modal window will close, and the newly entered value will be selected in the DropDownList when the focus returns to the original form. This is useful for smaller lookup tables, or for allowing a user to add a new value without having to back out of the record they are working on.
In the URL/Image Path/Data textarea of the control, the FormKey must be entered in the following syntax to instruct NetForum which form to open:
FormKey=0d027971-afb3-4300-83e1-76b07bf46e30
If the user is to add a new record that is linked to a particular record, such as a new address for a customer, then you must also supply additional values. For example, this is a link that will add a new Customer Address record for a Sponsor. The cxa_cst_key is defaulted to the value of the Customer in the parsed value {spo_cst_key} and the customer’s sort name is defaulted in the parsed value {cst_sort_name_dn}:
FormKey=E53A8841-1D85-4C4A-B68B-80568FA7191D&cxa_cst_key={spo_cst_key}&cst_sort_name_dn={cst_sort_name_dn}DropDownListAddEdit
Same as #DropDownListAdd above, only this also adds an edit link. No change in configuration other than the control class type itself. Requires a FormKey parameter in the URL/Image Path/Data, just as DropDownListAdd does.
DropDownListAddEditQuery
This is a very specialized kind of DropDownList used to select a Query. With this control, a user can select an existing query, create a new query on the fly, or edit an existing query on the fly.
When you are adding a new query or editing an existing query, when you are finished with the query, press the Run Query button to save the query and return back to the originating page. After doing this, the newly added query will be selected.
As a best practice, you might prefer to create and test your query first, and then come back to the setup area and choose the query you have developed. It is not possible to view the query results from a DropDownListAddEditQuery, which means you cannot tell for sure if you developed your query in the way you intend it to work.
DropDownListAddEditQuery Toolkit Setup
Set the auto postback to checked so that if the user changes the selection, the edit link will be refreshed with the value of newly selected querykey
In the URL/Image Path/Data you need to set the FormKey and the value of the QueryKey control in this format:
formkey={zzz_dyn_key}&QueryKey={xxx_que_key}
In the example above, zzz_dyn_key must be the value of a FormKey that describes where the Queries exist. For example, if you wanted the user to be able to select a Query from the Individual object, then you'd need to have a FormKey on an Individual Form. In some cases, you can hard-code the FormKey, but if this can be variable then you'll need something more dynamic. If you do not have a field available for parsing, then you might need an object with a virtual field. See Facade with Virtual Field sample.
The xxx_que_key example above is simply the name of the DropDownListAddEditQuery control.
DropDownListAddEditQuery Toolkit Setup, Advanced Settings
On the advanced tab, set the following values needed to populate the dropdownlist with the correct queries:
- Value From: sql
- Value Column: que_key
- Value Text Column: que_description
- Value Where: enter the following template and change settings from the generic zzz sample values:
exec fw_group_query_security @act_obj_key ={zzz_obj_key},@user_name ={currentusername},@exclude_group_queries=1,@include_ask_at_runtime=0,@que_key_current ={zzz_que_key}
Each parameter of the fw_group_query_security stored procedure is explained below:
- @act_obj_key (string). The key of the Object associated with the Query.
- @user_name (string). Enter {currentusername}. This is used for query security.
- @exclude_group_queries (bit). Enter 1 if you want to exclude group by queries, which you probably do.
- @include_ask_at_runtime (bit). Enter 0 to exclude queries with ask-at-runtime parameters.
- @que_key_current (string). Pass the parsed value control of the query's control. This will ensure that even if the current user does not have rights to run the currently selected query (if any), that query will still be in the dropdownlist to ensure that the user cannot accidentally save the record and inadvertently blank out the query.
The baseline SP fw_group_query_security will return a list of valid queries. If you need different logic, then you can use that SP as a starting point and write your own SP or T-SQL.
Troubleshooting
Q. I am trying to show a column of data type av_text in a dropdownlist display text column, but I get an error: Operand type clash: uniqueidentifier is incompatible with ntext.
A. The value of the "display text column" must have a data type (or base data type) of varchar or nvarchar or char. You must convert your "av_text" to a nvarchar. Enter this in the "display text column":
code = convert(nvarchar(4000), x99_text_column)
At any rate, it is usually not a good idea to try to put a "av_text" column into a DropDownList because users can enter large amounts of data there and it would not look good in a DropDownList.
Customization - DropDownList with separate add button
Scenario
We have a client who had publication products in multiple languages. We customized the product table by adding a language code field that was related to a language table. When editing the publication detail the client wanted to be able to select from a list of language, but also add new languages. The baseline DropDownListAdd control was used initially, but the control is only designed to work with relationships based on record keys. Since the product table is storing the language code the control wasn't able to properly select the new language in the drop down list forcing the client to do it manually.
The language table contains both a "code" and "language" field. In this case the drop down list was using "language" field as both the value and text entry for the drop down list. Make sure the value that the javascript passes back is the value field for the drop down list.
Solution
To solve the problem we used a DropDownList control and added a custom Add Language link that opened a wizard in a popup window. The wizard used one form to insert the new language record and used a second form to call javascript that would populate a placeholder record in the drop down list and select the new record.
url[javascript:void(0)]
onclick[OpenNewWindow('Wizard.aspx?Modal=Yes&WizardKey=a103eb64-6018-4406-95f3-9c07ab9bc554&Action=add');]
img[~/images/add_16.gif]
Wizard
Wizard Save button
Set Product Language Code Wizard Form - HTML Content
<TABLE><TBODY><SCRIPT language=JavaScript>this.params={};var qs= location.search.substring(1, location.search.length);qs= qs.replace(/\+/g,' ');var args = qs.split('&');for(var i =0; i < args.length; i++){var pair = args[i].split('=');varname= decodeURIComponent(pair[0]);var value =''if(pair.length==2) value = decodeURIComponent(pair[1]);this.params[name]= value; }var prd_g03_code_ext = window.opener.document.forms[0].prd_g03_code_ext;window.opener.DropDownAddOption(prd_g03_code_ext,this.params['g03_language'],"new item reloading..."); prd_g03_code_ext.options.selectedIndex= prd_g03_code_ext.options.length-1; window.opener.__doPostBack('prd_g03_code_ext',''); window.close();</SCRIPT></TBODY></TABLE>