Applies To
Product(s):	OpenPlant Modeler CONNECT Edition
Version(s):	10.07.00.32+
Environment:	Windows 8.1/10 (64-bit)
Area:	OpenPlant Modeler CONNECT Edition
Subarea:	External Database Query
Original Author:	Deepak Singh,Bentley Product Advantage Group

In OpenPlant Modeler CONNECT Edition, requirement is to populate properties field via external database file.

Background

In OpenPlant Modeler CONNECT Edition, we have provision to fetch data from external database & populate against the properties of a class. In this wiki article, we will learn

1. How to enlist DEVICE_TYPE_CODE for support using external database.
2. Based on selected DEVICE_TYPE_CODE value, values from database should reflect to rest mapped properties automatically.
3. Sorting of record should happen based on ranking.

For example, below provided image shows column name of database as PICK_LIST. In this exercise we will select SupportCode values & based on selection Area, TYPE and Ranking value should show against DISCIPLINE, TYPE & SEQ properties respectively for support class.

 Steps to Accomplish

1. Launch Bentley Class Editor CONNECT Edition.
   
   
   
   
2. Load OpenPlant_3D.01.08. ecschema of your desired workset.
3. Supplement with OpenPlant_3D_Supplemental_Modeling.01.08. ecschema  & OpenPlant_3D_Supplemental_ModelingViews.01.08. ecschema.
4. Search SUPPORT class, switch to Properties tab, refer image below.
   
   
   
   
5. Add below mentioned properties & add Category custom attribute to them. Refer image below
   
   DISCIPLINE
   
   
   
6. Similarly, add SEQ and TYPE properties along with Category custom attribute defined to them. Refer image below
   
   
   
   
7. Right click on DEVICE_TYPE_CODE property & select Custom Attributes > Add/Remove to add custom attributes. Refer image as shown below.
   
   
   
   
8. From Available Custom Attributes section, select Extend Type and External Data Source Definition Mapping custom attributes. Using drop arrow add them to Applied Custom Attribute Ensure to change the schema location. Once done click on OK. Refer bulleted sequential steps.
   
   
   
   
9. For External Datasource Definition Mapping custom attribute, right click on Property Mapping & select Add Element to define mapping components, refer image below.
   
   
   
   
10. Perform the same steps to four more time to add four records of mapping. Once done, it will look like as shown below.
    
    
    
    
11. Enter details for External DataSource Definition Mapping custom attribute as mentioned below.

Data connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
File Name of database:  SupportList.mdb
This is the name of database file, in this exercise database file name is SupportList.mdb
Path Location of database: _USTN_WORKSETROOT
This ask to point to location of database file, in this exercise database file is located as workset location, refer image below.
Note: Make sure there is no space before _USTN_WORKSETROOT.


SQL Select Statement: SELECT SupportCode,Area,TYPE,Ranking FROM PICK_LIST WHERE [Area] = 'Support' AND  [TYPE] = 'Stock' ORDER BY [Ranking] 

Property Mapping [0]
Table Field Name: SupportCode
EC Property Name: DEVICE_TYPE_CODE

Property Mapping [1]
Table Field Name: Area
EC Property Name: DISCIPLINE

Property Mapping [2]
Table Field Name: TYPE
EC Property Name: TYPE

Property Mapping [3]
Table Field Name: Ranking
EC Property Name: SEQ

12. Once desired inputs are added it will look like as shown below.
    
    
    
    
13. For Extend Type custom attribute, enter details as shown below
    
    Name: ShowPickListviewDataBase
    
    
    Note: Name value is case sensitive.
    
14. Save all the schema customizations
15. Load OpenPlant Modeler CONNECT, route a pipe & place support on it.
16. Notice that, for DEVICE Type Code it enlists the list of filtered record based of SQL query from database. Refer image below
    
    
    
    
17. Select RH record & inspect the properties value that should update automatically.
18. Notice that, rest properties get updated automatically as shown below.
    
    
    
    

See Also

communities.bentley.com/.../how-to-create-association-for-openplant-modeler-connect-using-openplant-project-administrator-connect

Still a Problem?

If above problem still persists, then please create a Service Request and contact the Bentley Product Advantage group.  Please include a list of all relevant details to get in depth analysis for a solution or workaround