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 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.
One other suggestion would be to have a Move Up & Move Down button on the Values side of the Pick List Manager. This would aid with managing the order of the Pick List entries.
Thanks,
Karl
Sure Karl, I have added this to our wish list.
Hi Marco,
Marco Salino said:even though this may be an option to enable bi-directional support as many organization wouldn't like they users to be modify their master tables from MS but keep the single source of truth in Excel.
I think that you are missing the point:
Yes, some will want to keep everything in an external 'primary source'. And some, as Jon pointed out will want to keep / make the information in the dgn as the primary source. This is secondary. At some point, you will need to synchronise all the 'primaries' and then, you will need to provide a means of managed, bi-directional updates... to reconcile and maintain a 'single source of truth(s)'.
Hi Dominic, I understand you guys are talking about a managed bi-directional workflow, what I am thinking is if the changes should be done in the dgn and pushed to Excel, or directly in Excel and then pushed back in the dgn file.
I vote for changes done directly in Excel and then pushed back in the dgn file.