MicroStation CONNECT Edition Help

Expression to Derive Data from Lookup Tables

Lookup tables are lists of data that are associated with a field in your project database, usually in excel format. You can use an expression input in the Item Types dialog to derive data from lookup tables into the item type property definition.

Note: Supported Excel file formats are .xlsx (Excel Workbook 2007 or later) and .xlsm (Excel Macro Enabled Workbook 2007 or later).

Use the following expression syntax for associating a lookup table with an item type property definition:

LookUp.GetEntry("Table Name", "Key Column Name", this.Key Column Value).Property Name

Following is the syntax break down:
  • LookUp.GetEntry - Get entry of data from the excel lookup table pointed by the configuration variable "ITEMTYPE_LOOKUP".
  • Table Name - Name of the sheet within the Excel file from which data will be extracted.
  • Key Column Name - Name of Column to search for in the Excel sheet.
  • Key Column Value - Value to fetch from the key column in the excel sheet. This parameter extracts data from the entire row representing the column value.
  • Property Name - It is the property value that will be extracted with respect to the value in the key column.

When an item’s property definition has a lookup table associated via an expression, the lookup data will display in the element’s properties in the Properties dialog.

Advantages of using lookups are as follows:

  • Standardized data input
  • Reduced chances of error while data entry
  • Faster data entry

Let us look at an example to understand this.

Consider the following information in an Excel file:

100 Honda Red 2010 1HLQU1520KA186392
101 Ford Black 2011 2FMPK4J992UC29655
102 Toyota Blue 2012 4T1BE32K35U614028
103 Nissan Steel Grey 2013 JN1AR5EF30M005868
104 Mercedes White 2014 WDBCA35EOKA488250
105 Renault Beige 2015 WP0ZZZ96ZNS402013
To associate the data in the above table with an Item's property definition, do the following:
  1. In the DGN file open the Configuration Variables dialog (File > Settings > Configuration Variables) and create a new configuration variable "ITEMTYPE_LOOKUP".
  2. Point the Excel file with the above data to this newly created configuration variable.
  3. Open the Item Types dialog.
  4. Select the desired Item Type. For example, let us consider an Item Type named Cars with Property Definitions - NUMBER, MANUFACTURER, COLOR, MODEL and VIN.
  5. Select the property definition MANUFACTURER and type in the following expressions in the Expression field of Calculated Property section:

  6. Select the desired element and attach the item Cars to it.
  7. In the Attach Item tool settings window, you will see a drop-down menu in the NUMBER field which displays the values in the NUMBER column of the lookup table.
  8. Select the desired number. Say, 102.
    You will see that the values for other fields corresponding to 102 in the property definitions are populated from the look up table, as shown below:

  9. Right-click on the element and select Properties to open the Properties dialog.
    Here you will see the look up information as shown below: