OuterJoin Problems


I have Order Table and Order Lines table. I have a ListPage which shows the OrderTable records. I want to add OrderLines to the query and list page. When I do this, I get a duplicate record of OrderTable for each orderLines table.

I have the query datasource OrderLines set to FirstOnly true, however I still get duplicated header records.

Tips? Tricks?


To note, I want a single OrderTable record in the grid regardless if 0 OrderLines or many. But, I’d like the ‘most recent’ order Line status to show in the OrderTable.LastLineStatus field. This also has to be filterable, thus the need to add the child datasource to the parent grid.

That’s the correct behavior of outer join. If you have one order with two lines and one order without any lines, database will return something like this:

OrderTable.ID LineTable.OrderId LineTable.LineNum
Order1 Order1 1
Order1 Order1 2

AX will just translate that to its objects.

You could use GROUP BY for fields you’re interested in and the MAX() aggregation function for line status. Or maybe a computed column in view (in AX2012) would give you what you need.

I was lucky enough to develop a quick enhancement to allow users to modify their existing Cues, and helped the users setup cues based on Advanced Filters.

The original tables were ProdTable and InventQualityOrderTable. Users wished to have the latest quality order status displayed alongside the production order in the list page, but this was not possible due to the 1:n relationship.

Cues, with the ability to modify them on the fly as needed was my workaround in this case, although I am intrigued by your comment about the computed column in views. Will have to give that a shot!

Grazie come al solito, Martin!