Tuesday, February 17, 2015

Datasource link types.


LinkType

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.
Link types in AX can be divided in two groups. Active, Delayed and Passive are 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 for the relevant record.

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.
For less application specific joins of datasources, there are four more flavors. Innerjoin,, OuterJoin, ExistJoin and NotExistJoin

InnerJoin

Classic inner join of two collections (parent and child data source).

INNER JOIN
This one is so trivial that even the makers of AX couldn't get it wrong. This is the first and last join name that we can agree on.


 

OuterJoin

All records from the parent data source that do NOT have a corresponding record in the child data source.

LEFT JOIN
In the more civilized regions of the SQL sphere these are called left joins, but that was of no concern to the boys at Damgaard. Microsoft has not bothered to change it so far or just never noticed it hard enough.
 


ExistJoin

Selects ony the records from the parent data source that have a reference to a child.

LEFT INNER JOIN ?
I’m not sure if such a thing as a left inner join exists or should exist. Maybe that’s how the DAX developers came up with the name for this conjunction.
The main benefit is speed. Pure and simple beautiful speed. So if you require a subset of your table and you have another table that you can use as a cake knife, then this is the way to go.

NotExistJoin

Selects only the records from the parent data source that do not have a reference to a child. This one was a mystery to me, until I bothered to build the example below. I found that every single description I ever googled about this link type was wrong. This is simply a…..

LEFT OUTER JOIN
..would be the proper name for this link type in my humble opinion.
Wouldn't it be nice if we could all just get along?




 

Don’t believe me?
Take a look at the following example.

TABLE A
TABLE B
CustomerID
Name
10
Red
20
Blue
30
Green
40
Pink
50
Black

OrderID
CustomerID
Item
101
10
Pepsi
102
10
Coke
103
10
Sprite
104
20
Dr. Pepper
105
30
7-Up
106
40
Mountain D
107
40
Fanta
108
60
Jolt
109
70
Fresca


Two simple tables that I will use as the datasources for an equally simple form using all five fields from the two tables. Linked on customer ID:


INNER JOIN
No surprises here. Only the records that exist in both tables are shown. Customer ID 50, 60 and 70 are left out.



OUTER JOIN
The same result as the inner join, plus Customer ID 50 who has never ordered pop in his life. This is by all means and conventions a left join, Microsoft.



EXIST JOIN
Here we see the customers from the inner join, but none of the precious sodas. It's an inner join light.



NOT EXIST JOIN
And finally, there is our lost customer. The one that got away. The one with its own secret soda fountain. Ever felt the need to track those customers who just fill up your CustTable without ever placing an order? AX makes it easy for you. I call this a left outer join and I dare anyone in Redmond to prove me wrong.




And there you have it. These are the flavors AX has to offer you. If you need more, then you are doing it wrong.

Enjoin!

No comments:

Post a Comment