An Integrity Rule consists of two main components – WHEN rules (selection conditions) and TEST rules (rules for validating data). The WHEN portion of the rule is used to identify which records the TEST portion can be applied to. This means that different rules can be set up for data in different conditions, for example one rule for Invoices and another for Credit Notes. If there are no WHEN conditions, the rule will apply to all records.
The TEST portion of a Data Rule contains the data validation specification. This can be created in one of two basic forms:-
- Static Rule. Data field compared to static value(s).
- Cross File Rule. Data field compared to derived values from a field in another (linked) file/record.
An Integrity Rule may combine both techniques within the same rule.
Static Rules
The operation codes available for a static value rule are:-
DATE – Valid Date
EQ – Equal To
GE – Greater Than or Equal To
GT – Greater Than
LE – Less Than or Equal To
LIST – List Check, value must be one of those listed
LT – Less Than
NE – Not Equal To
NLIST – Not In List
NRANGE – Not in Range Check
RANGE – Range Check
TIME – Valid Time
Examples:
Possible values for the field ‘STATUS are 0,1,2 and 3.
STATUS RANGE – 0 3
REGION is one of four possible values, NE, SE, NW, and SW.
REGION LIST – NE SE NW SW
VDATE (eg 2004/06/26) a valid date between 01Jan2004 and 31Dec 2005
VDATE DATE – LMD
VDATE RANGE – 20040101 20041231
The customer number must be present.
CUST NE – 0
Cross File Rules
Cross file rules are used to validate data in one file against data in another file. This is achieved by linking the first file to the linked file and by using one of the following operation codes:-
- COUNT Count the number of records
- SUM Summarise the value
- MAX Maximum Value
- MIN Minimum Value
- AVG Average Value
- EXISTS Existence Check
Examples:
The total price in the header should equal the sum of the invoice line prices in the file INVLIN.
TOTP SUM (invlin.lval), Linked by INV#
Every invoice header should have at least one line.
INV# EXISTS (invlin.inv#)
(no link is required, the link is implied in the above using the same field name)
Similarly, the customer number must exist in the customer master file.
CUST EXISTS (custmast.cust#)
The last line number in the invoice file should match the line count in the header file.
LINE MAX (invlin.lin#), Linked by INV#
The last line number count should be the total of the number of invoice lines.
LINE COUNT, linked by INV#
Using a Cross File Rule
Cross file rules are set up in the normal rule set up screens but use special operation codes. These are:-
- SUM. The field in the main file represents the sum of the values of a number of other records. This can only be used for numeric fields.
- COUNT. The field in main file is a number which matches the count of a number of records. Numeric fields only.
- MAX. The field in the main file is equal to the maximum value found from a number of other records. Numeric fields only.
- MIN. The field in the main file is equal to the minimum value found from a number of other records. Numeric fields only.
- AVG. The field value in the main file is equal to the average value found from a number of other records. Numeric fields only.
- EXISTS. The field value is equal to the value in another record, or an instance thereof can be found in a number of other records.
In all the above cases, the field must be linked to another file and field. This can be the same file as the main file if required.
This is achieved in two parts.
1. Key in the field on the current file and the operation code as shown below, then press F17.
2. Fill in the rule definition on the following screen.
Library The library in which the linked file can be found. If the library name is *LIBL, the library list will be used both during rule set up and at the time of the integrity run. Bear in mind these may be different depending on your current library list values and the settings against the integrity case.
File The name of the linked file, which can be the same as the file defined in the main part of the rule.
Member The name of the member to which the rule applies in the linked file.
Field The field which is the subject of the operation code, i.e. which will be summed, averaged, checked for existence etc.
This File/Based On The rule is executed by linking or joining the record in the main file to the related records in the linked file. This requires a field or a number of fields in both files to have matching data values which can be used to perform this ‘join’. Fields of different names, types and lengths can be linked for the purposes of achieving the join between the two files. Press F4 to select from a list of fields on the files.
Once Enter is pressed on the above screen, the rule definition is formatted and added into the Values column as shown below.
If there are no records retrieved for a link (null return), this will be treated as a failure in most cases except for the following specific example
COUNT = 0
COUNT <= X
COUNT < X
In which case a null return will be considered as a rule pass.
