Validated fields – friend or foe?

Whenever you require a field on a form that has a definitive list of values to choose from, a Service Desk Express administrator is always faced with either creating a dedicated module and foreign key reference or a validated field.

So what is a validated field?

A validated field is a text field (or more accurately in database speak – a varchar field) of a size able to accommodate the largest option. The options, added through either the DB administration tool or the customisation wizard, are enforced using a check constraint on the column created. As such, even if you try to insert the data direct into the table using, for example SQL Enterprise Manager, you cannot insert a value other than those allowed.

Accessing the values available of a validated field?

There are times when a developer or dba might want to programmatically determine what the options are of a given validated field. This is remarkably easy once you know what table to look in for the information:

SELECT BVD.[SMTYPE] FROM dbo.SMSYSBASEVIEWDATA BVD JOIN dbo.SMSYSTABLES T ON T.[TBLSEQ] = BVD.[TBLSEQ] WHERE T.[SYSTBLNAME] = '_CONFIG_' AND BVD.[COLNAME] = 'NETWORK'

The query above, for example, will return all the options of the network field from the _SMDBA_._CONFIG_ table. You simply replace the tablename and the column name to get your options.

When to use a validated field vs. a dedicated module and foreign key relationship?

To answer the above question you need to understand some of the advantages and disadvantages of using validated field and dedicated modules:

• Performance
  • Validated fields are significantly more efficient at reading data than foreign key relationships as a join is required to retrieve the information from the appropriate table in the case of the foreign key. Whilst the effect of this can normally be reduced using an INNER JOIN if the foreign key column is mandatory, Service Desk Express does not make use of these efficient joins in its views and consequently performance is affected.
• Re-Use
  • Validated fields cannot be re-used as they are table specific. Consequently if, for example, you have a list of locations a validated field would probably be a bad choice as you may want to use this list in a variety of modules e.g. Incidents and Work Orders. In this example, a dedicated module with foreign keys from both the Incidents and Work Order modules would be a much better bet as you would only need to update the list of locations once and both foreign keys would be reading the new data.
• Number of options
  • If you have a lot of options then a validated field is almost certainly a bad choice. Particularly with advances in Service Desk Express that allow you to show foreign key fields as drop down lists (emulating validated fields) using form customisation, validated fields are usually only sensible with less than 10 records.
  • Validated fields provide no way for the end-user to sort the results (only what the system administrator has decided the order should be)
  • Validated fields provide no way for the end-user to search for the value other than scrolling through the list.
• Setting Up
  • Without a shadow of a doubt validated fields are much each to create and use QUICKLY than a dedicated module and foreign keys. When you create a dedicated module you need to
    • Create the form to maintain the records in the dedicated module
    • Modify the popup to allow retrieval of the records
    • Add the form to the appropriate navigator bars
    • Update any forms that use this module
• Simplicity
  • Not that this should be a reason per se but, validated fields are easier for novice Service Desk Administrators to get their heads around and make use of.
• Maintenance
  • Whilst maintaining the list of options in a validated field has become considerably easier using Service Desk Express, if your options are likely to be changing frequently a dedicated module is a much easier solution to maintain.

Summary 

So, in answer to the above question of which should you use:

• Use a validated field if you have less than 10 options which are unlikely to change and will only ever be used in one module. Validated fields also offer a significant performance boost where the number of records in the table is very large.
• Use a dedicated module if you have more than 10 options, or the options are likely to change, or the options will be needed in more than one module.

 As always, thanks for reading and I welcome any comments.

• database customisation
• Service Desk Express
• validated fields