Monday, October 20, 2008

Field and Table Constraints

When tables are added to a data dictionary a host of additional features are available (see the comparison below). Part of these additional properties are constraints which include Minimum, Maximum and Null Valid on fields. You can also specify a table level constraint using an expression.

Free Table Properties

DD_Table_Properties

Minimum and maximum values are very useful when the data must be within a certain range. NULL valid is useful for ensuring that a field contains a value, although this can also be solved by assigning a Default value for the field. Providing a useful Failed Validation Message will be helpful to your users and people trying to create their own tools for your data.

These field level constraints can solve many of your validation situations. However, there are times where you need more control over validating the data. This can be done using a validation expression, which are defined at the table/record level. You can use any valid Advantage Expression Engine function within your validation expression.

The expression can be used to validate one or more fields in the table. For example if we want to ensure that the gender field contains only “M” or “F” we can use the following expression: NOT(Gender = 'M' or Gender='F').

You can also check multiple fields using this syntax. For example to ensure that at least one Phone number is specified you could use the following expression: WorkPhone = NULL AND CellPhone = NULL. These two expressions can also be combined: (NOT(Gender = 'M' or Gender='F')) AND (WorkPhone = NULL AND CellPhone = NULL). You will need to provide a very descriptive error message or do some additional validation at the client side to ensure that the user knows what they did wrong.

1 comment:

Anonymous said...

Nice info. I am currently using free tables with ADS and would like to eventually migrate to a data dictionary. Any additional information like this helps push me in that direction. Thanks.