Column Validation
Use the Validation tab of the Column Property Editor to setup basic field validation and more advanced field validation using a SQL query.
Basic
Basic validation parameters allow you to ensure that: values are entered for a field by making it a Required field.
When defining a database table in SQL, a field can be defined as NOT NULL, which means that a value is required. If this setting is used in SQL, it is best practice to define a default value for the field if it is not a primary key field. The relevance of this to the "Value required" setting is that the scope of the setting is limited to the dataview in which it is configured. Therefore, the two cases in with you should use this setting are:
1. If the database allows nulls on the column, but in the dataview you want to force the user to provide a value.
2. If the database does not allow nulls for this column AND you use the setting in conjunction with an intialisation setting for the column. This could be the SQL default or another value, but an initialisation must be specified if NOT NULL is set in the database
Do NOT have "Value required" as true without an initialisation, as Micromine Geobank will validate the row of data BEFORE committing it to the database. Thus the database does not have opportunity to apply the database-level SQL default. Consequently, if the field id is blank, Micromine Geobank reports that a value is required, which will be confusing to users who know that a default is specified in the database.
List of Values
Advanced validation using a query is disabled by default. To set more advanced validation for a field, select the Generate a list of values check box to enable the options:
Select the Cache the lookup codes list option to retain a cached view of the list of lookup codes when you make any changes to the Data View configuration.
The Limit the user's response... option, when selected, will invalidate any user entry not contained in the generated lookup list.
Select the Use Lookup Code table option and select a table from the Lookup Category drop down if you want to use a lookup code table to generate the list. OR;
Select the Use a query option to enter or Build an SQL query to generate the lookup list.
If Use a query is selected, you can select the Lookup result column and Lookup display column from the drop downs provided.
Range Check
Select the Apply a range... check option to specify a range.
You can enter the minimum and maximum range values in the Min and Max fields:
If you select the Use a query... option, you can enter or Build an SQL statement to generate a minimum and maximum range and select the Minimum value column and Maximum value column drop downs:
