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

 

orderBy=idFirst|lookupFirst

 

Value

Description

idFirst (or not specified)

Sort by ID values, then by Lookup values.

lookupFirst

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:

 

<root>
     <lookup/>
</root>


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

 

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

 

The database defines lookup preferences for the following fields:  

 

Tables

Field   

dp, dv, bu

name

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

name

rmtype, rmcat, rmstd, emstd, ac, projecttype

description

org  

org_name  

 

The V.23 database adds these new fields:

 

Table

Field

Heading

Size

em

name

Employee Name

96

cost_cat

description

Cost Category Description

96

cost_class

description

Cost Class Description

96

 

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>
</field>

 

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> 
</field>

 

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"

 

Auto-complete


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,bl.name" >

  <title>Building</title>
</command>

 

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,bl.name"
         visibleFieldNames= "bl.bl_id,bl.name" >
   <title>Building</title>

</command>


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 news@mass-plc.com



Martin Matt



 

 

 

 

 



BACK TO BLOG


CAFM and Climate Change 17/07/2019

We live in an ever-changing world with more and more challenges being faced by us as a species, and one of those challenges- one that arguably has the potential outcome of wiping us out a...
read more view all blog posts

GET IN TOUCH