I just started testing this method out but it seems to be working great so I thought I would share.
I am using the Excel Lookup table to populate various Text Fields for all my Sheet models. In a separate model, I place a cell with an Item Type definition attached to it. Then place Text Fields that are linked to the Cell. The Text Fields correspond to my border sheet information. I copy the Cell & linked Text Fields to all my sheets. Then by selecting the Properties of the Cell, I change the text fields from a default Heading to the Sheet Number corresponding to my file in the Excel Lookup table.
I found the background information from the Forum site postings by Snehal Deshpande:
(Technology Preview) Expression to Derive Data from Lookup Tables
General Access Technology preview in MicroStation Update 12
https://communities.bentley.com/products/microstation/f/microstation-forum/176214/connect-u12-technology-preview-expression-to-derive-data-from-lookup-tables
https://docs.bentley.com/LiveContent/web/MicroStation%20Help-v14/en/LookUp.html
The key for me to get this to work was the development of the “Pick List”. I listed 500 sheets in this pick list. If I had one suggestion, it would be to have a Move Up & Move Down button on the Values side of the Pick List Manager. Make it a bit more robust.
I made a cell with the Item Type attached to it.
Here are my Item Type definitions.
Here is a before and after example of the Text Fields that reflect choosing the proper Sheet file from the pulldown.
Pulldown set to Headings Pulldown set to Sheet-001
Here is a snapshot of the Properties dialog of the selected Cell showing the Expression values of the Item Type. This is where I change the pulldown from Headings to Sheet-001.
Pulldown selections
Here is a snapshot of the Excel file lookup table. Note that the pink colored cells should never change since they are utilized by the Lookup expression. The orange row of headings can change and the columns of data under the headings can be changed for each sheet.
This could even be utilized as a Sheet Index by making a heading for Sheet Total and Sheet Name/Number.
Here are the variables that I used:
# File that contains the Item Type and Pick List for Text Substitution method
MS_DGNLIBLIST > $(_USTN_ORGANIZATION)dgnlib/TextSubstitution/TextSubstitution_CE.dgnlib
# Project Directory to locate Text Substitution Excel file
ITEMTYPE_EXCELLOOKUP = $(_USTN_WORKSETDGNS)Shared/Support/TextSubstitution_CE.xlsm
I apologize for this lengthy posting. My goal was to share this process or possibly start a discussion to see if anyone else is using the lookup table as a text substitution method.
Using MicroStation CONNECT Edition Update 12 - Version 10.12.00.40
Karl Todt
Interesting!
I think that you are using Item Types embedded in the title blocks to look up an Excel table.
So, the corresponding fields in the title block would not be editable in the Sheet model?
What would be great is if you can have bi-directional updates. A change in the title block / Sheet model would update the Excel table as well.
Also, is there a way to jump to the Excel table from the title block / Sheet model ? Engineering Link?
I've played with this too and agree that it will be really useful if it can be made bi-directional.
I agree it would be nice if it was bi-directional for updates. But I find that it is not too much of an inconvenience to open the Excel file for updates. It is very useful to have a single Excel source that can be modified to reflect text field changes in 100 files.
I don't believe there is a way to "jump to the Excel table" from the text field links in the sheet model.
Karl Todt said: It is very useful to have a single Excel source
I agree! Bi-directional data exchange sounds good, but raises the question "Which is my primary data source?"
That is not to say that transmitting data from MicroStation to Excel is a bad idea. In fact, it's a good idea that Bentley Systems have implemented in their Report tool. You can already query Item instances in a Report and export that Report to external data sinks, such as Excel.
Regards, Jon Summers LA Solutions
Jon Summers said:You can already query Item instances in a Report and export that Report to external data sinks, such as Excel.
Yea.. I wonder if the next step would be to embed / store the Excel table in a dgn and bypass Excel altogether. :-)
Jon Summers said:raises the question "Which is my primary data source?"
Yes, I suppose one benefit of having bi-directional updates is that there would be a mechanism to avoid / answer this question. If all the manifestations of the data is always synchronized, then every thing is 'primary' or 'single source of truth'...
But, the devil is in the detail. For example, say you roll your own and provide a means of looking up an external table and do not provide the means to lock the calling object (the title block in this case); and/or provide a reasonably idiot-proof means to jump to the primary data source, then you can bet that you will have consistency and other problems later on... easily negating any initial productivity gains.
CE has started to provide tools to support 'model based documentation'... which is good, but what is still a bit missing is a Revit-style 'Parametric Change Engine'.
Dominic Seah said:Yyou can bet that you will have consistency and other problems later on
Yes: a hole in one! Those problems bedevilled older GIS tools (MicroStation GeoGraphics) that stored non-graphic data in an external database. That's one reason why Bentley Map moved to XML-based Feature Modeling (XFM) as the DGN internal data store. I guess that XFM informed EC Schemas and subsequently Item Types. EC Schemas define that single source of data.
I think that Bentley Systems should make more noise about EC Schemas. They have a splendid technology embedded in their base platform, MicroStation. It's the essence of a coherent BIM story.