Using Descriptive Fields instead of Codes

This week I’m back taking a good look at when best to use descriptive fields instead of code. 


In the Past

Some sites need to display both numeric identifiers and human-friendly names for employees, organisations, 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, department name, room category description, and so on.


















In addition you would need to change forms, reports, and other views to meet this requirement, and you would need to re-implement the changes with every release.  Hence why things had to change….


Now and the Future

The Automatic ID Lookup feature obviates the need for this customisation 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 customised 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


Configuring Automatic ID Lookup

Activating Automatic ID Lookup

The lookup ID feature is disabled by default. You activate it via these methods:


·         Application Parameter

To use numeric ID fields, enable ID lookup by setting the activity parameter for the project: AbSystemAdministration-EnableIdLookup.







If this parameter is not defined in the database, automatic ID lookup is disabled.

System administrators can change this parameter value and use the "Reload All Parameters" action in the System Administration / ARCHIBUS System Administration / ARCHIBUS Administrator - Application Configuration / Configure Application Parameters view to apply the change. There is no need to restart the application server. Users who are signed in will see the change after reloading their views. 


·         Manage Automatic Lookups view

System administrators can also use the System Administration / ARCHIBUS System Administration / Add-In Manager / Manage Automatic Lookups view to enable ID lookup:


ID lookup field metadata   

To automatically add lookup fields and tables to data sources and panels, designate the field in a table that contains lookup values for the ID field.

  • If the ID field is a primary key, the lookup table is the main table.
  • If the ID field is a foreign key, the lookup table is the validating table of the foreign key.
  • The lookup table may only have one lookup field.


For each lookup field, you can specify these preferences:


How to display ID and/or lookup values


How to concatenate ID and lookup values when displayType=concatenate


// displays "02300 - Snow Removal Costs" - this is the default format
displayFormat= "{0} - {1}"

// displays "SMITH, JONAS (3421122)"
displayFormat.em_id= "{1} ({0})" 


Placeholder {0} refers to the ID value, {1} refers to the Lookup value.


How to sort records in reports and grids when displayType=concatenate






idFirst (or not specified)

Sort by ID values, then by Lookup values.


Sort by Lookup values, then by ID values.


These preferences are stored in the afm_flds.attributes field for all lookup fields in most common lookup tables (bl, em, bu, dv, dp, rmcat, etc.). All XML attributes are optional. 

To mark another field as a lookup field, enter the code below in the afm_flds.attributes field:



To mark a field as a lookup field, and specify optional lookup preferences:


   <lookup displayType= "concatenate"  displayFormat= "{1} ({0})"  orderBy= "lookupFirst" />


The database defines lookup preferences for the following fields:  




dp, dv, bu


property, bl, site, city, county, state, ctry, regn


rmtype, rmcat, rmstd, emstd, ac, projecttype





The V.23 database adds these new fields:








Employee Name




Cost Category Description




Cost Class Description



Manage Automatic Lookups

With the System Administration / ARCHIBUS System Administration / Add-In Manager / Manage Automatic Lookups (ab-auto-lookups-manage.axvw) task you can:

activate and deactivate the Numeric ID feature



The Field List has a permanent restriction to show only lookup fields:


afm_tbls: Is SQL View? = No, Domain Name <> Schema, Table Type IN (Project Data, Project Application Data).
afm_tbls: Only tables that are referenced (afm_flds.ref_table) by at least one other table.
afm_flds: Data Type IN (Char, Varchar), ARCHIBUS Type IN (None, Desc)

Enable/Disable Numeric IDs

Toggle the Numeric ID feature on and off with this button. This action sets the application parameter to 0 or 1 and executes the WFR to reload all application parameters. If the parameter does not exist, the action creates it.


Enable Translated Primary Keys

Use this button to configure the Translatable Primary Key solution with a single action. Selecting this button performs three functions:

  • Add lookup attribute with displayType="translate" to all translatable primary keys where afm_flds.attributes is empty.
  • Clear afm_flds.attributes (if contains "lookup") for all fields that are not translatable primary keys in the default standard tables.
  • Enable Numeric IDs if not already enabled.

For the first two functions above, the program lists the affected fields and asks for confirmation prior to making any changes.


