User login

Joins

Joining Tables



You can also add supplementary tables to the report if you need to pull information from fields not available in the main table. Click the + in the Join Info column to choose from a list of tables related to the main table.

Add Join

The lower dropdown box contains the different tables that have a relationship to the main table. Select the table to which you’d like to join the main table. The box just above it allows you to choose the join type.


Join Types


There are four types of joins available when adding tables to a report.

  • Left Outer Join: All of the records in the first (left-hand) table are included even if there are no matches in the second (right-hand) table. This is the default join type.
  • Right Outer Join: All of the records in the second (right-hand) table are included even if there are no matches in the first (left-hand) table.
  • Full Outer Join: All of the records in both tables are included.
  • Inner Join: All of the records from both tables which have matching values in the fields on which the tables were joined are included.


NOTE: It is always better to choose the correct main table and add additional tables than try to compensate for choosing the wrong table by adding tables and modifying the join.

Any join will show beneath the main table. Unlike the main table, joins can be added at any time and can also be removed from the report if you either chose the wrong table or no longer need its information. You can add multiple joined tables. Tables can be joined directly to the main table or joined to another table, which is joined to the main table. Click the + for the table to which you want to join another. In the example below, the PM table is joined to the COMPINFO table (Assets), which is joined to the WORK table (Work Orders), which is the main table. This is indicated by the blue arrows.

Joined Tables


Options with Joined Tables


Each table is displayed in a different color and the fields on the right side of this screen are displayed in the same color as their corresponding table. This makes it easier to see from which table a particular field was taken. You can add fields from these tables, by clicking on the folder next to the table, just like adding fields from the main table.


Joined Tables and Fields


You can change the join type by clicking on the pencil icon for that table, or remove the table by clicking the trashcan.