Manage Related Tables

In this document the methods are described for displaying fields from other BE tables or XtendM3 tables related to the master table by defining related tables in the program 'Related Tables. Connect' (CMS011).

Follow these steps

  1. Open (CMS011) by using alternative 11='Related tables' in these programs:

    • 'List and Printer Programs. Configure' (CMS005)
    • 'XML Structure Section. Open Element' (CMS009)
    • 'Information Browser Category. Open' (CMS010)

    In (CMS011), you can define up to 30 related tables.

  2. To create a related table on the (CMS011/B) panel, specify the table.

  3. Use alternative 1='Create'.

Parameters to set

Field The field indicates ...
Sequence number ...the read sequence used for this related table compared to other related tables.
Sorting option

...an index connected to the table.

An index is a unique sorting order of a table. The sorting is controlled by the selected fields and by their relation to each other.

System-defined indexes (00–99) or user-defined indexes (U1–U9, V1–V9, or X1–X9) can be used.
Read option

...how to set the key fields in the related table according to the selected 'Sorting' option.

  1. On the N panel, select if all or only specific fields should be added to the 'Field' group.

  2. Press Enter to activate the related table and update the 'Field' group. In the list program, fields from the related table can be selected as fields (columns) in the list.

  3. Select an alternative:

    1 = Key field set equal to value from the field 'Object' or 'Value'

    2 = Key field set equal to blank

    3 = First record found according to key fields with a lower number

    4 = Last record found according to key fields with a lower number

    5 = Key field set equal to user's current division

    6 = Key field set equal to user's current language

    7 = Mixed division

    8 = Active date

    If alternative 3, 4, or 8 is selected, all subsequent key fields must have the same read option.

    If alternative 7 is selected, the program first reads with the current division. If no record is found, a second read is performed with a blank division. This alternative is used for tables having both blank and local division data, for example, accounting identities.

    If alternative 8 is selected, the program returns the first record found according to the key field with a lower date that is based on the Object or Value field. The first record is displayed in a generated XML after printing. If both fields are empty, the lower date is based on the current date. This alternative is used for tables with a valid from date in the key, for example, sales price lists.

    Alternative 9 is only available when creating XML sections in the 'XML Section. Open Table (CMS008).

    Alternatives 12 and 13 are only available for XML output.

    For connection type 2-'Join', only read option 1 is used.
Trim zeros ...if leading zeros should be removed. The field is only used if the 'Master table' field is numeric and the 'Related table' field is a text string.

You can also perform a quick entry to define a related table:

  1. Use F8='Create New' for the button 'Create New' on (CMS011/B).

  2. Specify the related table name and press OK .

    If the 'Read' alternative between the related and master table can be determined automatically, (CMS011/N) is displayed.

  3. On (CMS011/N), specify the field selection to select if all or only specific fields should be added to the 'Field' group. If specific fields should be selected, a maximum of 10 fields can be selected simultaneously.

    If the 'Read' options cannot be determined automatically, (CMS011/E) is displayed.

  4. Press Enter to activate the related table and update the 'Field' group with selected fields.

  5. If additional fields should be added, use alternative 7='Add field to field group' .

Outcome

The fields from the related table can now be selected as fields (columns) in the view.

Related topics