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
• Right-Join
• Equi-Join

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