Wednesday, September 24, 2014

Form data source link types - Active, Delay, Passive, Inner join, Outer join, Exist join, Not exist join

Form data source link types

Form data source link type is a property of the form data source. We can add more than one tables as a data source to the form. Those data sources should has the table level relation, So, then the developer no need to work on the coding part to find the relation records. For example, if we create the order form, that order form has orders and order details tables as form datasources. We can add both tables as a data sources to the form.

The parent table and child table should has the table relation. So, once we add these tables in the form as data sources. We can select the child table data source and mention the parent table name in the join source property of the child table form data source property.

Example:

Here, I have created two tables Hari_Order and Hari_OrderDetails. Hari_OrderDetails has the foreign key of Hari_Order table Key is OrderNo.

Hari_Order table


Order No
Customer Name
Ord 2
Ram
Ord 1
Hari
Ord 3
Vithyaa
Ord 4
Uma

Hari_OrderDetails

Order No
Product Name
Product Description
Ord 1
Prod 1
Product One
Ord 1
Prod 2
Product Two
Ord 1
Prod 3
Product Three
Ord 2
Prod 1
Product One
Ord 2
Prod 2
Product Two
Ord 3
Prod 1
Product One

Set the join source and set the link type

Table relation


Use join source and link type


Active

Active link type update the child data sources without any delay when you select the parent table record. When you deal with more records it will be affect application performance.


Delay

Delay form data source link type is also same as active method the different is delay method won't update immediately when you select the parent record. It will update the child data source when you select the parent table, Ax use pause statement before update the child data source. For example, if we are dealing with lot of records so, when the user click or scroll the order, order details will update without any delay if we use active method.

So, We can use delay method because of performance improvement.



Passive

Passive form data source link type won't update the child data source automatically. For example if we select the parent table order then order details child data source won't update. If we need to update the child data source we need to call the child data source execute query method by program (code).



The order details grid is empty. If we need populate the child data source (order details) then we need to call the Hari_OrderDetails_ds.executeQuery() method in the parent table Hari_Order form data source active method. We can add separate button "Populate order details" and call the code Hari_OrderDetails_ds.executeQuery(). So, if the user need to see the order details then the user update by click the "Populate order details" button.


Inner join

Inner join form data source link type displays the rows that match with parent table and child table. For example if the order doesn't has any order details then the order will not be display.

Here, Order 4 does not has the order details records, so it is not coming in the order grid.


Outer join

Outer join form data source link type will return all parent records and matched child records. It will return all rows in the parent table. Here, Order 4 doesn't has the child records (order details) but it is populating in the order grid. It is the difference between inner join and outer join.

Here, Order 4 is coming even order 4 does not has the order details.



Exist join

Exist join form data source link type return matched rows of the parent table. It behaves like inner join but the different is once parent row matched with child records then stop the process and update in the grid, Ax won't consider how many records in child table for the parent row.

Here, Order 4 is not coming because Order 4 does not has the order details.


Not exist join


Not exist join form data source link type is totally opposite method to exist join. It will return the not matched parent records with child records.

Here, Order 4 is coming because order 4 does not has the order details.



Each form data source link type has different behavior. Active, Delay and Passive are one category and Inner join, Outer join, Exist join, Not exist join are another category. So, please understand the behavior and choose the link type based on your requirement.


Thanks,
Hari

2 comments :

  1. Superb Recipe and it is very clear to understand. Waiting for more valuable posts.

    ReplyDelete
  2. By mixing these two orthogonal concepts (passive, active, delayed are child retrieval strategies, the others determine which records get returned), AX has eliminated half our possibilities, since in the first group we must still determine which records get returned but AX doesn't give us the option of choosing that. In the first group, someone must still determine whether parents without children get included (outer join) or excluded (inner join). Your examples imply that the first three are all outer joins, since "Ord 4" appears despite its having no detail records. What happens if you want a delayed inner join (e.g. in this case you want the form to display only parents with children)? I suppose you must then separately control the query that delivers the parent records (i.e. you would restrict it separately using an "exists" join)?

    ReplyDelete