4.24.4.3.1. Database
4.24.4.3.1.1. Add / remove database columns
Prev  Next
4.24.4.3.1.1. Add / remove database columns
4.24.4.3.1.1.1. Create database columns via SQL scripting
4.24.4.3.1.1.2. Add database column with PARTlinkManager
4.24.4.3.1.1.3. Make database column visible for display in PARTdataManager
4.24.4.3.1.1.4. Remove database column

To be able to import data from third-party systems in the form of a CSV file, the columns in the link database must be available in the same way.

The following description shows how to add any additional columns to the standard configuration.

The columns ERP_PDM_NUMBER, MAT_NAME and DESCRIPTION are created with the standard installation.

4.24.4.3.1.1.1. Create database columns via SQL scripting

The LinkDB database instance consists of several tables. Three columns are created in the ERPTABLE by default:

  • ERP_PDM_NUMBER

    Display of the material master number

  • DESCRIPTION

    Display of the description text

  • MAT_NAME

    Display of the material

Any number of additional columns can be created in the ERPTABLE to record the third-party system information.

ERPTABLE 

sql_execute create table ERPTABLE (/
ERP_PDM_NUMBER varchar (50) not null,/
DESCRIPTION varchar (200),/
MAT_NAME varchar (100),/
primary key (ERP_PDM_NUMBER))
sql_execute create public synonym ERPTABLE for SYSADM.ERPTABLE
sql_execute grant all on ERPTABLE to PUBLIC

Detailed information on the database structure can be found under Section 4.2, “Installation Link database ”.

4.24.4.3.1.1.2.  Add database column with PARTlinkManager

The following explains how to add database columns with PARTlinkManager.

  1. Log into the database under File menu -> Connect.

    [Note]Note

    The "Add columns" process accesses the database directly.

    Standard administration login for the PLINKDB:

    • User: sa

    • Password: (according to the password assignment during database creation)

  2. Select in PARTlinkManager -> Extras menu -> Database -> Change/create database columns [Modify/create database columns].

    -> The Create/remove database columns dialog box opens.

  3. Column [Column name] name:

    Define a name of your choice.

  4. Column type:

    Select a column type in the list field:

    VARCHAR (variable character length is often better than CHAR to avoid problems), CHAR, SMALLINT, INT, DECIMAL

  5. Length:

    A character string length must also be specified for text fields

    Default value: Optional

  6. Activate Create ERP column.

    If you want to edit the column in the PARTlinkManager in the Configure column display [Modify column display] dialog box (i.e. not only create it in the database itself, but also enter it in the corresponding configuration files), check the box. This is the recommended procedure.

  7. Confirm with OK.

    -> The column has been added.

  8. Open the Configure dialog box via Tools [Extras] menu -> Configure ERP environment.

    The column you have just created is displayed in the Configure column display [Modify column display] dialog area.

    The column is also visible in the PARTlinkManager table.

4.24.4.3.1.1.3. Make database column visible for display in PARTdataManager

For the column to be displayed in the PARTdataManager column is displayed, two conditions must be met:

  1. Under Configure column display [Modify column display], the corresponding column must be set to Yes under Visible.

    In the Variable [Variable DB] DB columns and Variable columns, the assigned column name is displayed.

    The entry under Variable DB is fixed.

    The entry under Variable can be adjusted if necessary.

    [Note]Note

    Care must be taken to ensure that this name is used consistently for all configurations. (See the next two points in particular)

  2. In PARTadmin, the column must be entered in the desired role(s) under Columns to be displayed.

    [Note]Note

    Pay attention to which role you have selected on the left under Roles. The column display is always role-related.

    [Note]Note

    The position in the PARTdataManager corresponds exactly to the position in the listing.

Save the change.

-> A message appears stating that the link database index should be regenerated if the column is to be included in the search.

See Section 1.3.3.6.6.4, “LinkDB search index ”.

PARTadmin -> Index management [Index administration]

PARTadmin -> Index management [Index administration]

Result in the PARTdataManager

[Note]Note

To display the column in the PARTdataManager you must be logged in with the role that contains the column.

4.24.4.3.1.1.4.  Remove database column

You can reach the corresponding dialog in PARTlinkManager via Extras --> Database --> Change/create database columns [Modify/create database columns] -> Remove tab page.

All columns created in the database are displayed in the list field under Column name.

Select the column to be removed.

Confirm with OK.

[Note]Note

If the database does not support the deletion of columns (e.g. SQLite), the Remove tab page is hidden.

The database is not accessed! Deletion only applies to PARTsolutions data.

Prev Up Next
4.24.4.3.1. Database Home 4.24.4.3.1.2.  Show database columns