How to Add a Validated Field

Note: You can also access the Archibus Field table from the System / Add-in Manager / Edit Data Dictionary task, available in Web Central or Smart Client.

In Smart Client, select System / Add-in Manager / Edit Field Dictionary to access the Archibus Fields table.
Click in the top row to create a new blank record.
Specify the table to which this field belongs by choosing an existing table for the Table Name field.
For Field Name, enter a value that will uniquely identify this record.
Complete the Validating Table field with the table that matches the value of Field Name.

For example, the Floors table has bl_id, which validates on the Buildings table. Accordingly, in the Floors table you must complete the Validating Table value with "bl" for this field.
It is important that in both the Buildings table and in the Floors table that the rows for bl_id have matching attributes. (The exception is that in the Floors table, the value for Validating Table will be "bl" and in the Buildings table this value will be blank.)

Complete the Fields to Validate (dep_cols) field.
- This value indicates which field in the original table is being validated.
- This field specifies the foreign key fields within the table you are currently defining into a single part or multipart key. It is this key that will be validated against the primary key of the validating table.
- You only have to enter the Fields to Validate (dep_cols) value if the name of the field that is being validated does not match the field name of the primary key field of the validation table.
- If omitted, the program uses the fields in the current table whose field names match the field names of the primary key fields of the validating table.
Tip: A common error is to misunderstand the use of this field. Note that this field is not titled "Field to validate against;" it is "Fields to Validate." That is to say, in the Fields to Validate field, enter the fields that will be validated against the validating table.
Define the remaining properties of your field by entering the appropriate values for the fields defining this field's attributes. For information on setting the specific properties of your new field, see Archibus Fields table (reference).
Run the Schema Change Wizard. For details, see General Procedure for Changing the Structure of the Database.

Examples of Completing the "Fields to Validate" (dep_col) Field

State field in the Vendors table

Consider the example of the State field in the Vendors table (vn.state). For the Fields to Validate field, the value is "state"; it is not "state_id". The Fields to Validate (dep_cols) value, if NULL, defaults to the pkey field of the validating table; thus state.state_id. You only need to enter the value if the name of the field that is being validated does not match the field name of the pkey field of the validation table.

If dep_cols IS NULL, then the assumption is state.state_id = vn.state_id

If dep_cols is "state", then the assumption is state.state_id = vn.state

The software always knows the names of the pkey fields of the validating table since it gets those from the afm_flds for the validating table.

Data sources use the dep_col field to join tables. The correct join between vn and state must look like this:

SELECT ... FROM vn LEFT OUTER JOIN state ON vn.state = state.state_id

The value of Fields to Validate, arguably, could default to the field being validated, however, if the validation table has a multi-part key then the rule is more involved and thus would require a value in Fields to Validate. The below example shows a multi-part key.

"Move to Room" field in the Move Orders table

mo.to_rm_id --- The validating table is "rm"; The value for Fields to Validate (dep_cols) is: "to_bl_id;to_fl_id;to_rm_id".

Why? Because the software knows that the Pkeys of the rm table are "bl_id; fl_id; rm_id". However, the software has no way of knowing which fields in the Move Orders table need to be used in the look-up on the rm table. With the above value for Fields to Validate, the software knows to map:

rm.bl_id = mo.to_bl_id

rm.fl_id = mo.to_fl_id

rm.rm_id = mo.to_rm_id