How to Select Field Value from External Database PickList

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 : 
    
    
    
    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 
    
    

    
    
    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!