Sharing Tables

To share tables, you must modify the record definitions for both the table that you want to share and the one that contains the set control field. Using the online PeopleTools Utilities for TableSets, you define the terms or controls for sharing.

For example, the PeopleTools database holds data for three companies: one U.S.-based parent company (PST) and two sister companies (CCB in the U.S. and VNB in Canada). In this organization, all of the U.S.-based companies share one set of accounting codes, and all of the Canadian-based VNB companies share another:

These two groups of companies must maintain parallel sets of accounting codes and, in some cases, use the same code, uniquely identified for U.S. or Canadian companies.

You must set up the Account Code table so that users who access that page or prompt for valid values see only the values for their respective companies, depending on whether they are located in the U.S. or Canada.

To do this:

1. Modify the Account Code table, which will be shared, and the Company table, the values of which (CCB, PST, VNB) control the TableSets that are used.

2. Create two TableSet IDs:

   • USA: For U.S. companies.

   • CAN: For Canadian companies.

Image: TableSets and set control fields

The following diagram explains how two groups of companies can maintain parallel sets of accounting codes that contain unique TableSets and set control fields

Add the setID field to the record definition for table sharing purposes.

Image: Adding setIDs to record definitions

This example illustrates the fields and controls on the Adding setIDs to record definitions. You can find definitions for the fields and controls later on this page.

To add a setID:

1. Open the record definition for the table that you want to share.

2. Select New > Field > Character.

3. Enter the special field that PeopleTools provides for table sharing:

   SETID

   SetID is a 5-character field. Add it to the top of your record definition list, and define it as a required key, search and list item, with a prompt table edit against the PeopleTools SETID_TBL.

After you add the setID field to your record, identify the set control field in Record Properties.

To define the set control field:

1. Select Edit > Definition Properties from the menu.

2. Select the Use tab.

3. Select the field that identifies the appropriate setID in the Set Control Field drop-down box.

   See Understanding Table Sharing.

4. Click OK to save your changes.

5. Use the SQL Alter process to alter your underlying SQL table.

6. When the system prompts you for a default setID, enter the most common setID for your company.

If you are sharing tables or using a set control field that is not already defined as such in your PeopleSoft application, you must make some modifications to the record definition for the table in which you store values for your specified set control field. For example, all PST company codes are stored in the COMPANY_TBL.

Here, you must add PeopleCode to the set control field so that each time you add a new value, the system populates the set controls in PeopleTools utilities, where you assign the appropriate TableSets.

Image: Attaching PeopleCode to your set control field

This example illustrates the fields and controls on the Attaching PeopleCode to your set control field. You can find definitions for the fields and controls later on this page.

This program assigns a default setID with the same value as the Set Control Field value, for each new COMPANY row that you add to the COMPANY_TBL. For example, when you add a row for CCB in the Company table, it creates a default setID of CCB. This way, each value that you add is assigned a unique TableSet until you assign shared TableSets in the Utilities window.

You can easily clone and modify this program to change the ADD_SETID and ADD_TABLESET_CNTRL statements to reflect your table sharing objectives. Alternatively, if you are working with existing TableSet functionality, you can point to a model setID that is delivered with your PeopleSoft application.

After you prepare for sharing your table, you define the setIDs that logically group information. When you define setIDs, you create groups that might naturally share more than one table. For example, although CCB and VNB are planning to share only the ACCT_CD_TBL now, the setIDs that you set up might define logical divisions in the organization that serve as the basis for sharing all accounting-related tables. Describe your sets to give them as broad an application as is practical in your organization.

If possible, use descriptions that denote the shared table and set control field. For example, for CCB and VNB, we added two TableSet IDs, identifying them as related to accounting codes for companies:

You create SetIDs on the TableSetID page in PeopleTools > Utilities > Administration > TableSet IDs.

Record groups are a set of logically and functionally related control tables and views. They help enable TableSet sharing, which eliminates redundant data entry and ensure that it is applied consistently across all related tables and views.

In the record group table, you group the record definitions for the tables that you want to share and any dependent record definitions. If you add a table to a PeopleSoft application, an appropriate record group might already be defined. However, if you add new business functions, you might need to add a new record group for the tables that you define. You access the record groups table in PeopleSoft Pure Internet Architecture on the Record Group page.

As you add values for your set control, the system automatically populates the TableSet Controls 1 table with default values. You define TableSet controls in PeopleTools > Utilities > Administration > TableSet Control using the browser. Here you can enter the set control value for which you want to assign a TableSet.

For example, we use Company as our set control field, and PST, CCB, and VNB are values that we want to add to the Company table. The system automatically assumes that each new set control value maintains its own set of tables and does not enable table sharing. So, the default values are the same as the set control value. In this case, when we added the value CCB to the Company table, the system populated the TableSet controls with a default setID of CCB.

Because CCB is a U.S.-based company that should share the same U.S. accounting codes, we must change the defaults to USA.

To share trees as well as tables and views, complete TableSet Controls 2 in the same way that you did TableSet Controls 1. For example, if PST and CCB want to share one department tree for organizational security and VNB another, you assign the appropriate setID for each value.