Use of Joins and LinkType Property with Form data source in Dynamics Ax 2012

A join combines records from two or more tables in a database. A Join is a means for combining fields from two tables by using values common to each. We can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table. 

In Dynamics Ax, We can add more than one table to a form data source. If one table includes a foreign key that is the primary key of the second table, We can set a join between the two tables. The benefits of joins are:

• If we want to use both data source as single data source.
• If we want to make parent and child relation between the tables.
• If we want to specify how form data source methods run for the tables in the form data source. Form data source methods such as active, next, and executeQuery are directed to the parent table of the join.
• If we want to improve performance when insert, delete, update and select operation implement across all the tables in the join relationship.
• If we want to synchronize navigation between tables. For example, every time that you select a new record, both data sources are updated at the same time.

Types of linkType property in Ax:

• Active-The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.
• Delayed-A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately. For example, the user could be scrolling past several orders without immediately seeing each order lines.
• Passive-Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.
• InnerJoin-Selects records from the main table that have matching records in the joined table and vice versa.
• OuterJoin-Selects records from the main table whether they have matching records in the joined table.
• ExistJoin- Selects a record from the main table for each matching record in the joined table.The differences between InnerJoin and ExistJoin are as follows: 1.When the join type is ExistJoin, the search ends after the first match has been found. 2.When the join type is InnerJoin, all matching records are searched for.
• NotExistJoin-Select records from the main table that do not have a match in the joined table.

Let’s take a scenario to better understanding. First we have to create two tables with following fields and indexes according to figure 1.

Figure 1. Create two tables

And populate some record in both tables like figure 2.

Figure 2. Populate some record

Now create a form and drag-drop both table in form data source and make design according to figure 3.

Figure 3. Create a form

And set property  “JoinSource - Department”  on Employee data source like below.

Figure 4. Set JoinSource property

Finally, when we trying to set LinkType property to different types of joins we will get below results .

Figure 5.Result