IMOS - Joining Tables and Fields

When adding columns to a Report Designer report, fields from logically linked tables can be added to the same report, including custom elements created in the Data Dictionary. Between any two tables there can be two types of relationships:

• One-to-many: Created by joining tables.

  • There can be many records in a joining table that match a single record in the base table. As such, an increase in the number of rows can occur when utilizing this type of join.

• One-to-one: Created by joining fields.

  • There is only one matching data record in the joining tables, and there can never be more than one

  • There will not be an increase in the number of rows in the output with this type of join.

Joining Tables

1. After selecting one of the primary tables, hover over it and then click + to join a table. All one-to-many joins available for the selected table will be present when clicking the “+” icon:

2. Click on the tribar menu and select one of the following table joining methods:

   • Default: The main table record appears the same number of times as the corresponding secondary table records.

   • Aggregate: The values from the secondary table will be concatenated within each of the secondary table columns. You can use this method to consolidate records.

Tutorial: Joining Tables

Tips on Joining Tables

• Try to avoid joining one-to-many twice from the same source.
  
  Example:

  • Start from Voyage > Voyage Itinerary.

  • To know which cargo is loaded/discharged, adding Voyage > Cargo Handling directly results in too many lines. A better approach is Voyage → Voyage Itinerary → Cargo Handling.

• If you have no choice but to join one-to-many more than once, plan each join carefully:

  • Start with a plan for which columns you expect. If those are not available as columns in the Report Designer, you might have to export to an Excel output and then use a PivotTable for the result.

  • If there is a logical link between the two tables added, there should be a logical filter to be applied, as in the example below.

  • Identify your primary table and the relationships you will use; make sure you have everything mapped.

Joining Fields

The join icon, , indicates that a link can be made to another table via that field.

For example, you can click  next to Ref Company No in the Cargo table to create a direct link to the Address Book table, in which that field is located.