Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
AutoPLANT | OpenPlant | PlantWise
  • Product Communities
  • More
AutoPLANT | OpenPlant | PlantWise
AutoPLANT | OpenPlant | PlantWise Wiki How to Select Field Value from External Database PickList
    • Sign in
    • -AutoPLANT | Bentley Raceway and Cable Management | OpenPlant | PlantWise Wiki
      • -OpenPlant
        • +CONNECT Services - OpenPlant
        • +Example Configuration - CONNECT Edition
        • +Installations - OpenPlant CONNECT Edition Applications
        • +OpenPlant Download Links & Videos
        • OpenPlant Product Compatibility
        • +OpenPlant Modeler CONNECT
        • +OpenPlant PID CONNECT
        • +OpenPlant Isometrics Manager CONNECT
        • +OpenPlant Orthographics Manager CONNECT
        • +OpenPlant Support Engineering CONNECT
        • +OpenPlant Project Administrator CONNECT
        • +OpenPlant CONNECT Edition-PlantSight Integration
        • +OpenPlant Modeler v8i
        • +OpenPlant Isometrics Manager v8i
        • +OpenPlant Project Administrator v8i
        • -OpenPlant PID v8i
          • +Application Solutions - OPPID
          • +Class Editor - OpenPlant PID
          • +Customization - OpenPlant PID
          • -Database - Plant Project
            • How to Create a new SQL Server Project and Synchronize OpenPlant PID Projects to it?
            • How do I turn on Number Auto Incrementation of Components on placement?
            • How to Associate an OpenPlant PID Drawing with Another Project
            • How to Change OPPID and Plant Project's Names
            • How to Configure Particular Project to Work in Offline Mode By Default
            • How to Create Custom Border and Map Custom Property with Database
            • How to Get Related Valve Tag in Instrument List in Data Manager
            • How to Map Custom Properties with Database
            • How to Map Input/Output Signal of Instrument in OpenPlant PID
            • How to Select Field Value from External Database PickList
            • How to Setup a Batch Routine to Sync all Drawings in my Project to the Database at one time
            • How To Show Modified Tag Format in Data Manager
            • How to Sync the Valve End Condition with Project DB
            • Importing Data into the Plant Project Database from an Excel Spreadsheet
            • Mapping Plant Project Extended Data Fields to OpenPlant PowerPID
            • OPPID - All drawings have same GUID. Drawings update database incorrectly
            • OPPID - Database Mapping Tips
            • OPPID - Document and their component Links
            • OPPID - Guidelines to ensure OPPID to APPID database field integrity
            • OPPID - How to determine Database Connection and Sync issues
            • OPPID - How To Get Motor Related to Equipment Sync to Database
            • OPPID - How to use the Class Editor Mapping Extension for the Plant Project Database
            • OPPID - Import of Associated Items
            • OPPID - YStrainer Database populate
            • OPPID Pipeline To-From SQL
            • OPPID- How to add custom properties to specific piece of equipment or sub class and link to a database
            • OPPID- SQL Logging
            • Plant Project Database - Class Mapping
            • Plant Project Database - Mappings
            • Plant Project Database - OpenPlant Validation and Repairing a Database Mapping
            • Plant Project Database - Operations with Schema Mapping and Mapping Add-in panel
            • +Plant Project Database - Oracle Install notes
            • Plant Project Database - Property Mapping
            • Plant Project Database - Relationship Mapping
            • Plant Project Database - Schema Mapping Custom Attributes
            • Plant Project Database Tag Format - Tag Codes
            • Setting OpenPlant PowerPID Project with Plant Database Project
            • SQL Server 2008 and Database to Schema Connection
            • To Import Modified Tags from Excel in Data Manager
            • Error: "Connection Test Failed" While Mapping SQL Database to Plant Project
            • Calculated EC Property Shows Failure Value in Data Manager
            • "{SERVICE_IS_RELATED_TO_EQUIPMENT(VG.0000003279)} belongs to the ECSchema {ECSchema: PlantProjectSchema.01.04} which is not supported in this connection" Error on Synchronizing from Database to Drawing
            • "Project ID 'XXXX' not found in AT_PROJ.DB3"
            • How to Move a Project Database from One Directory to Another For OPPID
            • Troubleshooting Synchronization issues using Component Selection Tool
          • +Properties - OpenPlant PID
          • +Release Notes - OpenPlant PID
          • +Training - OpenPlant PID
          • +White Papers/Guidelines - OPPID
        • +OpenPlant Orthographics Manager v8i
        • +OpenPlant Support Engineering v8i
        • +OpenPlant ModelServer v8i
        • +OpenPlant Reporting v8i
        • How to Connect with Bentley Technical Support Team using Microsoft Teams
        • How to Connect with Bentley Technical Support Team using Microsoft Teams via Browser
      • +Bentley AutoPLANT
      • +Bentley Navigator
      • +Bentley PlantSpace
      • +Plant Project Tools
      • +Bentley Raceway and Cable Management
      • i-model Composition Service for S3D
      • Working from home with OpenPlant
      • Working from home with Bentley Raceway and Cable Management
      • Working from home with AutoPLANT Modeler
    • How to Associate the Specification Based on Service in OpenPlant Modeler Connect

     
     Questions about this article, topic, or product? Click here. 

    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 : 



      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!



    • dropdown
    • External database
    • SQL statement
    • OpenPlant PID
    • 08.11.11.309
    • 08.11.09.XXX
    • SS6
    • PickList
    • OPPID
    • Class Editor
    • Manufacturer
    • Customization
    • Database
    • Share
    • History
    • More
    • Cancel
    • Nikhil Salunkhe Created by Nikhil Salunkhe
    • When: Wed, Mar 21 2018 7:09 AM
    • Kestutis Mitkus Last revision by Bentley Colleague Kestutis Mitkus
    • When: Wed, Mar 6 2019 3:27 PM
    • Revisions: 5
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2021 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies