Lookup tables are a good way to avoid manual data entry and ensure consistency. Item Types now have a lookup tables capability. Combined with storage of Item Types in shared DGNLIBs and the Pick List feature this is very powerful.

The outcome of the steps described below will be an Item Type that allows a user to pick the common name of a tree which will look up and populate the other properties listed:

Sequence:

1. Decide where to locate a) Excel source files, b) DGNLIBs containing Item Types
2. Add configuration variable definitions for ITEMTYPE_LOOKUP
3. Create lookup table(s) in Excel
4. Create Pick List(s) in Excel
5. Create Item Types in DGNLIB, add Expressions and Pick List

Location of Excel and DGNLIB files

While Item Types can be stored locally in the active DGN file, to be of real use they will need to be in a library file, a DGNLIB accessible to multiple projects. This DGNLIB (or multiple DGNLIBs) can be at Organization, WorkSpace or WorkSet level, depending on who they are to be shared with or controlled by.

The lookup tables are contained in one or more Excel files specified by the variable ITEMTYPE_LOOKUP. The Excel file can contain a number of worksheets each containing a separate lookup table. The queries in the Item Types will specify which worksheet to look up. The appropriate location for the Excel file may depend on whether its contents are pre-set by administrators or open for editing by users. It is a probably good idea to locate Excel files alongside the DGNLIBs that use them.

For this example an Item Type will be created that offers a list of native British trees to all users in an organisation. The DGNLIB containing the Item Types and the Excel files containing the lookup table and Pick List will be located in the Custom Configuration at the organization level:

• DEC_ItemTypes.dgnlib contains the Item Types which are available to all users as this location is included by default in the definition of the MS_DGNLIBS configuration variable.
• DEC_ItemTypesLookup.xlsx contains the look up table, this will be used first when the Item Types are created and then every time the related Item Types are attached to elements.
• DEC_Pick Lists.xlsx is used to create Pick Lists but is not active thereafter.

Configuration

To make lookup table(s) available to Item Types the configuration variable ITEMTYPE_LOOKUP must be defined and set to include the path and file names of any Excel files containing lookup tables:

ITEMTYPE_LOOKUP = $(_USTN_ORGANIZATION)Dgnlib/DEC_ItemTypesLookup.xlsx

This should be added to the organization Standards configuration file, in this example: D:\DEC_Configuration\Organization\01_DEC_Standards.cfg.

Create the Lookup table

In this example, a list of British trees, is imported from a web page using Excel (Excel > Data tab > From Web, just specify the URL of a web page containing one or more tables and Excel should present quite well formatted tables to load into WorkSheets, this is a very useful Excel feature).

The incoming data has been tidied up slightly to ensure all entries are consistent. Note that the column headings do not contain spaces, this is important for use with the Named Expressions used in the Item Types:

This was saved as: D:\DEC_Configuration\Organization\Dgnlib\DEC_ItemTypesLookup.xls", copy attached below:

DEC_ItemTypesLookup.zip

Create the Pick List

Creating Pick Lists using the MicroStation UI is covered in the help documentation, in many cases it is more efficient to create Pick Lists using Excel. Here we want to populate the CommonName property with the contents of the CommonName column from the lookup table. We cannot use the same Excel file to generate Pick Lists, the list has to be copied to a separate file.

This file can be created two ways:

• Use the Pick List UI add a new list, CommonName, then add the first value to the list (we cannot paste a list into the values column), use the Export tool to create D:\DEC_Configuration\Organization\Dgnlib\DEC_Pick Lists.xlsx, then copy and paste the complete list of common names into the CommonName column. Use Excel's fill series tool to copy and increment Pick List.Value1 to the bottom of the list.
• Alternatively, create D:\DEC_Configuration\Organization\Dgnlib\DEC_Pick Lists.xlsx, type the two column headings: PickList.Name and CommonNmae, Pick List.Value1. Then copy and paste the complete list of common names into the CommonName column. Use Excel's fill series tool to copy and increment the Pick List.Value1 to the bottom of the list.

The result should look like this:

In MicroStation open the Pick List Manager then import D:\DEC_Configuration\Organization\Dgnlib\DEC_Pick Lists.xlsx (copy attached below).

DEC_PickLists.zip

Resulting in:

That Pick List will now be available to use in the Item Type. (Note the Pick List Manager saves immediately, it does not have a Save button.)

Create the Item Type

Open the Item Types dialog. Create a New Item Type Library then the Item Type followed by four Properties as shown below:

Once they are all created and named correctly (note that the Property names must match the column names in the Excel lookup table), select CommonName (this will be the controlling Property) changing Pick List from (None) to CommonName.

Add an expression to each of the remaining Properties to link the value selected for CommonName with those properties:

The expressions are:

LookUp.GetEntry("UKNativeTrees","CommonName",this.CommonName).LatinName

LookUp.GetEntry("UKNativeTrees","CommonName",this.CommonName).Native

LookUp.GetEntry("UKNativeTrees","CommonName",this.CommonName).Notes

The expression specifies the name of the Excel worksheet containing the lookup table (the Excel file can contain multiple lookup tables on separate worksheets), the controlling value and finally for each property, its name, to link it to the controlling value.

(Note save Item Types as you go along, the library name will be highlighted in blue if there are unsaved changes.)

The Item Type is now ready to use, test it by picking the Attach Item tool, British Trees should be available with a pop down list in the CommonName field.

Here is the finished DGNLIB:

DEC_ItemTypes.dgnlib

Use it!

Then open a project file and the Item Type will be available for attachment, note that the library icon is now grey signifying that the Item Type is from a DGNLIB, the location of which will be shown in a tooltip:

Use with Labels

Create a cell that contains text fields sourced from an element with an attached Item Type, in this case obviously a tree!

Attach Labels to the trees that have Item Types attached as shown in the video below, Item Type properties can be changed, Labels will update in response to property changes:

Reporting

Once elements have Item Types attached reports on their properties can quickly be produced:

Resymbolization

The symbology of elements can be changed using Display Rules activated by the Item Type Properties, here the Silver Birch trees are highlighted in red indicating that they are to be removed:

Notes:

1. Once ITEMTYPE_LOOKUP has been defined using the '=' operator other files can be added using the append '>' or pre-pend '<' operators, e.g.:
   

   ITEMTYPE_LOOKUP > $(_USTN_WORKSETSTANDARDS)Dgnlib/WorkSet_ItemTypesLookup.xlsx

1. The original variable ITEMTYPE_EXCELLOOKUP has been deprecated from MicroStation CONNECT Edition Update 13 onwards. It will still function if used in existing configurations but has been replaced by ITEMTYPE_LOOKUP. This has been done in anticipation of other file formats being supported as lookup table containers as this feature develops over future releases. Currently only Excel files are supported.