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.

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:

NUMBER	MANUFACTURER	COLOR	MODEL YEAR	VIN
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:

In the DGN file open the Configuration Variables dialog (File > Settings > Configuration Variables) and create a new configuration variable "ITEMTYPE_LOOKUP".
Point the Excel file with the above data to this newly created configuration variable.
Open the Item Types dialog.
Select the desired Item Type. For example, let us consider an Item Type named Cars with Property Definitions - NUMBER, MANUFACTURER, COLOR, MODEL and VIN.
Select the property definition MANUFACTURER and type in the following expressions in the Expression field of Calculated Property section:
LookUp.GetEntry("CarInfo","NUMBER",this.NUMBER).MANUFACTURER
Select the desired element and attach the item Cars to it.
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.
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:
Right-click on the element and select Properties to open the Properties dialog.
Here you will see the look up information as shown below: