User Defined Validation Rules

User defined validation rules can be created to extend the capabilities of network validation. User defined rules are created and listed on the User Defined Page of the Validation Editor.

Edit the rule syntax on the Validation Rule Editor Dialog. This dialog is displayed automatically when a new rule is created. It can also be displayed at any time for an existing rule by clicking the Edit button in the right hand column of the grid on the User Defined Page for the rule to be changed.

Joined Tables

Some types of table are automatically joined when a network is opened. This means that SQL can be used to do checks that span the joined tables. There is a one-to-many relationship between:

• the Node (cams_manhole) table and the Manhole Survey (cams_manhole_survey) table.
• the Pipe (cams_pipe) table and the CCTV Survey (cams_cctv_survey) table.

The join goes in the many-to-one direction. For example, the join is from the Manhole Survey to the Node object.

Joins exists because the primary key field(s) in the object at the one end also appear in the object at the many end (the foreign key). If the value of a primary key and the foreign key match, there is a join.

• cams_manhole_survey and cams_manhole both have a field called node_id
• cams_cctv_survey and cams_pipe both have fields called us_node_id, ds_node_id and link_suffix.

When doing an SQL Query or setting up user defined validation rules the joined object can be used in the statement. For example, if the table selected was CCTV Survey (cams_cctv_survey) joined.width would mean the width of the pipe. See the first example below.

SQL Examples

Here are some examples:

Object Type	Rule
CCTV Survey	((finish_depth - start_depth) / total_length)<>((joined.ds_depth_from_cover - joined.us_depth_from_cover) / joined.length)	This rule will check that the gradient in the CCTV Survey matches the gradient in the Pipe.
Pipe	(us_invert <= us_node.cover_level)OR(ds_invert <= ds_node.cover_level)	This rule will check that a pipe's invert levels are below its cover levels.
Pipe	((us_node.system_type = "S")AND NOT(ds_node.system_type = "S")AND NOT(ds_node.system_type = "C"))OR((us_node.system_type = "F")AND NOT(ds_node.system_type = "F")AND NOT(ds_node.system_type = "C"))	This rule will check that the us and ds nodes of a pipe are of compatible system types assuming S=Storm, F=Foul, C=Combined and any other system type combinations would be incompatible.
Pipe	((shape = "C")OR(shape = "S"))AND NOT((width - height) = 0)	This rule will check that width and height are the same for pipes of shape C (=Circle) and S (=Square), assuming all other shapes are non-symmetrical.