Define Join Relationships
Joins are defined by specifying the fields that link a record in one object to a record in another object.
Join Theory
In a join, choose at least one object that contains an ownership record. The reason:
- The Filter by Record Ownership option is commonly used for Reports based on Database Views.
About creating joins:
- When two objects are selected, a single, mandatory relationship (Join) must be specified
- When three objects are selected (e.g. A, B and C), two mandatory relationships must be specified
- Joins between the following fields are allowed:
- ID field (a field containing a record identifier)
- Components
- All Lookup fields and Lookup-Derived Fields
- Fields where the field name ends with '#'
Rules for Joins
When creating joins, note that each join is dependent on the previously defined join.
For example, consider the case where we have three tables: A, B and C. In these joins, the first join row defines the possibilities for the second row:
A.id <join type> B.id A.id <join type> C.id or A.id <join type> B.id B.id <join type> C.id where <join type> is Left, Right or Equi
Note: Self joins are not permitted; The same object cannot be used on both sides of the join
Example Objects
In this example, we have a two objects: instruments and players in a band. As you might expect, the players in the band might play multiple instruments, and multiple instruments may be available to the band members. As is true with many bands, this band needs a drummer, and some players do vocals only.
|
Object A |
Object B | |||
| instrument_ID | Instrument | player_ID | Players | Can Play Instrument |
|---|---|---|---|---|
| IN1 | Guitar | P1 | Bill Bertie | IN1 |
| IN2 | Keyboard | P1 | Bill Bertie | IN4 |
| IN3 | Drums | P2 | Madeline Morris | IN2 |
| IN4 | Accordion | P2 | Madeline Morris | IN4 |
| P3 | Sissy Smith | IN2 | ||
| P3 | Sissy Smith | IN4 | ||
| P4 | Kerry Kline | IN1 | ||
| P5 | Crissy Clemens | |||
| P6 | Gary Green | |||
Using Joins
Joins are a means to relate two objects (tabs) in order to build reports containing fields from both objects. There are three types of joins:
Left-Join
When objects A and B are related with a Left-Join, the report includes all records in object A, and all matching records in object B. Using the example above, the resulting report would include these records:
|
Left-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN3 | Drums | |
| IN4 | Accordion | Madeline Morris
Sissy Smith |
Right-Join
When objects A and B are related with a Right-Join, the report includes all records in object B, and all matching records in object A. Using the example above, the resulting report would include these records:
|
Right-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN4 | Accordion | Madeline Morris
Sissy Smith |
| Crissy Clemens | ||
| Gary Green | ||
Equi-Join
When objects A and B are related with an Equi-Join, the report includes all records where a record in object B is related to a record in object A. Using the example above, the resulting report would include these records:
|
Equi-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN4 | Accordion | Madeline Morris
Sissy Smith |