Edit Field Attributes

Select a field from the field list on the left, and the right pane displays its attributes for review or editing the Field Attributes field.

The form contains an instruction bar showing a sample of Numeric ID syntax. This is useful for instant reference, as well as for copying and pasting; right click on the text to access the Copy command.

Two action buttons are available from the field list:

  • Add Lookup Field Attributes: Inserts the default lookup XML in afm_flds.attributes for all fields in the grid where afm_flds.attributes is empty.
  • Clear Lookup Field Attributes: Clears afm_flds.attributes for all fields in the grid where afm_flds.attributes contains "lookup".


Upgrading Existing Applications to Support Automatic ID Lookup

Small filter panels

Filter panels are typically designed to be as small as possible vertically, as to maximise the available space for filtered content. When Automatic ID Lookup is enabled, you may not realize that the lookup value is actually displayed below the ID field. The panel will display a scroll bar, but it is not easy to see, and requires additional scrolling action to see the value:   






In such views, increase the layout region height by 15-20 pixels per ID field, so that there is space to display lookup values without scrolling:





Unqualified field names in SQL  

Some views have data sources with SQL restrictions or SQL virtual fields that do not fully qualify field names. For example:


<field name= "bl_pct_gp"  dataType= "number"  decimals= "2" >
    <sql dialect= "generic" >area_gp - area_gp_dp - area_gp_comn</sql>


These unqualified field names may conflict with field that have the same name in the lookup table. To resolve the conflict, add table name qualifiers:


<field name= "bl_pct_gp"  dataType= "number"  decimals= "2" > 
   <sql dialect= "generic" >bl.area_gp - bl.area_gp_dp - bl.area_gp_comn</sql> 


Similarly, if applying SQL parameters or restrictions from client-side JavaScript code (which is not a recommended practice), use fully qualified field names.


Concatenating fields in SQL

Some charts and reports concatenate fields in SQL queries or SQL virtual fields. For example:


<sql dialect= "generic" >RTRIM(dv_id)${sql.concat} '-' ${sql.concat}RTRIM(dp_id)</sql>


These SQL fragments cannot be automatically altered to display lookup fields. To support lookup in such reports, replace literal SQL with the new sql.lookupConcatFields expression:


<sql dialect= "generic" >${sql.lookupConcatFields( 'rm.dv_id' , 'rm.dp_id' )}</sql>


The query will then return ID and/or lookup fields, depending on whether ID lookup is enabled for the project and for specific schema tables.


If the data source does not already contain corresponding ID fields, make sure to add them:

<field name= "dv_id"  hidden= "true" />
<field name= "dp_id"  hidden= "true"



Filter consoles that use virtual ID fields need to add virtual name fields as well, in order for auto-complete to display both ID and name. For example, the Create New Survey form in the Manage Asset and Equipment Survey view allows users to enter Building Code to automatically add survey task for equipment in a specific building. The Survey table does not have the Building Code field, so the form defines a virtual field, and sends its value to a workflow rule that creates survey tasks (ab-eq-survey-new.axvw):


<field table= "survey"  name= "bl_id"  dataType= "text"  size= "8" />


The form also defines a custom Select Value command, since virtual fields do not validate on any table:

<command type= "selectValue"
     fieldNames= "survey.bl_id"
     selectFieldNames= "bl.bl_id"
     visibleFieldNames= "bl.bl_id," >



To support auto-complete on Building Name, add another virtual field to the data source, and modify the Select Value command:


To support auto-complete on Building Name, add another virtual field to the data source, and modify the Select Value command:


<field table= "survey"  name= "bl_name"  dataType= "text"  size= "64" />


<command type= "selectValue"
         fieldNames= "survey.bl_id,survey.bl_name"
         selectFieldNames= "bl.bl_id,"
         visibleFieldNames= "bl.bl_id," >


Plenty of useful detail this week! That brings this week’s technical blog to an end. If you have any further questions, please don't hesitate to contact our Support Team 0118 977 8560 or email us at

Martin Matt







COVID-19 Update: Back to Work 08/07/2020

Following on from COVID-19 effects on business and planning the back to work strategy I have looked back at last years block and thought it would be worth reposting as space occupancy and...
read more view all blog posts