1,800
Like
Share
ReportHello AX World,
You could easily and unintentionally cause a breaking change while introducing a new relation. It could break any other query in the system meaning it could break any functionality using those queries.
We had a recent case where a standard query stopped working because of a new relation added with a 3rd party solution. It can happen to any code.
The problem is that there is no information whatsoever how the relations are automatically picked. Not in official technical references, nor I found it in community posts. I have seen both old and new posts on this issue and none had answers to it. So I went on a little adventure of finding out how it truly works and explain it to you. And I think I managed to figure that out.
This applies to all queries that use relations which are determined automatically:
- AOT queries that has Use Relation set to Yes on its data source.
- query build framework queries that use relations(true) method.
- all form data source queries which relations use dynalinks.
- and any other similar queries.
Here is a summary of how a relation is automatically picked for the two joined tables in the descending priority order:
1. Child-to-parent over parent-to-child
2. Original model over extensions
3. Extensions in alphabetical order
4. Top relation in the table or table extension
The Tests
This is how I figured that out.
Consider we have a simple query joining two tables TableA and TableB with Use Relations set to Yes.
TableA is a parent and TableB is a child in this relation.
When Use Relations set to Yes the relation will be selected by the system. But how?
Where should the relations be defined for the auto relations to work?
Consider we have a relation in TableA to TableB (parent-to-child).
The result of having this relation would result in this query.
JOIN * FROM TableB(TableB)
ON TableA.A1 = TableB.B1
Two relations
Let's add another relation in TableA to TableB bellow it.
This is the query now.
JOIN * FROM TableB(TableB)
ON TableA.A1 = TableB.B1
The topmost relation is picked.
Parent-to-child vs. child-to-parent
What if we had another relation in TableB to TableA (child-to-parent)?
The result of having this relation would result in this query.
JOIN * FROM TableB(TableB)
ON TableA.A3 = TableB.B3
The new relation is picked. It seems like a child-to-parent relation is prioritized over parent-to-child relation. It does make sense.
To prove that lets switch the parent and child in our query.
Now TableB is a parent and TableA is a child.
The result of switching parent to child.
JOIN * FROM TableA(TableA)
ON TableB.B1 = TableA.A1
Now the topmost relation of TableA has been picked. So, it proves that the child-to-parent relation is prioritized over parent-to-child.
Extensions
Let's level up the complexity.
We switch back to the query #1 where TableA is a parent and TableB is a child.
Consider we have only relations in TableA to TableB. And we remove relations from TableB to TableA
Create a new model EvaldasTestingRelationsExt1 and extend the TableB by adding a relation to TableA.
The result of such change will be this.
JOIN * FROM TableB(TableB)
ON TableA.A1 = TableB.ExtB4
The relation in the EvaldasTestingRelationsExt1 extension of TableB has been prioritized as its the only child-to-parent relation.
Do you see where I am going? You could unknowingly introduce breaking changes if you don't know how relations work.
OK, what if we had a relation in TableB to TableA in the original model.
The result of this.
JOIN * FROM TableB(TableB)
ON TableA.A3 = TableB.B3
The relation of original model in TableB to TableA is picked. This behaviour has changed since last time I wrote about relations and the picking order of them in this blog post. That's good!
Two extensions
OK, let's remove that for now and let's have only the relation in TableA to TableB.
Let's create another model EvaldasTestingRelationsExt2 and extend the TableB by adding a relation to TableA too.
So now we have two child-to-parent relations in different models.
Which one will be picked? Let's run the query.
JOIN * FROM TableB(TableB)
ON TableA.A1 = TableB.ExtB4
The relation of EvaldasTestingRelationsExt1 has been picked.
I will not do any further tests and just confirm that the order is alphabetical. Model name will define which relation will be picked.
Conclusion
So, relations are automatically picked in the following descending priority order:
1. Child-to-parent over parent-to-child
2. Original model over extensions
3. Extensions in alphabetical order
4. Top relation in the table or table extension
Disclaimer: This is purely based on my tests, it is not official information, and I might have missed something, but I did a lot of tests, and every test confirmed my assumption.
Recomendations
Based on the findings I would recommend the following:
Be very cautious where you define a new relation. It might seem a harmless one, but it could affect auto relations. Consider all possible queries between the parent and child table and all relations between them. If there is a risk to break the relation, consider using a separate table instead of adding fields and relations in the table extension.
When joining two tables try to avoid auto-relations whenever possible. Define which relation you would like to use.
Let's break a relation
Shall we break some relations?
VendorPaymentsOpen was a query that has been broken when a relation was added in an extension.
Standard code has a relation to DirPartyTable define in VendTable and that make sense, since Vendors are parties. In the forementioned query this would be a parent-to-child relation.
Any child-to-parent relation in any extension of DirPartyTable or it's derived tables would break this relation. So, it does not have to be DirPartyTable relation, in can be other tables as well as long as they have a relation to VendTable.
The original query:
...ommited for clarity...
JOIN FIRSTFAST Name FROM DirPartyTable(DirPartyTable)
ON VendTable.Party = DirPartyTable.RecId
The query after this new child-to-parent relation was created:
...ommited for clarity...
JOIN FIRSTFAST Name FROM DirPartyTable(DirPartyTable)
ON VendTable.AccountNum = CompanyInfo.MyNewField
And because of that Vendor Payments workspace will stop working. It will not show any data in Invoice past due, and Payments not settled pages
Try to break something yourself, in DEV of course.
Be aware and take care!
*This post is locked for comments