Define Join Relationships
Object relationships are defined by specifying fields to link in (a maximum of three) objects.
Join Theory
In a join, choose at least one object that contains an ownership record. Good reasons to do this:
- The Filter by Record Ownership option is commonly used for Reports based on Database Views.
- Visibility of Reports in folders can also be managed with record ownership
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. Three join types are available:
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 records 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 |