It seems easy enough, but when it comes to link types in AX, the noise is overwhelming and makes it hard to understand the simple facts. This may be due in part to the lets say unorthodox naming and gaming of this datasource property.
So let me try to clear things up once and for all.
Parent-child types
Link types in AX can be divided in two groups. The first group consists of the parent-child types. Active, Delayed and Passive are used specifically for one-to-many relations. Records in the child data source are retrieved only for the current (selected) record in the parent data source. This makes these link types unsuitable for use in grids, but only for use in header/line type forms such as Sales orders.
Active
The child data source is updated immediately when a new record in the parent source is selected. Not suitable for high-scroll-level forms, such as anything with a grid.
Delayed
There is a literal delay in synchronizing the child data source with the parents. This allows for fast scrolling through the parent data source and ony retrieve child data once the relevant record is selected.
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.
Collection types
The collection types (Innerjoin,, OuterJoin, ExistJoin and NotExistJoin) are more like your regular SQL relationships and consistent with the same types in SQL server. That said, I still have to frown sometimes over the results from joins in MS-SQL.
Innerjoin
This one is so trivial that not even the masterminds in Redmond could obscure it. It only returns records of which the selected value exists both in data source A AND in data source B, as depicted in the Venn Diagram.
Outerjoin
From here it gets dangerous, because what AX refers to as an outerjoin is what I would call a left join and Wikipedia calls a left outer join. Be that as it may, this is consistent with MS-SQL, where an unspecified outer join is considered a left outer join and this is exactly what AX does. It returns all records from data source A, but only the ones from data source B which are in the cross section.
Existjoin
The existjoin is really not a join at all, because it doesn’t join anything. It gives you only those records of datasource A which have an equivalent in data source B. The big difference with the innerjoin is that it does not return any records from data source B. Essentially data source B functions as a cake knife to chop off the intersection of the two data sources from data source A. Best thing is that it does this, really, really fast, which makes it a great tool to use (only!) when the situation calls for it.
Notexistjoin
The notexistjoin would be the equivalent of a left outer join (or at least what I would call a left outer join…and I’m not alone, Wikipedia!), if it wasn’t for the fat that, as with the existjoin, it is not a join at all. Again, data source B serves as a cake knife, but this time it doesn’t return the piece it cut, but the remainder of the cake (oh boy, oh boy, oh boy!). Again, because only one data source has to be returned, it can be a huge performance booster. Use it wisely, but use it when you can.
Test
So to put everything to the test, let’s start with two tables, table A containing 5 customers, table B containing 9 orders.
TABLE A
|
TABLE B
| ||||||||||||||||||||||||||||||||||||||||||
|
|
Next I create a simple form that uses both tables as a data source. I link the two data sources on the common customer ID field. Let’s see what happens if I select different link types.
Innerjoin
No surprises here. Only the records with a customer ID that exists in both tables are displayed. Customer ID’s 50, 60 and 70 are not shown because 50 never ordered. And 60 and 70 don’t exist in our customer base.
Outerjoin
The same result as the inner join, plus Customer ID 50 who has never ordered pop in his life. 60 and 70 are still missing because they don’t exist in data source A (i.e. they don’t exist as customers).
Existjoin
Indeed we get the same records from data source A, but none of the records from data source B. The cake knife, or cookie cutter, did its work.
Notexistjoin
Finally, the reverse cake knife at work. All records from data source A, that do not have an equivalent in data source B, but only data source A and nothing from B.
These are all the flavors AX has to offer. It is not the full pallet of join types, but it is enough to get by. Remember to use the cookie-cutter when you can. It doesn’t just safe processing time in the database, but it also saves on client-server communication.
Enjoin!