To access the Table Definition window, choose Tables from the Tables button on the toolbar. The Tables window will open. Select the name of the table you wish to use as the primary table, and then click Open. The Table Definition window for the desired table will open.
Use the following procedure to access the Table Relationship Definition window and define a new relationship. As an example, a table relationship will be created between the Sales Transaction Work (SOP_HDR_WORK) table and the Record Notes Master table. This table relationship allows the record-level note for an invoice to be printed on the invoice.
From the table definition of the table you wish to use as the primary table, click the Relationships button.
Click New to define a new relationship. The Table Relationship Definition window will open, and the name of the current table will appear in the Primary Table field.
Click the Secondary Table lookup button to open the Relationship Table Lookup window. Select the name of the table you want to use as the secondary table. In this example, this is the Record Notes Master table. Click OK. That table’s name will appear at the top of the Secondary Table portion of the Relationship scrolling window.
Choose a key containing the field or fields that you want this table relationship to be based on from the Secondary Table Key drop-down list. Once the key is selected, each of its key segments is listed in the Secondary Table portion of the Relationship scrolling window.
|
If you are defining a one-to-many relationship, selecting a key that places records from the secondary table in the desired order will optimize performance. |
For at least the first of the secondary table’s key segments, select a related field from the corresponding drop-down list in the Primary Table portion of the Relationships scrolling window. The drop-down list contains only fields from the primary table that have a data type compatible with the corresponding key segment in the secondary table. For example, if the key segment is a STR30 data type, only fields in the primary table that are compatible with the STR30 data type will appear in the drop-down list.
If the key contains multiple segments, you don’t need to select a corresponding primary table field for each key segment. However, the key segments for which you do select corresponding fields must be consecutive key segments. For example, if a key has four segments, you could select corresponding fields for the first two segments, but not for the first and third segments only.
If the secondary table’s key upon which this relationship is based allows duplicates, the relationship will be one-to-many, and Multiple Records will appear in the Relationship Type field.
If the selected secondary table’s key doesn’t allow duplicates, and you select a corresponding field for each key segment, the relationship type will be one-to-one, and One Record will appear in the Relationship Type field. If you select a corresponding field for only some of the key segments, the relationship type will be one-to-many, and Multiple Records will appear in the Relationship Type field.
To change the primary table field you’ve selected, simply choose a different field from the drop-down list. To deselect fields you’ve selected and not replace them with different selections, click Clear.
|
Clicking Clear removes all primary table field selections. |
When you’ve finished defining the relationship, click OK; you will be returned to the Table Relationship window. You can define another new relationship or edit an existing relationship. To edit an existing relationship, select the name of the secondary table from the Table Relationships list and click Open.