4

I came across a problem in AX 2009 and I have to admit I'm basically clueless.

I would like to build a view (based on an AOT query or not; but from what I understand you can do more using an AOT query than strictly with a view) using a table which looks like this:

id    status    date
1     IN        10/10/2011
1     OUT       11/10/2011
2     OUT       12/10/2011
3     IN        13/10/2011
4     IN        14/10/2011
4     OUT       15/10/2011

The view would ideally look like this:

id   IN_Date      OUT_Date  
1    10/10/2011   11/10/2011
2    *NULL*       12/10/2011
3    13/10/2011   *NULL*
4    14/10/2011   15/10/2011

In strict SQL or even using Microsoft Access it's a trivial task to do but I can't find a way to do it in AX 2009 since there is no "AS" option in views' fields. I don't want to use display methods since I want the view to be accessed from outside of AX. Any hint would be greatly appreciated!

Max
  • 3,453
  • 3
  • 32
  • 50

1 Answers1

3

You would like to do a full outer join on the table (joined with itself).

This is not supported in X++ or as a query joinMode, but can be simulated using two intermediate views with outer join combined with a union.

View 1: 

select id, date as dateIn from table as table1 
    outer join date as dateOut from table as table2
    where table2.id == table1.id and
          table1.status == Status::In and
          table2.status == Status::Out

View 2: 

select id, date as dateOut from table as table1 
    outer join date as dateIn from table as table2
    where table2.id == table1.id and
          table1.status == Status::Out and
          table2.status == Status::In

View 3: 

select id, dateIn, dateOut from view1 
union 
select id, dateIn, dateOut from view2

The above is more or less SQL, which can be turned into AX queries and views.

A way to do that is given in this answer.

Community
  • 1
  • 1
Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
  • Thank you for your answer. It is a good one but it can become overwhelming if at some point a new status is added and we need another column. I found a way to do what I wanted by using an AOT query. – Max Jul 04 '12 at 13:10
  • How did you do that? Please tell. Adding outer status would not impact the views, as they are concerned with In and Out only. A simpler solution would add two dates to the table and do away with the status. Then there would be no problems if there are several In or Out with the same id. – Jan B. Kjeldsen Jul 04 '12 at 13:13
  • 1
    I created a query with a main datasource without any range on it. I added a sub-datasource for each possible state (i.e. columns). Each sub-datasource was joined using OuterJoin, Firstonly et 1:1 relationship. Of course, each sub-datasource was linked to the main-datasource using their unique id, and had a range on their status (a different range for each sub-datasource). On the view, I used the date fields of each sub-datasource with MIN aggregate on them. I did it that way since in my case I can have several lines on the same status with different dates (and I want the oldest date). – Max Jul 04 '12 at 13:38
  • PS: I wanted to explain how I did that in my first comment but by pressing the enter key it submitted my comment. I wanted to edit it but you have 5 min to do so once you've submitted it :d – Max Jul 04 '12 at 13:41
  • Also (sorry to flood the comment thread), by adding status I meant adding a status to the current list {IN, OUT} to get something like {IN, OUT, OVER, X, STARTED} – Max Jul 04 '12 at 13:48
  • @RedPolygon you should consider posting this solution as an answer! See [Posting and answering questions you have already found the answer to](http://meta.stackexchange.com/questions/2706/posting-and-answering-questions-you-have-already-found-the-answer-to) – user85421 Jul 09 '12 at 07:40