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
- 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:
Let us look at an example to understand this.
Consider the following information in an Excel file:
- In the DGN file open the Configuration Variables dialog ( ) 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:
- 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.
- Right-click on the element and select Properties to open the Properties dialog.