User Defined Validations

File-> Customisation Configuration -> User Defined Validations

The idea of this feature is to allow you to defined default field validation rules. This can be useful when setting up new records be they Customers, Supplier, Items etc..  These User Defined Validations are run both or either (depending upon the setting selected) when a new record is either 'Inserted' into the database or when an existing record is Updated.

This feature can be used to check for no entry of a field value or check to see if the field value meets a specific condition or value. Unlike Required Fields which only check for a field value, this feature provides far more detailed conditioning. This avoids the need for defining Required Fields, Custom Triggers and Screen Data Scripts in order to force field validation of a blank field value. 

An example of this is if you creating or updating an item and if the item Category was either 'Accessories' or 'Fasteners' then the Analysis Group for that item must not be left blank.

Note:

• When defining a 'From Clause' and 'Conditional Clause' that relate to more than one table, you must prefix each field name with the Table Name 

eg: CustomerMaster.SalesPerson (aliases may be used to abbreviate table names 'OTHER'  than Primary Table Name)

• These rules are run whenever a record is either inserted or updated in the database. This includes records being entered or amended via external sources eg: Data Imports or API

• Ostendo runs User Defined Defaults, Validations and Actions in the following sequence

1. User Defined Defaults
2. User Defined Validations
3. User Defined Actions

Hints:

• When defining a User Defined Validation with 'From and Conditional' Clauses it is suggested you first test your syntax in the Data Spreadsheet (General -> Data Spreadsheet) feature of Ostendo prior to implementing the validation rule.

eg: 

Select ITEMMASTER.ANALYSISGROUP from  ITEMMASTER where ITEMMASTER.ITEMCATEGORY IN ('Accessories','Fasteners')

• When checking for a blank entry (eg: Null or blank) it is suggested you use the "coalesce" function and condition this with two single quotes, then condition based upon the 2 quotes. This caters for fields that may contain a space or a null (uninitialised value). A coalesce is not needed if the field you are conditioning is a Lookup value or a Domain value.

eg: (coalesce(CUSTOMERMASTER.CUSTOMEREMAIL, '')  = '')       (The coalesce guards against the possibility of a 'blank' existing in the email field)

• Most rules will be defined to raise an error either on Update Only or on Insert or Update. However for completeness we have provided additional rules to cover off numerous scenarios when conditions are either met or not met.

• Example of using User Defined Defaults alongside User Defined Validations.
• Suppose you are importing Job Orders from a Web Source (outside of Ostendo) and that web source does not provide for a job category. You could create a Job Category called 'Undefined' and then set a User Defined Default to use this field value when inserting a Job Order of a specific Job Type eg: 'Web Order'.
• Then setup a Validation rule that forces the user to update the Job Category to a another category when the Job Category =  'Undefined' when the Order is Updated later. This will allow the initial creation of the order without stopping the import process if there is no Job Category within the import data.

Fields:

• Table Name: Drop Down and select the Table Name of the Field you wish to define the Validation against
• Field Name: Drop Down and select the Field Name of the Field you wish to define the Validation against
• Validation Style:
• Raise Error if Condition is met on Insert Only
• Raise Error if Condition is not met on Insert Only
• Raise Error if Condition is met on Update Only
• Raise Error if Condition is not met on Update Only
• Raise Error if Condition is met on Insert or Update
• Raise Error if Condition is not met on Insert or Update

• Validation Message: This is the message the user would see when this validation is triggered and the condition is met.
• From Clause: Specify the Table Name(s) to be used in the Conditional Clause  (This can be left blank if only one table name is conditioned and that table name is the same as the TABLENAME selected., however it is good practice to define the table name irrespective)
• Conditional Clause: Specify the conditioning syntax from the 'where' clause in the SQL. 
• Rule Status: These options allow you to control the Status of this rule.
• Restricted to User: (Default) Select this only to allow a specific user to test this Validation before it is released to all Users. (This is used in conjunction with the Test User Option)
• Active For All Users: This Rule is now available to all users in this database and is effectively 'Live'
• Not Active: This Rule is no longer Active, therefore despite it remaining in the Validations, it is never triggered. Use this to temporarily turn off this rule.
• Restricted User: (Must be specified if Rule Status is set to 'Restricted User') Specify a User that this rule will only be Active for. This allows you to test this rule before committing it to the 'Live' environment.

Examples:

Suppose we want to force the user to enter a Primary Supplier when they create a new Item that has a Category of 'Fasteners'

Suppose we want to force the user to enter an an Email Address if  the Email Address field is left blank when creating a new customer

Suppose we want to force the user to enter an an Email Address if  the Email Address field is left blank when creating or updating a customer

Suppose we want to force the user to enter a Primary Supplier when creating or updating an existing item that has a Category of 'Fasteners'