Automatic ID Lookup

Some sites need to display both numeric identifiers and human-friendly names for employees, organizations, room categories and types, buildings, and other data.  Most existing Web Central views include only the primary or foreign key field, which is where the numeric ID is stored.  For example, views showing rooms display Building Code and Department Code, but do not display Building Name and Department Name.

The report below illustrates the general issue.  Imagine that the Room Category, Room Type, Division Code, Department Code, and Employee Name fields were all just numeric values; the form could not be used off-the-shelf.  You would need to add the name fields to the view manually, for example, dp.name , rmcat.description , and so on.

em_table_ex.png

You may need to change forms, reports, and other views to meet this requirement, and you may need to re-implement the changes with every release. The Automatic ID Lookup feature obviates the need for this customization because it automatically displays in views and controls a designated human-readable name or title field alongside -- or instead of -- the numeric code field. Archibus views and reports can be configured to work using numeric primary keys.

Automatic ID Lookup:

  • is available for stock Archibus applications, as well as customized applications
  • is provided by the Archibus application development framework that uses configurable metadata to automatically display and update appropriate database fields
  • works in conjunction with translatable fields, such that if the name or title field is translatable, the correct language field is displayed.
  • can be turned on and off globally
  • uses the lookup tables you designate

Examples of using automatic ID lookup

You may want to use the Automatic ID Lookup feature in these cases:

Issue Description
Numeric IDs Sites have their own numeric classification schemes that they use enterprise-wide, and they need to conform to these standards.  Some sites use building numbers in bl_id and use more recognizable names in bl.name ; as another example, sites use FICM standard numbers for rm_cat and rm_type, with human readable names in description field (100=Offices). Other sites always use alphanumeric codes for ID fields. The ability to show both the code and a human-readable name speeds work and enhances the clarity of reports.
Large Tables Sites have very large Employees, Buildings, and Departments tables, and these sites worry about ID duplication if they use descriptive IDs
Real Property IDs Some sites use a Web Service for assigning and looking up Real Property Unique Identifiers enterprise-wide.  This number is unique, but not meaningful.  As such, sites want to associate a meaningful name to the generated ID.
Employee Names One of the common use cases is Employee Code ( em_id ). Mid-sized and large-sized companies will almost always use a numeric code for all employee records. Names change often due to  marriage, divorce, and data entry errors. Variations (like Nick, Nicholas, Nicholas K,) all lead to duplicate and erroneous data. Multiple employees with the same name (Lee, Gina) are common. Numeric IDs are stored in em_id , but users will want to see and work with the employee name (ABERNATHY, ALLISON) stored in a new em.name field.  In some use cases, you will want to see both names and numbers (ABERNATHY, ALLISON – 121242)
Localizing Standard Codes Multi-nationals that have users working in multiple languages have IDs for organizational levels or for cost categories as numeric identifiers.  Alongside the numeric identifier, they want the program to look up and display the human-readable name or title.  They want this name or title to be localized for the current user. For instance, a multi-national company may have codes for Problem Types (ELEC01) but then wish to show a more human-readable look-up name. This would appear in English for users in the UK (Electrical, Type 1), but in Spanish for users in Spain (Electrica, Typo 1). Automatic ID Lookup resolves a common localization issue: standards codes such as Room Standards, Employee Standards, Business Units, Divisions, Departments, etc. are not limited to displaying in one common language (usually English) for all users

Terminology

The documentation for Automatic ID Lookup uses these terms:

Term Description
ID field A field used as a primary key in a validating table, such as dp.dp_id or bl.bl_id , or as a foreign key to the validating table, such as rm.dp_id or rm.bl_id . Instead of displaying these fields in forms and reports to identify the validated value, you can display the lookup field instead.
lookup field A text field in a validating table that can be displayed to identify the validated ID value, such as dp.name or bl.name , instead of the ID field, such as dp.dp_id or bl.bl_id.
lookup value The values for lookup fields.
ID lookup A process for looking up and displaying the lookup field values for the corresponding ID field value.
translatable field A text field that contains text in the customer’s preferred language, such as programtype.description . Forms and reports display this field if the user's locale is English OR if the Translatable field corresponding to the user’s locale is empty. Lookup fields are often translatable fields.
translated field A text field that contains translated text for a specific language, such as, programtype.description_es . Forms and reports display this field if the user's locale matches the language AND the field is not empty.

Usage Notes

  • Automatic ID Lookup. Sample department data . Those wishing to convert the sample HQ department names to numeric codes to illustrate the use of this feature should use the Basic Rule Wizard to execute this file: \schema\ab-products\common\resources\basic-rules\BasicRules_GenerateIds.java
  • Automatic ID Lookup . Pkey fields . If a System Administrator enables Automatic ID Lookup (LookupID), then they should remove any primary key (Pkey) fields from the <translatable> section of afm_scmpref.preferences . Lookups do not work properly for translatable primary key fields. (APP-1500)

  • Charts . Some views with complex lookups will require custom code to make Automatic Lookups work in those specific views. For example, you may have a chart query such as:

    SELECT
    dv.name AS dv_id,
    count(rm.rm_id) AS total_count
    FROM rm
    LEFT OUTER JOIN dv ON rm.dv_id=dv.dv_id
    GROUP BY dv.name

    With this setting, dv_id is replaced with name, however, the view also needs dv_id for the onClick event. By default, automatic lookup adds the lookup field to the query, but it will not work with a grouping query. To resolve a situation such as this, concatenate dv_id with name and add custom code to the onclick event to extract dv_id from the selected data. (AD-10780)

See Also

Configuring Automatic ID Lookup

Using Automatic ID Lookup in Data Sources

Using Automatic ID Lookup in Views

Using Automatic ID Lookup in Custom Code

Using Automatic ID Lookup for Translatable Fields

Upgrading Existing Applications to Support Automatic ID Lookup

Manage Automatic Lookups task