I've never liked the idea of duplicate data sources and the inevitability of them becoming out of sync, never mind the tedium of double entry. Excel functions can be used to link between worksheets to allow one list of values to used in different places. Basic stuff for Excel wranglers maybe, but simple is good and very useful in this context. I'm going to look at Pick lists and Look Up tables in the context of applying some degree of automation to drawing title blocks by eliminating a lot of manual data entry. As always the techniques demonstrated are sure to have other uses.
With Item Types Excel is used for at least two different purposes:
A workbook containing Pick Lists is used to make creation of Pick Lists quicker and easier as discussed in Item Types - Populating Items using Lookup Tables. This workbook may only used while Pick Lists are being created and is not linked to the resulting Pick Lists stored in a DGNLIB. It is browsed to during the process and while it can be anywhere in your file system, it makes sense for it to be in the same folder as the DGNLIBs for future use and, more importantly, for the steps described below where it will have an ongoing active role.
A workbook containing look up tables however, is always actively used. The Item Types that refer to it need it to be present for the Item Type look up values to be available. This workbook must therefore be in a configured location, again the DGNLIBs folder is the sensible location for this. The location is not defined by default so a configuration variable statement must be added to a configuration file at the appropriate level, for instance in a Standards CFG file in the Organization folder, e.g.:
ITEMTYPE_LOOKUP = $(_USTN_ORGANIZATION)Dgnlib/SMB_ItemTypesPickListsAndLookUps.xslx
Item Types should be stored in a DGNLIB so they can be shared by all users, the location of DGNLIBs containing Item Types is defined by configuration variable, e.g.:
MS_DGNLIBLIST_ITEMTYPES = $(_USTN_ORGANIZATION)Dgnlib/ItemTypes_*.dgnlib
Setting this variable will ensure that applications look only for Item Types in the listed DGNLIBs and ignore other content.
In this example I'm starting with a Pick List workbook that contains, amongst other things, some project names:
This contents of column C need to be added to the the Pick Lists in the DGNLIB by importing from this workbook, browse to the workbook SMB_ItemTypesPickListsAndLookUps.xslx:
To update existing lists select these options, all lists in the source workbook will be added (any unwanted lists that are imported can be deleted, separate workbooks can be used to partition sets of lists to avoid this.) :
The list of projects is now in the Project Pick List ready for use:
Notes:a) currently each workbook can only contain one PickLists worksheetb) while the workbook may contain multiple worksheets, the PickLists worksheet must be the first one, i.e. the leftmost tab.
The objective is for the site addresses to be looked up by Excel based on the project name in the PickLists source worksheet; so in this case the pick list worksheet and the workbook containing it will continue to be active after picklists have been created and must be accessible from the active WorkSet.
While the Excel function used can look at other workbooks when the full path is specified, this turned out to be problematic, as on repeated use Excel hardcoded the path to the source workbook. To simplify things and avoid this problem, the Pick List source and the lookup worksheet(s) can be in the same workbook (file).
The expression used to look up values includes the name of the worksheet containing them, so one workbook can contain multiple worksheets, one for each look up table, but as noted above these must all be to the right of the PickLists worksheet. In SMB_ItemTypesPickListsAndLookUps.xslx I have the existing table of trees in worksheet UKNativeTrees used in Item Types - Populating Items using Lookup Tables and have added a new worksheet, ProjectAddresses.
This worksheet contains the project address details, the project names are pulled from worksheet PickLists (note that all column headings should not contain spaces as they will be used in Item Type expressions):
Using the formula:
=PickLists!C1:C1
referring to cell C1 of the PickLists worksheet. More on this formula can be found online via appropriate searches.
Excel drag and fill is used to fill the the remaining fields in column A.
(As further projects are added the project list in PickLists should be updated, while the related address details are added to the ProjectAddresses worksheet in SMB_ItemTypesLookup.xlsx, using drag and fill to add the necessary rows to column A that will pull in the new project names.)
Returning to the DGNLIB, open Item Types Manager and add a definition for 'Project'. This one simply selects the Project Pick List. Then add a new definition for 'Site Location 1' (spaces are OK in the display name).
Site Location 1 will use an expression to gather the correct value from each project's row:
LookUp.GetEntry("ProjectAddresses","Project",this.Project).SiteLocation1
The syntax of this expression is:
LookUp.GetEntry("<WorkSheetName>","<ExcelKeyName>",this.<ItemTypeIndexValue>).<ExcelTarget value>
Click the Test button to check its validity, then click OK to enter the expression into the definition:
Create the remaining definitions by copying the first (using copy and paste icons in the dialog tool bar) then edit the name of each copy and the expressions to match the subsequent data columns. In this case it's easy, just change the number at the end of each:
This can be done using the Expression Builder, click on the edit icon at the right of the Expression field (...):
This process results in the address fields being added to the item ready for attachment to an element:
This example is aimed at populating Title Blocks so I have an example Title Block where the Title Block Fields Item Type has been attached to the boundary shape and a a number of Text Fields have been placed:
Some of these Text Fields are populated by the properties of the boundary shape, which now include project name and address. To add the address lines pick the Place Text tool then right-click to insert field:
Select Element Properties:
Identify the element (follow the cursor prompt!):
Then select the appropriate property, note that all of the Title Block Fields properties and values are available, the appearance of the text is previewed and can be formatted are required:
Click OK to place the text:
To enter the subsequent address lines, the quick way is to copy the first line (use number of copies!), which is formatted the way you want it, then edit each Text Field:
Right-click on the text, pick Edit Field:
Select the appropriate property and click OK:
Do this for each line:
To reuse with a different project select the boundary shape (that the Item Type is attached to as mentioned previously) and in Properties pick another project name from the pop down:
All the fields update accordingly:
I also have another example of using text fields in Title Blocks where WorkSet properties are used to populate some of the Text Fields: Item Types - Using Item Types and Text Fields in Title Blocks
Now you can hum the Looney Tunes (tm) theme to yourself...
............................................................beep beep!