How to Specify Joins for Tables with Multiple Relationships

You can specify the fields you want to use to join tables when the tables share more than one relationship.

Some tables are linked together in the database using more than one field. For example, incidents can link to accounts through three fields in the incidents table. When joining these tables, the link between the tables isn't automatically created, so you must define which field should be used to tie the tables together in the report. You’ll do this on the Select Join window, which automatically opens when you add fields from tables such as this to the same report.

The Select Join window also appears if your report already has multiple tables and you add a field from another table that can be related to more than one of the tables already in the report. In this case, you must define which table the new table should be joined to, since different data can be returned by joining different tables. By default, the new table is joined to the primary table in the report using an inner join.

An example that illustrates both cases is if your report includes the incidents and contacts tables, and you then add a field from the accounts table. The accounts table can link to the incidents table through three incident fields and can link to the contacts table through one contact field. In this instance, you would be asked if the accounts table should link to incidents or contacts. If you select the incidents table, you will be asked which field in the table should be used as the link.