Creating a Hierarchical Validation Table
- Create a validating table (e.g. Cost Center) that holds the hierarchical records. This table must have a primary key that consists of one and only one field.
- Categorize the primary key of this table as having A/FM Type “Hierarchy” or “Hierarchy-Concat”. “Hierarchy–Concat” is used for concatenated primary keys as these require the primary key to change when the hierarchy changes; all others use “Hierarchy” as the primary key is only changed by the user and is not dependent on the hierarchical relationships.
- Add a field “hierarchy_ids” to the table. Set the A/FM Type of this field to “Hierarchy Trace”.
- Add a validating field (e.g. rm.cost_ctr) to any inventory table to be categorized. This does not have a hierarchical A/FM Type -- but does know it validates on a table with a primary key of the hiearchical type. Thus, the A/FM core determines whether to display the Select Values dialog or the Select Hierarchical Values dialog based on the A/FM type of the primary key of the validating table; not of the inventory table.
Hierarchical Validating Tables
The validating table has:
- A primary key (cost_ctr.cost_id) of A/FM Type ‘Hierarchy’ or ‘Hierarchy-Concat’.
- A description (cost_ctr.desc) of A/FM type ‘Desc’
- A hierarchy_ids field of type varchar; typically of size 500, and of A/FM Type ‘Hierarchy Trace’.
A typical validating table would be a CSI Uniformat table:
| Primary Key | Hierarchy Ids | Description |
|---|---|---|
| A | A| | Substructure |
| A10 | A|A10| | Foundations |
| A1010 |
A|A10|A1010| |
Standard Foundations |
| A1020 | A|A10|A1020| | Special Foundations |
| A1030 | A|A10|A1030| | Slab on Grade |
| A20 | A|A20| | Basement Construction |
| A2010 | A|A20|A2010| | Basement Excavation |
| A2020 | A|A20|A2020| | Basement Walls |
| B | B| | Shell |
| B10 | B|B10| | Superstructure |
| B20 | B|B20| | Exterior Enclosure |
Hierarchy Format
The characteristics of the hierarchy are as follows:
- Maximum Number of Levels – This is limited only by the size of the hierarchy_ids field as the hierarchy_ids field records the hierarchy relationships for each record. (If there are coding reasons where limiting the maximum number of keys would be helpful then we could add a schema preference for this maximum and set the default to 10 or 20.)
- Updating, Adding, and Deleting – This is controlled by Security, as Select Values does. For more information see the Hierarchical Tree Control description in the API section of this document.
- Hierarchical values delimiter – This value is set in the Schema Preferences table and applies to all hierarchical tables in a particular project. The delimiter is a single character and should not be the percent sign ‘%’, not be a single or double quote character (‘,”), not be a typical alpha-numeric character, and not be any reserved SQL character such as a comma or parentheses (). The vertical bar ‘|’ is the recommended delimiter and is the default. Other possible characters to use are the hyphen “-“, the underscore “_”, the period “.”, the equal sign “=”, or the less than or greater than signs “<”, “>”. Depending on your data and how your data will be used you may wish to avoid using some of the above characters as separators.
The following topics discuss the hierarchy tree panel: