Validations

On the VALIDATIONS page of the Edit Logging Profile screen you can set validation rules for the tables in your schema.

Validation can be turned on and by toggling the Validation button on the ribbon toolbar. You may prefer to temporarily turn validation off, for example, while importing or adding data in bulk.

 

To insert a new rule after the selected rule in the list (if any) click the Insert New Rule button.

To delete rules, select the rules you want to delete and then click the Delete button. You can also select rows in the pane and then press CTRL+D to delete them. You will be prompted to confirm the deletion.

 

A red background in the data row means that the rule definition is invalid or incomplete:

 

  Calculation errors are show with a warning icon to differentiate them from Validation errors. The tooltip for the icon will show the reason for the calculation result error (such as a 'divide by zero' or overflow error). These warnings do not affect the validity of the row in which they are shown.
 

Equality

The value of the column must be equal to a specified value or the value in a specified column.

  • Checking Not Equal turns this into a “not equal” rule
  • Checking Blank means “is empty/blank/null”

  • Check both where an “Is not blank/empty/null” rule is needed (this is logically equivalent to the “Required” rule and is included in the Equality rule for convenience).

Reasons to use an Equality rule

The equality rule has been designed for these two scenarios:

  1. To test that the value in a calculated column meets a certain criterion. Here are some examples:
    1. The table may have three percentage fields and there is a work procedure rule that the sum of the three must be 100% (column to value comparison);
    2. The table may have a calculated column with an expression which evaluates to a text string such as ‘Pass’ or ‘Fail’; the intent being that if the result is ‘Pass’ then the row is valid. Set a rule that the string ‘Pass’ is valid (column to text value comparison);
    3. The table may have columns for numeric data and a rule is needed to compare a sum to a check sum(column to column comparison)
  1. To be used as part of a composite rule; usually an if-else composite rule; where rule B is applied if equality rule A passes.

For example, to enforce a specific required field for a certain sample type, such as: A Standard_id is required if the sample type is “Standard”.

Max Depth

Check a related table for the maximum allowable depth for the current value. In the example below, the value in the DepthTo column must be less than the value in the TotalDepth column.

Select the Check Minimum Depth check box if you want to use the rule to “check if the minimum depth, which would constitute a ‘complete log, has been reached”. For example, if the geologist is required to log the geology all the way down to the end of the hole, this rule will show a validation marker until the “To Depth” has reached the minimum, this being the end-of-hole depth from the Collar table.

Using this option in conjunction with the standard Max Depth rule, you can ensure that the data is logged to, but not beyond, the end of the hole.

Interval

For Interval data, this option allows you to ensure there are no gaps or overlaps in the interval data. You can also group interval data according to the selected column. In the example shown below, the DepthFrom and DepthTo fields are grouped by Hole ID. Typically, there are no gaps or overlaps permitted between the intervals, and no zero width intervals.

Range

The value of the column must fall within the range specified. In the example shown below, the value of DepthTo must fall between the value in DepthFrom and 100.

Required

The value in the designated field must exist in your data. i.e. null values are not permitted

A Required rule should be used to test the condition where a value is not blank/empty/null.

Unique

The combination of column values, in this example HoleID and DepthFrom, must be unique:

All columns specified as Primary Key columns do not need a separate Unique rule defined. The Unique rule is used when uniqueness outside of the primary key is required.

Composite

When you select Composite as the validation type, the above rules can be combined to form more complex rules, using the logical operators:

  • AND (fails if both rules fail/passes if either pass)
  • OR (fails if either rule fails/passes if both pass)
  • IF - THEN (if rule 1 evaluates to true (passes), then evaluate rule 2).

This allows rules to be set up for a variety of conditions such as different rules for routine and check samples. These are conditions which represent a failure if they are NOT met.

Composite rules can be nested to achieve complex logical conditions.

In the following example; if the sample is a standard, then DepthFrom must be equal to DepthTo:

Composite rule can be nested; (i.e one composite rule can contain another composite rule and so on) to build complex logical conditions.

For example, to set up a rule to check “Grade Control samples, logged in the North and East Pits, must have a date recorded for their HazCheck”:

First set up an AND composite rule with two equality rules to compare values North and East in the column Pit:

Then set up a required rule for HazCheck and use this in an IF-THEN composite rule:

will produce the desired validation in the data grid:

A composite rule must be fully specified before it is included in another composite rule.