How to Select Field Value from External Database PickList




Applies To 

Product(s):

OpenPlant PID

Version(s):

08.11.10.XXX (SS6)

Environment: 

Windows 7/8.1/10 (64 bit)

Area: 

Database

Subarea: 

Original Author:

Nikhil Salunkhe, Bentley Product Advantage Group

Background: 

In OpenPlant PID, user wanted to choose Field Value, here for Manufacturer Property from Picklist which is created in database. After creating database, Picklist is displaying in OpenPlant PID but unable to select any of the Field Value. Sql Select Statement and Data connecting string were causing this issue in OpenPlant PID. So, in below Wiki article, we are defining the steps on how to select Manufacturer Field Value from Picklist with the corrected Sql Select Statement and Data connecting string. 

Steps to Accomplish

  1. Create a Database for ‘Manufacturer’ inside Dataset of a Project, here Metric Project by using Microsoft Access Database, here Access 2013 (Manufacturer.mdb) 

    Go to Start Menu and click on Access 2013  




    Click on Blank Database

     



    Browse to Dataset location of Metric Project, define File name and choose Microsoft access database 2002-03 format from Save as type menu and click on Create






  2. Fill below data in Database, then right click on Manufacturer and click on Design view. See below image





  3. Define Data type for every field name after changing to Design view.




  4. Save database with 2002-03 Compatible version then close the file.





  5. Launch Class editor.

  6. Open OpenPlant_PID.01.07.ecschema.xml schema.



  7. Supplement with OpenPlant_PID_Supplemental_Imperial.01.07.ecschema.xml schema






  8. Expand Classes tab and navigate to Vessel class in Plant Base Object, select Properties tab, go to Manufacturer Property and click ‘Override’ then select Manufacturer property, right click and Add External Data source Definition Mapping and Extend Type Custom attributes.





  9. Change Location to OpenPlant_PID_Supplemental_Imperial.01.07 and click Ok




  10. Fill below data, See below image

    Data connecting string : Provider=Microsoft.Ace.OLEDB.12.0;Data Source= 

    If your system is showing an error about OLEDB.12.0, we are providing below link.

    Link to download : 

    www.microsoft.com/.../confirmation.aspx

    Note :     This DOES work for office 2010 even though it is for 2007 office,

    Data connection string : Provider=Microsoft.Ace.OLEDB.12.0;Data Source=

    File name of Database : Manufacturer.mdb

    Path Location of database : _USTN_PROJECTDATASET

    Sql Select Statement : SELECT MANU_NAME FROM MANUFACTURER AS "VESSEL MANUFACTURER" ORDER BY MANU_NAME

    Right click on Property mapping and Add element 

    Table Field name : MANU_NAME

    ECProperty name : MANUFACTURER

    ExtendType : There are two Extended types 

    1. ShowPickListViewDatabaseForm ( Displays a detached form from which to select your data. You will select the data and click OK to close the form and populate the field.)

    2. ShowPickListviewDataBase ( Displays a list from which to select your data that is attached to the element info dialog.)



    Note : Kindly ensure that Sql Select Statement is correctly defined.

  11. Save all the changes.

  12. Launch OpenPlant PID & open Metric Project

  13. Place Vessel from Equipment Task.



  14. Right click on Vessel, go to Element information and select manufacturer Property in General info Section and Select ‘TANKS ARE US’ option. See below image.




  15. Notice the change in Manufacturer property ‘TANKS ARE US’, See below image.




    See also 

    stage-communities-bentley2-com.telligenthosting.net/.../calculated-ec-property-shows-failure-value-in-data-manager

    communities.bentley.com/.../3157.How-to-supplement-a-schema.docx


    Comments or Corrections

    Bentley's Product Advantage Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!