Sharing Tables
This section provides an overview of table sharing and discusses how to:
Add the SetID field to record definitions.
Define set control fields as the fields controlling the assignment of TableSets.
Modify set control fields.
Create setIDs.
Define record groups to identify the tables and subordinate (child) tables that are affected.
Define TableSet controls.
Share trees.
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:
U.S. Account Codes |
Canadian Account Codes |
Description |
---|---|---|
123456789 |
123456789 |
Teller |
987654321 |
987600000 |
Customer Service |
CCB-4476-EXTSAL-USA |
VNB-4476-EXTSAL-CDN |
Extraordinary Salary Employees |
CCB-4476-REGSAL-USA |
VNB-4476-REGSAL-CDN |
Regular Salary Employees |
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:
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.
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
![TableSets and set control fields](img/ia2cf27cn-76f9.png)
Assigning Set Control Fields
If you plan to use the Table Sharing feature to add an additional high-level key to identify common sets of values and handle exception values, you enter a set control field. The set control field determines which set of values appears, based on how you define table sharing.
Table sharing is usually applied in multicompany environments, in which you might want to share certain tables in some operating entities while allowing others to retain control over the contents of their own. Use the feature to maintain a single table in which you can store multiple sets of values to be used by different entities in your organization.
When you create a record definition that will be shared, you add the field setID as a high-level key. The setID value is controlled by the key that you select in the Set Control Field drop-down list box in the Record Properties dialog box. These setIDs identify groups of tables to be shared and enable each business entity to identify whether it wants to use the corporatewide table or maintain its own. Enter the name of the key as your set control field for each record definition that will be shared.
For example, if you have several companies in your organization, many of which share the same set of account codes, add a SetID field to the Account table record definition. In this definition, enter Company as the set control field. The set control enables different companies to access the account values stored in the same Account table. As each company defines its set controls, it has the option to use the shared Account table or maintain control of its own.
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.
![Adding setIDs to record definitions](img/ia2cf27cn-765c.png)
To add a setID:
Open the record definition for the table that you want to share.
Select
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:
Select
from the menu.Select the Use tab.
Select the field that identifies the appropriate setID in the Set Control Field drop-down box.
Click OK to save your changes.
Use the SQL Alter process to alter your underlying SQL table.
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.
![Attaching PeopleCode to your set control field](img/ia2cf27cn-7734.png)
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:
setID |
Description |
Short Description |
---|---|---|
USA |
Accounting Codes - USA Co.s |
AcctCd USA |
CAN |
Accounting Codes - CAN Co.s |
AcctCd CAN |
You create SetIDs on the TableSetID page in
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
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.
Defining TableSet Controls for Trees
To define TableSet controls for trees:
Select
in PeopleSoft Pure Internet Architecture.Enter the name of the set control value for which you want to assign a TableSet on the search page.
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.
Because you already assigned a default setID in TableSet Controls 1, the system displays the default setID that you assigned to this field value. If you create another TableSet for sharing trees, you can change this value.
Specify the tree name.
Click the prompt button next to the Tree Name field for a list of only the tree definitions with the same set control field (which has PostSave PeopleCode that recognizes it as a TableSet control field).
This prompt list comes from a SQL view of tree definitions with those set control fields that have not already been associated with a tree group.
In each tree row, specify the appropriate setID.
Save your changes.
Viewing All Record Definitions Associated With a Set Control
You can view all record definitions that are associated with a set control on the Record Group tab of the TableSet Controls page.
Example of Set Controls
After you set up and define all of your set controls, you can see the results by looking at pages on which you reference codes that are stored in a shared table. For example, on Administer Workforce, Use, Job Data 1 and 2 pages, Account Code is an optional field that is edited against the ACCT_CODE_TBL. In this component, the system identifies the company based on the department to which an employee is assigned. For example, Simon Schumacher is in department 10100, which is associated with the company CCB.
Select the Job Data 2 tab and enter 8001 or Simon Schumacher in the search record dialog box to retrieve a CCB row, for which the company is defined in the department table. Here, you can see that Simon works in department 00001, which identifies him as a CCB employee. If you click the prompt button next to the Account Code field, the system displays only those rows in the Account Code Table that are associated with USA, the setID for CCB.
Alternatively, if you access the job row for Joan Avery, an employee of VNB, and click the prompt button for the same Job Data 2 Account Code field, the system retrieves only values that are associated with CAN, the setID for VNB.