View with query outer join in Dynamics AX

View with query outer join in Dynamics AX :

More on Using Views in Microsoft Dynamics AX 2012

I think of Views as a type of read-only temporary table. The fields and data are not stored in the database but created at runtime. Like temp tables, they may contain fields from multiple tables and/or fields based on complex business logic. The thing is, AX thinks of them much like tables too, and you can therefore do a lot of things with Views that you can do with Tables. Examples are; joining Views to tables and to other views, using Views in Queries, Forms and Reports, even writing Select statements against them in code.

Lately, I have been exploring different ways to utilize Views in AX, and here are a few things I have learned along the way.

1. Use a View to test a Query

Designing complex queries in the AOT can be a tedious and time-consuming process. Once completed, running the query object from the AOT does not display a resultset, so testing can be a challenge as well. I used to write code like this to test and make sure I was getting the expected resultset from my query:
QueryRun                    qr;
Query                       q;
CustTable                   custTable;
CustTrans                   custTrans;

qr = new QueryRun(queryStr(CustTable));

if (qr.prompt())
{
while (qr.next())
{

CustTable = qr.get(TableNum(CustTable));
CustTrans = qr.get(TableNum(CustTrans));
info(CustTable.AccountNum + “-” + num2str(CustTrans.amountCur,0,2,1,2));

}
}
A much easier way to do this is to simply create a new View in the AOT and drag your query to it. In the example below, I created the MyCustView View and dragged the CustTable query to the Metadata node.

Now I can simply add any field I want from any datasource in the query, save, sychronize and run the view from the AOT to view my results.

2. Base your Views on Queries to allow for joins, filters and grouping

Views only support inner joins. There is no way to specify any other join type. Instead of basing your views on tables, build a query in the AOT. Specify JoinMode, Relations and Ranges if desired. Then drag the Query to the Metadata node of the View and select some fields to show in the View (as in #1 above).

For example, the query used in #1 is CustTable. I changed the base query to use an Outer Join. Note the JoinMode and Relations properties are grayed out in the view, but the Join Mode was specified on the query so still applies. The relations established in the Query also apply here.

Keeping in mind that Queries can be made up of Tables, Maps, and Views, the possibilities are endless. Here is an example in base AX of a View based on a Query, and the Query contains multiple Views itself.

3. Create cross-company Views

Simply create a query as below with the AllowCrossCompany property to Yes. Drag the Query to a View and voila!

4. Solve complex join problems by using Views and joining to Tables and other Views

I recently had a need to use a groupby field to join an additional table, like this…
qbdsWorksheetTable   = query.addDataSource(tableNum(MCAShippingWorksheetTable));
qbdsWorksheetTable.addRange(fieldNum(MCAShippingWorksheetTable,WorksheetId)).value(_worksheetId);
qbdsWorksheetLine   = qbdsWorksheetTable.addDataSource(tableNum(MCAShippingWorksheetLine));
qbdsWorksheetLine.relations(true);
qbdsWorksheetLine.addGroupByField(fieldNum(MCAShippingWorksheetLine,ProjId));
// All of the above works great. I get everything grouped by ProjId
qbdsPallet      = qbdsWorksheetLine.addDataSource(tableNum(wmsPallet));
qbdsPallet.relations(false);
qbdsPallet.joinMode(JoinMode::InnerJoin);
qbdsPallet.addlink(fieldNum(wmsPallet,MCAJobId),fieldNum(MCAShippingWorksheetLine,ProjId));

I want to use ProjId from MCAShippingWorksheetLine to join in the WMSPallet table. This just does not work when ProjId is the GroupBy field. The query returns no records at all. I suspect there may be a couple of ways around this, but instead of wasting a lot of time, I decided to use a simple View for the GroupBy portion of this query. Piece of cake!

Then I was able to use the View in my X++ query, joining the WMSPallet table to the View. (MCAWorksheet_ProjId is my View)

qbdsWorksheet   = query.addDataSource(tableNum(MCAWorksheet_ProjId));
qbdsWorksheet.addRange(fieldNum(MCAWorksheet_ProjId,WorksheetId)).value(_worksheetId);

qbdsPallet      = qbdsWorksheet.addDataSource(tableNum(wmsPallet));
qbdsPallet.relations(false);
qbdsPallet.joinMode(JoinMode::InnerJoin);
qbdsPallet.addlink(fieldNum(MCAWorksheet_ProjId,ProjId),fieldNum(wmsPallet,MCAJobId));