11 Setting Up Data Relationships
This chapter contains the following topics:
11.1 Understanding Data Relationships Rules
These rules apply to data relationships:
-
Only one set of current rules can exist for a supported table. For each set of rules, sequencing is set up to determine the order of the default values. Each sequence level for a set of rules must be unique.
-
The destination table must be the same as the basis table.
-
If more than one entry program exists for a table, the default rules are the same regardless of the entry program. For example, for the F0006 table, the default rules are the same for both the Business Unit Master (P0006) and Job Cost Master (P51006) programs.
-
A single basis column and value can populate multiple destination columns and values. For example, if the basis column is category code 01 and the value is ABC, then the destination columns could be category codes 02 and 03 with values 123 and XYZ, respectively, within the same table.
-
The basis and destination columns are edited based on the data dictionary rules for the respective data item. For example, assume that an administrator defines category code 01 as the basis column. When a user selects the visual assist for the basis value, the user should see a list of values for category code 01.
The data relationships functionality can also be used by customers who conduct business with the government. You can set up data relationships rules for government contracts.
11.2 Activating Data Relationships Functionality
This section discusses how to activate data relationships functionality for tables.
11.2.1 Forms Used to Activate Data Relationships Functionality
| Form Name | FormID | Navigation | Usage |
|---|---|---|---|
| Work With EnterpriseOne System Control | W99410A | Data Relationships (G09415), Activate Data Relationships | Review a list of data items. |
| EnterpriseOne System Control - Revisions | W99410B | On the Work With EnterpriseOne System Control form, select the data item for DRF0006 or DRF5201. | Activate data relationships for a table. |
11.2.2 Activating Data Relationships Functionality for a Table
You must activate data relationships before you can use the data relationships functionality that is available for the F0006 and F5201 tables.
|
Caution: Activating data relationships is like activating a constant setting. After the initial activation, you should not change the setting. The results will be unpredictable and will cause data integrity issues. |
Access the EnterpriseOne System Control - Revisions form.
Figure 11-1 EnterpriseOne System Control - Revisions form
Description of ''Figure 11-1 EnterpriseOne System Control - Revisions form''
To activate the data relationships functionality for the associated tables, select the Yes option for these data items:
-
DRF0006 (for the Business Unit Master table, F0006).
-
DRF5201 (for the Contract Master table, F5201).
11.3 Reviewing Supported Data Relationships Columns
This section provides an overview of supported data relationship columns and discusses how to review supported relationship columns for a table.
11.3.1 Understanding Supported Data Relationship Columns
You can use the Register Relationship Columns program (P00424) to review a list of the columns in the F0006 and F5201 tables that are supported for data relationships. For each column (data item), the system displays the column type, which identifies whether the column can be used for both basis and destination values or for basis values only.
The program logic in the data relationship programs is dependent on the column type. The column type for AN8O (Owner Address) in the F0006 and F5201 tables is hard-coded to be basis only. The value in the AN8O column can be dependent on columns that are not supported by data relationships. If you set up a data relationship with AN8O as a destination column, the system would override the original value in AN8O, which could cause severe data integrity issues. For that reason, the AN8O should be designated as a basis column only.
If you try to change the column type for AN8O to basis and destination, the system issues a hard error message and you are prevented from changing the column type.
The system does not prevent you from changing basis and destination column types to basis only; however, you should do this during setup to prevent data integrity issues and not after you begin using the data relationships functionality. If you change a column type to basis only for any data item other than AN8O and then change the column type back to basis and destination, the system does not issue an error message. To prevent users from changing column types from basis only back to basis and destination, you should add logic in the P00424 program or place security on column types.
Although the software allows you to add new columns to a table, that work should be performed by a software developer or a field consultant because program logic must also be added to accommodate the new columns. JD Edwards EnterpriseOne does not support changes made to column types after the initial setup or the addition of columns to a table.
The data relationships functionality can also be used by customers who conduct business with the government. The F0006 and F5201 tables support additional columns for government contracting functionality.
11.3.1.1 F0006 Table
This table discusses the supported data relationship columns in the F0006 table and whether the column can be used both as a basis column and destination column or as basis column only:
| Column (Data Item) | Description | Column Type |
|---|---|---|
| ADDS | State | Basis/Destination |
| ADJENT | Adjustment Entry | Basis/Destination |
| ANPA | Supervisor | Basis/Destination |
| AN8 | Address Number | Basis/Destination |
| AN8GCA1–AN8GCA5 | Address Number 1–5 | Basis/Destination |
| AN8O | Owner Address | Basis Only |
| CT | Contract Type | Basis/Destination |
| RP01–RP50 | Category Codes 1–50 | Basis/Destination |
| STYLE | BU Type | Basis/Destination |
11.3.1.2 F5201 Table
This table discusses the supported data relationship columns in the F5201 table and whether the column can be used both as a basis column and destination column or as basis column only:
| Column (Data Item) | Description | Column Type |
|---|---|---|
| AI01–AI15 | Contract Category Code 1–15 | Basis/Destination |
| ANOB | Address Number–Owner Billing | Basis/Destination |
| AN8O | Job A/R | Basis Only |
| AN8K | Address Number–Architect | Basis/Destination |
| CT | Contract Type | Basis/Destination |
| DS | Contract Status | Basis/Destination |
| INVF | Invoice Format Code | Basis/Destination |
| USA1–USA5 | User Address Number 1–5 | Basis/Destination |
11.3.2 Forms Used to Review Supported Data Relationships Columns
| Form Name | FormID | Navigation | Usage |
|---|---|---|---|
| Register Relationship Columns | W00424B | Data Relationships (G09415), Register Relationship Columns | Review the list of tables that support data relationships. |
| Revise Relationship Columns | W00424A | On the Register Relationship Columns form, select a table and click Select. | Review the supported columns for a table. |
11.3.3 Reviewing Supported Data Relationships Columns for a Table
Access the Revise Relationship Columns form.
Figure 11-2 Revise Relationship Columns form
Description of ''Figure 11-2 Revise Relationship Columns form''
This form example shows a sample of the supported columns for the F0006 table.
See F0006 Table.
- Column Type
-
The system displays the column type that is valid for a column. Values are:
1: Basis and destination.
2: Basis only.
You cannot change the column type for AN8O from basis only (2) to both basis and destination (1); the system issues a hard error message. AN8O is the only column in the F0006 and F5201 tables that is hard-coded to be basis only.
You can, however, change other column types to be basis only. Be aware if you change the column type from basis and destination (1) to basis only (2) and then back to basis and destination (1), the system does not issue a warning or error message. Consider adding program logic to the P00424 program or place security on column types to prevent users from doing this.
11.4 Defining Column Relationships
This section provides overviews of column relationships, the Enforce Rule and Blanks Allowed check boxes, and destination columns that are check boxes, lists a prerequisite, and discusses how to:
-
Define column relationships for a column set.
-
Revise a column set.
-
Delete a column set.
11.4.1 Understanding Column Relationships
For each data relationship, you must define the column pairs that relate to each other in the column set. You use the Column Relationships program (P00421) to define the basis and destination columns by effective date for each column set, as well as specify:
-
The sequence in which the column pairs should be read and edited by the Update Data Relationships program (R00422).
-
Whether the enforce rule applies to the destination column for the column pair.
-
Whether a blank value is allowed in the destination column for the column pair.
The columns for value relationships must have edits at the data dictionary level. For example, assume that the basis column in a data relationship contains the data item RP12 and the destination column contains RP13. When you define basis and destination values for that relationship, you must enter values from UDC table 00/12 and UDC table 00/13, respectively. The basis and destination columns are edited based on the data dictionary rules for the respective data item. If you use the visual assist, the system displays valid codes from those UDC tables only.
11.4.1.1 Column Relationships Rules
These rules apply when you define column relationships for a column set:
-
A table can have only one column set for a specific effective date.
You can define column relationships for column sets with future effective dates.
-
The sequence numbers within a column set must be unique.
-
A basis column can have multiple different destination columns. A destination column cannot be defined for multiple basis columns.
-
A column pair in a column set does not have to have a relationship with the other column pairs. You can have unrelated relationships within the pairs in a column set.
-
After you define a basis column for a column pair, you cannot use the basis column as a destination column for another pair with a higher sequence number. Stated another way, after you define a destination column for a column pair, you cannot use the destination column as the basis column with a lower sequence number. The end result in both of these scenarios is the same; the system will issue an error message.
Following these rules, sequence number 40 would result in an error:
Sequence Number Basis Column Destination Column 10 Category Code 01 Category Code 02 20 Category Code 02 Category Code 03 30 Category Code 03 Category Code 04 40 Category Code 04 Category Code 02
|
Caution: The Business Unit Master (P0006) and Job Cost Master (P51006) programs both write records to the F0006 table. Not all columns are used by both programs when writing to the F0006 table; therefore, you should use caution when you define column relationships. Otherwise, you might get unexpected results or have integrity issues. The same caution applies to the Contract Master (P5201) and Create/Edit Project and Government Contracts (P52G01M) programs, which both write records to the F5201 table. Ensure that you define column relationships only for columns that are used by the corresponding entry program. |
11.4.2 Understanding the Enforce Rule and Blanks Allowed Check Boxes
The Column Relationships program (P00421) has two check boxes:
-
Enforce Rule
-
Blanks Allowed
These check boxes control the default rules that apply to the destination columns that you define for a column set. The system uses the rules to update the destination values in the F0006 and F5201 tables. This table discusses the check boxes and default rules:
| Check Boxes | Default Rule |
|---|---|
| Enforce Rule | If the Enforce Rule check box is selected, the destination column is protected from input in the entry program. (The destination column is protected from input even if it later becomes a basis column in the column set.) If a value for the destination column exists in the table, in update mode the system overrides that value with the value defined in the Value Relationships table (F00422). If a blank value is valid and the Blanks Allowed check box is selected for the destination column, the system overrides the existing value in the table.
If the Enforce Rule check box is not selected, the destination column allows you to enter non-blank values. If a value for the destination column exists, the system does not override that value because the Enforce Rule check box is not selected. Instead, the system skips the destination column and the default rules continue with the first destination column that is either blank or has the Enforce Rule selected. |
| Blanks Allowed | If the Blanks Allowed check box is selected, blank values are valid for the destination column. This check box is valid only for strings and characters, and not for math numeric values.
Note: The Blanks Allowed check box is only valid if the Enforce Rule check box is selected. |
11.4.3 Understanding Destination Columns That Are Check Boxes
Most destination columns are fields in the F0006 or F5201 tables, but some are check boxes. For example, the destination column for ADJENT (Adjustment Entry) in the F0006 is a check box. Columns that are check boxes function differently than columns that are fields.
Unlike a field, a check box value cannot be blank. The value is either 1 (the check box is selected in the entry program) or 0 (the check box is not selected in the entry program). So a check box that is unchecked has a value of 0, and not blank.
In update mode, the system overwrites a destination column value with the check box value from the value relationship only if the Enforce Rule flag is activated for the column relationship. In add mode, the system updates the check box with the value from value relationships, regardless of the Enforce Rule flag.
11.4.4 Prerequisite
Before you complete the tasks in this section, you must remove any default values that exist at the data dictionary level For data items that are check boxes and are used as destination columns. Data Relationships will not function properly if default values exist.
11.4.5 Forms Used to Define Column Relationships
| Form Name | FormID | Navigation | Usage |
|---|---|---|---|
| Data Relationships Setup | W00420A | Data Relationships (G09415), Data Relationships Setup | Navigate to the setup programs for data relationships. |
| Work With Column Relationships | W00421A | On the Data Relationships Setup form, select Column Relationships.
Data Relationships (G09415), Column Relationships |
Review a list of existing column relationships. |
| Revise Column Relationships | W00421B | On the Work With Column Relationships form, click Add or Select. | Define column relationships. |
11.4.6 Defining Column Relationships for a Column Set
Access the Revise Column Relationships form.
Figure 11-3 Revise Column Relationships form
Description of ''Figure 11-3 Revise Column Relationships form''
You can navigate between the programs for column relationships and value relationships by selecting Value Relationships from the Form menu or Revise Values from the Row menu on the Revise Column Relationships form.
- Table
-
Enter F0006 or F52001 in this field, depending on whether you want to define column set relationships for the Business Unit Master or the Contract Master table.
- Column Set Description
-
Enter a description to further define the column set.
- Effective As Of
-
Enter the date that the column set will be effective.
- Sequence Number
-
Enter a unique number that identifies the sequence of the columns. The sequence determines the order in which the column pairs in the column set are read for default values when you run the Update Data Relationships program (R00422).
Note:
If you leave the Sequence Number field blank, the system increments the last active sequence number by 10. For example, if the last active sequence number is 20, the system enters 30 as the default value.Using the example, if you add the sequence number 25 (to insert between 20 and 30), the system increments that number (25) by 10 if you leave the next Sequence Number field blank. So the default sequence number would be 35, which might not be the desired result if, for example, sequence number 40 already exists.
For this reason, you should be careful when mixing the system default values with manual numbering.
- Basis Column
-
Enter the data item of the column that is the basis of the data relationship.
You can define one basis column with multiple different destination columns. For example, you could enter multiple column pairs with RP01 in the Basis Column field and different destination columns (RP02, RP03, and RP04) in the Destination Column fields.
After you define a basis column for a column pair, you cannot define the same basis column as a destination column with a higher sequence number. Using the previous example, if sequence number 10 = RP01 (basis) —> RP02 (destination), you cannot enter sequence number 20 = RP03 (basis) —> RP01 (destination).
- Destination Column
-
Enter the data item of the column that is populated when the basis column and value are entered for the data relationship.
- Enforce Rule
-
Select this check box to enforce the data relationship rules for the destination column. If selected, the destination column is disabled in the entry program and the system prevents you from revising the corresponding destination value. If a destination value already exists, the system overwrites the value in the F0006 or F5201 table.
If not selected, the destination column is input capable and the system does not overwrite the existing value.
See Understanding the Enforce Rule and Blanks Allowed Check Boxes.
- Blanks Allowed
-
Select this check box if blank can be a valid value for the destination column in the Value Relationships program (P00422). You can select this check box only for strings and characters, and not for math numeric values. If you select this check box, the Enforce Rule check box must also be selected for the column pair.
See Understanding the Enforce Rule and Blanks Allowed Check Boxes.
11.4.7 Revising a Column Set
If, after you define relationships for a column set, you later revise the values for the check boxes for Enforce Rule or Blanks Allowed, the system displays a warning message that unpredictable results could occur for existing records.
11.4.8 Deleting a Column Set
To delete a column set, you must first delete the corresponding values in the Value Relationship program (P00422) if values exist. The system issues an error message on the Revise Column Relationships form if you try to delete a column set with existing values. After you delete the values for the column set, you can delete the column set on the Revise Column Relationships form. (You can easily navigate between the Revise Value Relationships and Revise Column Relationships forms by selecting exits from the Form and Row menus.) Select the record on the Revise Column Relationships form and click Delete. Confirm the deletion. The system no longer displays the records. Click OK to permanently delete the records from the F00422 table.
You can delete only one column set at a time.
11.5 Defining Value Relationships
This section provides an overview of value relationships, lists a prerequisite, and discusses how to define value relationships for a column set.
11.5.1 Understanding Value Relationships
After you define column relationships for a column set, you must define basis and default destination values for the column relationships. You use the Value Relationships program (P00422) to define the basis and destination values by effective date. You can define basis and destination values only if the parent record for the column set exists in the Column Relationships table (F00421). The parent record contains the column relationships, whereas the child record (which is a subset of the parent record) contains the basis and destination values. The basis and destination value are stored in the Value Relationships table (F00422).
11.5.2 Prerequisite
Before you complete the tasks in this section, you must define column relationships for a column set.
11.5.3 Forms Used to Define Value Relationships
| Form Name | FormID | Navigation | Usage |
|---|---|---|---|
| Work With Value Relationships | W00422A | On the Data Relationships Setup form, select Value Relationships.
Data Relationships (G09415), Value Relationships |
Click Find to review a list of column sets and column relationships. |
| Revise Value Relationships | W00422B | On the Work With Value Relationships form, click Find. Select the column set and do one of the following:
To add a new record, click Add. To add values to an existing record, click Select. |
Define value relationships for a column set. |
11.5.4 Defining Value Relationships for a Column Set
Access the Revise Value Relationships form.
Figure 11-4 Revise Value Relationships form
Description of ''Figure 11-4 Revise Value Relationships form''
You can navigate between the programs for value relationships and column relationships by selecting Column Relationships from the Form menu or Revise Columns from the Row menu on the Revise Value Relationships form.
11.5.4.1 Column Set
The fields in the Column Set group box are disabled and are based on the column pairs that you defined in the Column Relationships program. The values for these fields are stored in the Column Relationships table (F00421).
11.5.4.2 Value Set — Date Selection
Enter values in the Date Selection fields to review existing basis and destination values for a specific date or range of dates; otherwise, leave the fields blank to review all records and effective dates for the column set.
- Show Current Values
-
To display records that are current as of today, select this check box and click Find. If this check box is selected, the Effective On and To Date fields are disabled.
To display records that are effective for other dates, do not select this check box and enter values in the Effective On and To Date fields.
- Effective On
-
If you selected the Show Current Values check box, the system enters today's date in this field and the field is not input capable.
If you did not select the Show Current Values check box, enter a date in this field.
- To Date
-
If you selected the Show Current Values check box, the system enters today's date in this field and the field is not input capable.
If you did not select the Show Current Values check box, enter a date in this field and click Find.
If you entered a date range, the system displays all records that are current as of the date in the Effective On field, as well as all records with effective dates after the Effective On date and through the To Date.
11.5.4.3 Value Set — Defaults
Enter values in the Defaults fields only if you are entering new records for basis and destination values.
- Default Effective As Of
-
Enter a default date to use in the Effective As Of field. The default date appears in the detail area after you enter the destination value and exit the row.
If you leave this field blank, you can manually enter the date in the Effective As Of field in the detail area for each record or let the system enter the Column Set Date as the default date.
- Pre-Load Unmatched Basis Values
-
This check box appears on the Revise Value Relationships form only if the basis column value in the Column Set group box has a UDC edit.
Note:
If you have entered records in the detail area of this form, click OK to write those records to the F00422 table before you select this check box and click Find. Otherwise, the system will write over the newly entered records.Select this check box to display the unmatched UDC values for the basis column in the detail area. A UDC value is unmatched if it has not been previously selected for the column set. For example, if you select this check box and the Basis Column (in the header area) contains the data item RP12, the system will display the values for UDC table 00/12 that have not been previously selected for the column set. To add a basis value from the UDC table, enter a value in the Destination Value field of the record and click OK. Then click Find to review the records. If you do not add a basis value, the system does not write a record to the F00422 table if the Destination Value column is blank.
Note:
If blanks are allowed for the column set (the Blanks Allowed check box in the header area is selected), select each of the records that you do not want to include in the column set and click Delete.You must delete the records manually; otherwise, the system includes them in the column set (because they are blank) and writes the records to the F00422 table.With the remaining basis values displayed, enter dates in the Effective As Of field. If you leave this field blank, the system uses the date in the Default Effective As Of field as the default when you click OK. If that field is blank, the system uses the date in the Column Set Date field as the default when you click OK.
11.5.4.4 Detail Area
- Basis Value
-
If you did not pre-load unmatched basis values, you must manually enter the basis values.
Enter the value that is the basis for the default destination value. The value that you enter in this field must be valid for the Basis Column field in the header area. For example, if the Basis Column field contains the data item RP12, you must enter a value from UDC table 00/12. The visual assist shows a list of values that are valid for the basis column.
A value of blank is not valid in this field, even if blank represents a value in a UDC table. Because basis columns and values trigger the destination default values in the F0006 and F5201 tables, you must enter a value other than blank in this field for defaults to occur.
If the basis column is a check box instead of a field, the basis value can be an unselected check box. An unselected check box is valid for a basis column. While the unselected check box might appear to have a blank value, it actually has a value of 0 in the database.
The basis value is the trigger that creates default destination values in the F0006 and F5201 tables when data relationship functionality is used.
- Destination Value
-
Enter the default value of the destination value associated with the basis value. The value that you enter in this field must be valid for the Destination Column field in the header area. For example, if the Destination Column field contains the data item RP13, you must enter a value from UDC table 00/13. The visual assist shows a list of values that are valid for the destination column.
A value of blank is valid in this field only if the Enforce Rule and Blanks Allowed check boxes are selected for the column set.
If you leave this field blank and blanks are not allowed for the column set (the Blanks Allowed check box is not selected), the system does not write the record to the database.
The destination value is a default value that appears in certain entry programs when data relationship functionality is used.
- Effective As Of
-
Enter the date in which the values for the basis and destination are effective. If you entered a value in the Default Effective As Of field, leave this field blank to accept the default date.