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 Implement a Secondary Instrument Database Table
    • 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
            • +Customize Tag or Annotation
            • +How do Modify the Component Flow Arrow?
            • How To Add & Write New Pipeline Size in OpenPlant PID
            • How to Add Custom Border in OpenPlant PID
            • How to Add Unit Abbreviations in DGN Reports and in the Databse
            • How to Add Units of Measurement on Property in OpenPlant PID
            • How to add “DN” prefix to valve size annotation in OpenPlant PID CONNECT Edition.
            • How to Add/Remove Tools in Main Task of OpenPlant PID
            • How to Assign Color by Service to the Pipeline
            • How to Assign Color to Equipment, Nozzle & Instrument by Service.
            • How to Break Multiple Properties on Spec Break
            • How to by pass the default line break and have a pipeline place on top of equipment.
            • How to Calculate and Customize the PID Text Style Size for Components
            • How to Cascade the Property Values from Pipe Run to Connected Valves
            • How to Change Pipeline Color and Line Weight in OpenPlant PID
            • How to Change the Date Format for Properties in OpenPlant PID
            • How to change the Date format for properties in OpenPlant PID CONNECT Edition.
            • How to Change the Order of Properties in Element Info and Component Dialog
            • How to Change the Priority of Pipe run Breaks
            • How to Configure OpenPlant PID to Show Imperial & Metric sizes in the Same Project
            • How To Create a Capsule Symbol for Instrument Winterization
            • How to create a pick list on EC Property using External Datasource definition
            • How to Create Battery Limit Symbol with Mirroring Capability in OpenPlant PID
            • How to Create Custom Angle Valve in OPPID
            • How to Create Custom Icon in OPPID
            • How to Create Custom Instrument and Custom Property Annotation.
            • How to Create Custom Spec Break with Extended Leader Line in OpenPlant PID
            • How to Create Custom Valve End Conditions using Existing Graphics
            • How to create Custom Valve Tag annotation which contains Custom Property that has a Drop-down option
            • How to Create Drop Down list on EC Property in OpenPlant PID
            • How to Create Jacketed Pipelines in OPPID
            • How to Create Multi-line Style Pipeline with Different Line Weights
            • How to customize Border Attribute Fonts?
            • How to customize Chemical Seal components so they do not prompt or create a Tag Number?
            • How to Format number property on Equipment and Pipelines in OpenPlant PID
            • How to get Association Property of One Project to Another Project
            • How to Get Drop Down List in Number Property of Instrument
            • How to get Line Tag Info for PRV in Data Manager
            • How to Hide Unused Properties on EC Class
            • How to Hide Valve Flange Line for Flanged End Condition
            • How to Implement a Secondary Instrument Database Table
            • How to Increase Gap Length Between Valve and Flange
            • How to Make a Valve Replaceable in OpenPlant PID
            • How to Make Off-Page Connector Rotatable to Place in Vertical Direction
            • How to make Page Connector Orientable
            • How to Make Pressure Reducing Valve Orient to Bigger Size
            • How to Make Revision Property Allow Alpha-Numeric Characters
            • How to Mirror or Rotate a Custom Equipment or Component while Placement
            • How to Modify Default Value for Device Type Code to a Specific Component Class
            • How to modify Specialty item
            • How to Notify Property Value Change From Pipe Run to Series of Instruments
            • How to place pipeline Tag to pipe run automatically in OpenPlant PID CONNECT Edition.
            • How to Populate Class Name on Components
            • How to Populate Component Class Name in Element information for OpenPlant PID
            • How to Propagate Property Value From Pipe Run to Pipeline to Update Pipeline Tag and Annotation
            • How to Pull Properties from Pipe Run to Annotate on Off-Page Connectors
            • How to Resize the Control Valve Actuators in OpenPlant PID
            • How to Set Any Component to Repeat the Insertion After Placement
            • How to Set Same Level to Major & Minor Pipeline but Different Line weight
            • How to Turn of PID Document Settings Dialog at OpenPlant PID Starting
            • How To Use Page Connectors to Show Information Between Two DGNs
            • Open Plant PID Grid and lock settings
            • OPPID: How do I Add a Custom Logo to a Border?
            • Placing Custom Component Leaves Gap in Component and Pipe Run
            • To Exclude Pipe Run using Line List in Data Manager for OPPID
          • +Database - Plant Project
          • +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 Implement a Secondary Instrument Database Table

    Applies To  Database Additional Tables
       
      Product(s): OpenPlant PID
      Version(s): V8i Selectseries 5+
      Environment:  N/A
      Area:  Customization
      Subarea:  Tables and Tags
      Uploaded by:

    Brandon Moberg

     

    This document defines the process of implementing a secondary instrument database table in a PowerPID environment. The situation is that additional fields are required in the instrument table to store information needed for control valves. There is a 255 field limit within any one database table in the plant project database and the addition of new fields will exceed this limit. This “How To” will guide you through the process of creating the additional instrument table and building the mapping in the PowerPID schemas.

     

    This process will require the following skill set.

    • Full understanding of the OpenPlant PowerPID schemas.
      • Adding a new mapping to the instrument class.
      • Adding new properties to a class and mapping them to the database.
      • Full use of the Bentley Class Editor.
      • Full understanding of the Plant Project Database and related administrative tools.
        • Adding a new table to the plant project database.
        • Adding new fields to the table.

      

    Edits in the Plant Project Database

     

    Editing the project database and adding a second instrument table

     

    Below are two processes to add the new table – one in an Access based project and the second in a SQL Server based project. For the SQL Server based project you will find a script that will create the table in a SQL Server project. This is provided to simplify the process.

                                    ACCESS BASED PROJECT

    1. Load the Bentley Project Administrator.
    2. Locate your Access based project, expand the Database node and then pick on Database.
    3. Select the PROJDATA table and then pick Edit Tables…
    4. From the Tables in PROJDATA dialog pick the Add… button. When prompted with the Add Tables dialog pick OK.
    5. In the Add Available Tables to Database dialog pick Create New…
    6. When Access loads add the fields as defined below.

    NOTE – KEYTAG field must be set as the Primary Key field and

    PROJ_ID field is only required for a Multi-Project mode project.

    INSTR_EXT1 TABLE

    Column Name

    Data Type

    Length

    Allow Nulls

    Is Primary Key

    KEYTAG

    Text

    10

    NO

    YES

    DESIGN_CODE

    Text

    25

    YES

    NO

    BODY_STYLE

    Text

    20

    YES

    NO

    ACTUATOR_STYLE

    Text

    15

    YES

    NO

    POSITIONER_REQUIRED

    Text

    5

    YES

    NO

    KEYTAG_GUID_PK

    Text

    AutoIncrement

    YES

    NO

    TSTRIP_ID_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    LOOP_ID_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    PANEL_ID_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    PLANT_KEY_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    SEGMENT_ID_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    DS_ID_GUID_FK

    Text

    AutoIncrement

    YES

    NO

    PROJ_ID

    Text

    4

    NO

    NO

     

    1. Once all fields have been added pick Save. In the Choose Name dialog name the new table INSTR_EXT1.
    2. Close Access and in the Add Available Tables to Database dialog pick the newly created INSTR_EXT1 table then pick OK.
    3. Pick Close in the Tables in PROJDATA dialog.

     


     

    SQL SERVER BASED PROJECT

    1. Launch SQL Server Management Studio.
    2. Pick the New Query button.
    3. Paste the query below into the new query window. Change the “xxxx” to your database name.

     

    USE [xxxxx]

    GO

     

    /****** Object:  Table [dbo].[INSTR_EXT1] ******/

    SET ANSI_NULLS ON

    GO

     

    SET QUOTED_IDENTIFIER ON

    GO

     

    SET ANSI_PADDING ON

    GO

     

    CREATE TABLE [dbo].[INSTR_EXT1](

       [KEYTAG] [varchar](10) NOT NULL,

       [DESIGN_CODE] [varchar](25) NULL,

       [BODY_STYLE] [varchar](20) NULL,

       [ACUATOR_STYLE] [varchar](15) NULL,

       [POSITIONER_REQUIRED] [varchar](5) NULL,

       [KEYTAG_GUID_PK] [uniqueidentifier] NULL,

       [TSTRIP_ID_GUID_FK] [uniqueidentifier] NULL,

       [LOOP_ID_GUID_FK] [uniqueidentifier] NULL,

       [PANEL_ID_GUID_FK] [uniqueidentifier] NULL,

       [PLANT_KEY_GUID_FK] [uniqueidentifier] NULL,

       [SEGMENT_ID_GUID_FK] [uniqueidentifier] NULL,

       [DS_ID_GUID_FK] [uniqueidentifier] NULL,

     CONSTRAINT [INSTR_EXT1_PK] PRIMARY KEY CLUSTERED

    (

       [KEYTAG] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

    1. Pick Execute. This will create the new table in your database.
    2. Close SQL Server Management Studio.

     

    Editing the project tag types to use the new INSTR_EXT1 table

     

    Now that the new table is defined you need to assign it to the required tag types as a Supplemental Table. This is performed in the Project Administrator. This process applies to all database types.

                                   

    1. Load the Bentley Project Administrator.
    2. Expand your project name node then the Database node and pick on the Tag Types node.
    3. Locate the AT_CVALVE tag type. Double click the tag type or pick the Edit… button.
    4. In the Edit Tag Type dialog pick the Tables tab.
    5. In the Tables tab and in the Supplemental Tables: field, add the new INSTR_EXT1 table as shown below.

     

     

    1. Pick OK.
    2. Repeat this process for all instrument tag types. AT_INST_, AT_INST_CS, AT_INST_FLO, AT_INST_MCC and AT_INST_ORF.

     

    Editing the projdata database to include the new INSTR_EXT1 table

     

    Next you must add the newly created table to the project. This will make the table available for the Process Instrument View which will be modified later. This is performed in the Project Administrator. This process applies to all database types.

                                   

    1. Load the Bentley Project Administrator.
    2. Locate your project and expand node to Database > Databases.
    3. Pick on the PROJDATA database name then pick the Edit Tables… button.
    4. In the Tables in PROJDATA dialog pick the Add… button.
    5. Select the newly created INSTR_EXT1 table. Pick EDIT.
    6. In the Edit Table [INSTR_EXT1] dialog the pick the ellipse button next to the Registered Applications field.

     

     

    7. As a minimum check on DataManager [DMGR] and Project Administrator [PA]. Others can be selected as well based on the need for this table in those applications. Pick OK.

     

     

    8. Pick OK in the Edit Table [INSTR_EXT1] dialog box.

    9. Pick Close in the TablesinPROJDATA dialog.

    10. Exit Project Administrator.

     

     

     Edit the default Process Instrument List view to include the INSTR_EXT1 table fields

     

    In order to be able to add new valves to the view in DataManager as well as be able to browse for tags in the database while in PowerPID you must modify the Process Instrument List view to include the new table. This is performed in DataManager and Access. This process applies to all database types.

                                   

    1. Load the Bentley DataManager. Make sure all other applications that access the project database are closed.
    2. Locate the Process Instrument List view. Right click on it and from the menu pick Edit.
    3. In the Edit View dialog pick on the Design… button (You may have to uncheck the Protected box if the Design button is not active.)
    4. When the Design View – Process Instrument List dialog box loads pick Design with Access… in the lower left hand corner.
    5. When Access loads, pick the Show Table button located in the Design ribbon. In the Show Table dialog scroll the list of tables and locate the INSTR_EXT1 table. Double click the table to add it to the view design panel.
    6. Close the Show Table dialog.
    7. A join line will be created between the newly added INSTR_EXT1 table and the KEY_LINK table. Right click on this join line and from the context menu pick Delete.
    8. Create a new join between the INSTR_EXT1 table and the INSTR table by clicking on the KEYTAG field in the INSTR table and dragging this over and dropping it on the KEYTAG field on the INSTR_EXT1 table.
    9. Right click on the join line and pick Join Properties.
    10. In the Join Properties dialog set the left and right tables and the left and right columns as shown below. Also set the join type to the second join type identified with the number 2 by picking the radio button next to the number 2.

     

     

    11. Pick OK.

    12. To add the INSTR_EXT1 fields to the view, scroll the list of field names in the INSTR_EXT1 table and double click the DESIGN_CODE, BODY_STYLE, ACTUATOR_STYLE and POSITIONER_REQUIRED fields. They will be added to the end of the field list below

    13. Save the view. Exit Access.

    14. Pick OK in the Design View – Process Instrument List dialog.

    15. To test the view modification, locate the Process Instrument List view and double click it. It should load in DataManager.

     

    SPECIAL NOTE: If mapping a class such as equipment where the base table (EQUIP) is NOT the tag registry this mapping is slightly different. You must map the TAG_REG table to the EQUIP_SUPPLEMENTAL table. DO NOT map the EQUIP_SUPPLEMENTAL to the EQUIP base table. So in the Join Properties dialog shown above the Left Table Name would be TAG_REG and the Right Table Name would be EQUIP_SUPPLEMENTAL table.

     

    Edits in the OpenPlant PowerPID Schemas

     

    Adding the INSTR_EXT1 table mappings to the mapping schema

     

    Now that the database table has been defined and the tag types adjusted to use it you need to modify the mapping in the schemas to include the new table. This process is completed using the Class Editor.

                                   

    1. Load the Bentley Class Editor.
    2. Load the OpenPlant_PID schema located in your project workspace.
    3. From the menu select Database Mapping > Open…
    4. In the Open Mapping dialog pick the Browse… button. Verify that the schema directory listed in the Custom Schema and Mapping Locations dialog is pointing to your project workspace. If not then use the Remove button to remove the current path and then the Add button to add the correct workspace folder. Pick OK.
    5. In the Open Mapping dialog pick OK.
    6. If prompted to locate the OpenPlant ProjectProperties schema browse to your project workspace folder and select the correct file.
    7. First you need to reload the database and related tables – In the Summary tab pick the Edit Database Structure… button
    8. In the Database Structure Editor dialog pick the Remove button. Then pick the Load button. Load (reload) your database so that the newly created INSTR_EXT1 table is available.
    9. Locate the Control Valve class. In the Class Mapping tab select the Edit… button in order to edit the mapping for Control Valves.
    10. When the Edit Class Mapping dialog loads start with the General tab. Add the new table to the Tables list by using the Add… button. Add the INSTR_EXT1 table. See below.

     

     

    11. Move to the Select tab. You must now add the Join Table clause. Pick the Add… button and in the Edit Join Clause dialog set the Joined Table Name to INSTR_EXT1 and the Main Table Name to INSTR Set both column names to KEYTAG. See below.

     

     

     

    12. Pick the OK button.

    13. From the menu select DatabaseMapping > Save. Then select DatabaseMapping > Close.

    14. Right click on the PlantProjectSchema in the tree and from the context menu select Close.

     

    SPECIAL NOTE: When adding the supplemental table to an equipment class you MUST add the Supplemental table to the EQUIPMENT class AND the specific subclasses that will be writing the data to the supplemental equipment table. Just like property mappings, class table mappings are not inherited and MUST be added to all equipment classes that use them. For example, the PUMP class requires the new EQUIP_SUPP table. When you perform the edits above, you need to perform these on the EQUIPMENT class AND the PUMP class. Properties only have to be mapped at the class level that require them and not the upper level class.

    On the Select Statement tab you would add the supplemental table and the join would be EQUIP_SUPPLEMENTAL.KEYTAG = TAG_REG.EKYTAG.

     

     

    Adding new properties to Control Valve class

     

    Now that the database table has been defined and the mapping between the Control Valve class and the new tables complete you will add properties to the Control Valve class into which you can add the additional data.

                                   

    1. Continuing in the Bentley Class Editor.
    2. Right click on the OpenPlant_PID schema and from context menu select Supplement Schema…
    3. Add the OpenPlant_PID_Supplemental_Imperial schema.
    4. Browse to the Control Valve class. Switch to the Properties tab.
    5. Add the four new properties that are to be written into the INSTR_EXT1 table. Add all of these properties to a new Category Named EXTENSION_PROPERTIES. See below. The Property name will change but the Category Custom Attribute will be the same for all four properties. Add DESIGN_CODE, BODY_STYLE, ACTUATOR_STYLE and POSITIONER_REQUIRED.

     

     6. Save the schema.

     

    Mapping the new properties to the INSTR_EXT1 table

     

    Now that the properties are defined on the Control Valve class you need to map these properties to the INSTR_EXT1 table fields. This process is completed using the Class Editor and Database Mapping tools.

                                   

    1. Continuing in the Bentley Class Editor.
    2. From the menu select Database Mapping > Open…
    3. In the Open Mapping dialog pick the Browse… button. Verify that the schema directory listed in the Custom Schema and Mapping Locations dialog is pointing to your project workspace. If not then use the Remove button to remove the current path and then the Add button to add the correct workspace folder. Pick OK.
    4. In the Open Mapping dialog pick OK.
    5. If prompted to locate the OpenPlant ProjectProperties schema browse to your project workspace folder and select the correct file.
    6. Locate the Control Valve class.
    7. Pick on the Properties tab.
    8. Locate the ACUATOR_STYLE property. In the Class Mapping tab, lower panel select the Edit… button.
    9. In the Edit Property Mapping dialog add the mapping to the new table and field. Pick the INSTR_EXT1 table and the ACTUATOR_STYLE field.

     

     

    10. Pick OK.

    11. Repeat this process for the three remaining new properties mapping them to their respective field in the new table.

    12. From the menu pick DatabaseMapping > Save.

     

     

    Testing the Configuration

     

    Test to verify all changes.

     

    The last part of the process is to test the results. This will verify the changes made. Your test should include the following:

    • Create a new PID in the OpenPlant workspace where you made the changes.
    • Draw a process line and then insert a Control Valve.
    • Synch to the database and verify the data in DataManager.
    • Switch to DataManager and add a new control valve to the Instrument view.
    • Switch back to PowerPID and add a new Control Valve but in lieu of typing in the tag, use the Browse button to browse the database and pick the valve tag. Place the valve.
    • Synch again from Drawing to Database to verify.
    • Make a change in the PID to the properties added in the INSTR_EXT1 table (Design Code etc.) and synch to the database.
    • Make changes to the new fields in DataManager and sync Database to Drawing and verify all properties update.

     

     


    • 08.11.10.523
    • Bentley OpenPlant PowerPID
    • Share
    • History
    • More
    • Cancel
    • bmoberg Created by Bentley Colleague bmoberg
    • When: Wed, Apr 1 2015 11:22 AM
    • bmoberg Last revision by Bentley Colleague bmoberg
    • When: Wed, Apr 1 2015 11:32 AM
    • Revisions: 6
    • 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