Step 3 Referential Integrity Rule in iiiEmployees File

Step 3. Referential Integrity Rule in iiiEmployees File

REP007 - File Validation Rules/Triggers

In this step you will add a Lookup rule to the Employee file. This rule specifies that the Department field which is being inserted into the Employee file must exist in the Department file.

1.  Open file iiiEmployees in the Editor. 

2.  On the Home ribbon, expand the Views menu, to select the Simple rules list.

The simple rules list gives you an overview of all the rules in the file and indicates their type and whether a rule is defined in the field or file definition.

3.  Select the iiiDeptCode field in the list of rules.

4.  Select the Designer button on the ribbon and expand the Add menu to select Add Lookup Check to add the rule to the iiiDeptCode field.

5.  In the Details tab, create the rule as follows:

Description Dept must exist in iiiDepartments Sequence 1 File name iiiDepartments When Inserting Always apply rule (ADD) When Updating Apply when field is used (CHGUSE) When Deleting Never apply rule

     Your Details tab should now look like this:

6.  Complete the Actions section, to define the actions to set the field in error if the value is not found:

If a "key match" is found in target file Evaluate next rule (NEXT) If a "key match" is NOT found in target file Set field in error (ERROR) Message file (delete the entry in this field) Message Number (delete the entry in this field) Message text Department must exist in Department File

     Your Details tab should now look like this:

7.  Save the file.