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:
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:
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.
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:
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:
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).
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.)
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:
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:
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:
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:
Once elements have Item Types attached reports on their properties can quickly be produced:
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:
ITEMTYPE_LOOKUP > $(_USTN_WORKSETSTANDARDS)Dgnlib/WorkSet_ItemTypesLookup.xlsx
Hi. Great function. But not possible if your xlsx-datasource is receiving data from another workbook (power-query). That would be amazing!