I’m using AX 2012 R1.
I’m trying to do cost analysis on items based on their worst (longest) route.
Each item can have one or many routes, each route can have one or more operations.
I need to find for each item which route is the worst. I define this as the route that has the most operations, or in the case of a tie, the one that was created first.
I’ve created a view that joins item and route and has the count of the operations in the route. This is called tem_itemRoutes.
I can create SQL that can give me the correct results:
select iRoutes1.itemid, iRoutes1.ROUTEID from tem_itemRoutes iRoutes1
where not exists (select * from tem_itemRoutes iRoutes2
where (iRoutes2.itemid = iRoutes1.itemid) and
(iRoutes2.COUNTOFOPRID > iRoutes1.COUNTOFOPRID) or ((iRoutes2.COUNTOFOPRID = iRoutes1.COUNTOFOPRID) and (iRoutes2.ROUTECREATION < iRoutes1.ROUTECREATION)))
However, I would like this query to be a view inside AX and I cannot figure out how to translate it. I’ve created a SQL query with two instances of the tem_itemRoutes view, the joinMode being NoExistsJoin. But when I go to the relations, it only allows me to use the == operation. Is there a way to use a ‘>’ operation? With just ‘==’, I can add a relation to match the views based on itemid, but I see no way to add a relation to match based on one count being higher than the other, or based on one creation date being lower than the other. I’ve been trying this in the AOT which is a bit harder to use, but if someone knows how to do this in the view’s init method that would work too.
