Joining tables

The connection between two tables is called a join and is established using a field that exists in both tables. This field can have a different name in each table but contains the same information. There are two kinds of joins: Local and Global.

Local Joins

A local join is temporary. It only exists while the object or query that uses the join is active or running. Local joins are not visible from any data tree view in Query Builder.

To access the Local Join window, click a field in the Query Builder, and click Create Local Join.

Global Joins

A global join is permanent and exists from session to session in the Query Builder data tree structure. Infor CRM SLX contains predefined global joins (system joins) that cannot be deleted. Anyone can use the predefined global joins, however only the system administrator or administrative user can create global joins using the Global Join Manager. See Global Join Manager.

To access the Global Join Manager, click Joins in Query Builder (while logged on as the administrator).

Working with Joins

When creating a join you must identify a parent table and a child table. The type of join determines what information is returned from the query. There are three types of joins, Inner, Left, and Right.

For example, the CONTACT and ACCOUNT tables both contain the AccountID field. As a result, you can use the AccountID to join the ACCOUNT and CONTACT tables.

You can also join tables with fields that have a different name, but the same value. For example, ACCOUNT table is joined to the USERINFO table through the AccountManagerID field in the ACCOUNT table, and the UserID field in the USERINFO table. Although the name of the fields in each table is different, the value in the fields is the same.

Generally, joins are used to build queries that run against a database. After a join is created, any field in table can be used in a query. These queries are built using the query builder. See What is Query Builder?

In the Query Builder data tree view, a join looks like this:

Note: By default, the ADDRESS table is joined to the ACCOUNT table through an Inner join. Do not change the properties of this join.