Joining Tables
The connection between two tables is called a join and is established using a field that exists in both tables. This field may 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 open the Local Join dialog box
- Click a field in the Query Builder, and then 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 contains predefined global joins (system joins) that cannot be deleted. Anyone may use the predefined global joins, but only the system administrator or administrative user can create global joins using the Global Join Manager.
To open the Global Join Manager
- Click the Joins button 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 either table may be used in a query. These queries are built using the Query Builder.
In the Query Builder data tree view, a join looks like this:
|
|
By default, the ADDRESS table is joined to the ACCOUNT table via an Inner join. Do not change the properties of this join. |
