The select_column_db_values model is similar to the select model in that it is used to build a drop-down selection list or datalist. The difference is that this model is used when the list of values to be added to the select are available in an SQL table. In this case, the selection list is created dynamically by querying the values in the specified table.

Consider the following sample specification:

Let's review the properties that can be assigned in this model.

Schema

Enter the schema where the table to be queried resides. If this is the default schema (assigned in site_settings) you can leave this value blank.

Table

Enter the name of the table to be queried.

Internal Column

Enter the name of the table column that should be used to assign the internal selection values. These are the values that will be posted when the form on which the selector is placed is posted to the server.

External Column

This column is optional. It is used if you don't want to show the internal values to the user. Enter the column name that contains the external information to be shown to the user. This column must contain unique values. Translations can also be used as a means to show alternate values to the user, as described below.

Empty Internal Value and Empty External Value

In most cases, the table being queried will not contain a row wherein the Internal Column contains an empty string. Therefore, when the user is first presented with a form containing the generated selector it can happen that the current value is not available within the drop-down. When this occurs, the Browser will show the first item in the drop-down giving the user the impression that this value is already chosen when, in fact, it is not.

To prevent this, it is a good idea to add one additional item to the selector to represent the "empty" value. In most cases the Empty Internal Value will be an empty string. The Empty External Value could be something like "Please Select" or "Nothing Selected". Enter the value '!' in the Empty External Value field if you don't want to generate an empty option.

Translation Id

This is used to support multilingual selection values supplied by a translation model generated class. If a translation class is supplied and if External Column is empty, the Internal Column value (select option) will be used to lookup the text to be shown for each option (based on the user's current language). If the Empty External Value is set, and this starts with the internationalization character (: by default) this will be substituted for the translation. This string can also contain an alternate translation class as in :some_class->some_field.

Namespace

If you are defining a select list that will be used by multiple websites, enter the Namespace to coincide with the pseudo site (inherited site) where the list is defined.

Cache Result

If the list of select values does not change often you should consider caching the select list. When this option is chosen, after building the select list for the first time, the resulting values will be cached in a text file. The next time the list is needed it can be built very quickly from the text file rather than rereading the values out of the database.

When caching results, you are responsible for deleting the cache whenever the original data source is updated. This will trigger the cache to be recreated with the latest values.  Here we show some sample methods that were added to the country object to delete the country cache whenever a country is added or updated.

Note that the code above will only delete the English version of the cache. To delete all versions, you can omit the last parameter of the remove_cache_item call.

Supporting Null Values

Sometimes you may want to allow NULL as one of the values that can be chosen. This will allow the user to indicate that they don't want any value set for a particular field that is populated using a select control. This can be achieved by setting the Empty Internal Value to (NULL) as shown here.