How to add Additional Field to Component Data in Access and SQL, and to make it an editable data fie


Applies To
Product(s):AutoPLANT V8i
Version(s):
Environment:N/A
Area:Database
Subarea: Access/SQL
Original Author:Vidit Ruhela, Bentley Product Advantage Group

Overview

To add Additional Field to Component Data, and to make it an editable data field in Piping.

Background

Add the Data to the Catalog

The first step to adding component data, is to add the data to the component.  There are several available fields in the catalogs that can be used for a piece of user defined data.  (i.e. Alt_P_Mark, User 1, User 2, User 3), User may also create a new field.  As always, data must be entered manually into the Catalog Databases. 
***In cases where the chosen data is simply a series or other form which Microsoft Office can calculate, the data may be created in Excel and then copied over to the Catalog MDB in Access as a shortcut***
Note: Ensure all the above-mentioned steps should be done under the supervision of some Database Admin/Specialist.

Steps to Accomplish

Add the new field to the Piping Table for Access Server.

Although all Catalog Data is generally stored in the GData of each component, only valid fields from the Piping Table are included in Component Data for BOM.  Once the data field is part of component data then user simply need to add the field exactly as it appears in the catalog to the Piping Table.  This can be done in Project Administrator by following the steps below:

  1. Launch Project Administrator.
  2. As per the mentioned steps in the below screenshot edit the PROJDATA table.



  3. Scroll Down to the Piping Table, highlight it and Press the Edit button.



  4. Select Table Design.



  5. Add required field to the Table.  The field Properties from most descriptive data can be set to match the Piece Mark field properties.



  6. Save and Close the Piping Table Design Session.

Add the new field to the Piping Table for SQL SERVER. New field can be added by two methods.

1st Method

  1. Before editing any table make sure to uncheck the Prevent saving changes from options as per below screenshot.


    once unchecked, the changes made can be saved.

  2. Open Microsoft SQL Server Management Studio open the required Database to add the new field in the selected Table. (The below mentioned screenshot show the required changes to be carried in the test1 Database)



  3. Expand the Tables field and scroll down to the PIPING Table right click on PIPING Table and select Design as mentioned in the below screenshot.



  4. Put the Column name as shown in step 1 and Data type in step 2 as per user requirement.



  5. Check in the Piping Data field and found that new Test field is added in the component data.



    Steps to delete any field

  6. Open the required field by following the above-mentioned steps 2 & 3.
  7. Right click on the field and below dropdown window for more editing options.

2nd Method

To create the new queries in SQL Server Management Studio kindly refer the below steps to add and modify the queries.

  1. Open SQL Server Management Tool.
  2. Click on New Query as mentioned in the below screenshot.




  3. Run the query “ALTER TABLE PIPING ADD TEST1 varchar(15);” in the query tab to add a new column in the piping table and click Execute. Please find the below screenshot for your reference.


    once the above mentioned query is executed the new column is added in the Piping Table as mentioned below.



    Deleting a column (TEST1) from the Piping table.

  4. Open SQL Server Management Tool.
  5. Click on New Query as mentioned in the below screenshot.



  6. Run the query “ALTER TABLE PIPING DROP COLUMN TEST1;” in the query tab to delete TEST1 column from the piping table and click Execute. Please find the below screenshot for your reference.


    ones the above mentioned query is executed the new column is deleted from the Piping Table as mentioned below.
     

Updating Previously Created Components

Although all components created from this point forward will automatically be populated with the new data, previously created components will need to be updated.  Running the command Piping>Database Tools>Update From Spec should take care of the update.

See Also

communities.bentley.com/.../adding-a-new-descriptive-field-to-updateable-component-data

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!