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.

  

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: