Dynamics AX Hints: query

22 January 2016

How to restrict view datasource fields

A view is specified by a query and there are two ways to define the view query - add existing query or add datasources to Metadata node:

Both options deal with datasources. It is recommended to restrict datasource fields and return only the fields which are actually used. Unused fields generate useless traffic and decrease performance. Is such recommendation relevant for the view Metadata?

Hints
Analyse the view CREATE script in SQL Server Management Studio. You can also jump to the conclusion section.

24 August 2015

Non-documented feature of cross-company keyword

My colleague asked my advise about cross-company query. The goal was to create missing records of customer specific table in another company. The query looked correct, however the tests failed.

Problem description
To make it simple let's assume that records of InventItemGroup table in company USMF must be created in company USRT (provided the records are missing in USRT company). It seems to be possible to use cross-company query like this:

InventItemGroup inventItemGroup, inventItemGroupJoin;
    
while select crossCompany inventItemGroup 
    where inventItemGroup.dataAreaId      == 'USMF'
    notExists join inventItemGroupJoin
    where inventItemGroupJoin.dataAreaId  == 'USRT'
       && inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId
{
    // create new record in USRT company
}

This approach is wrong though. Are you also curious why? Let's analyse some specifics of cross-company queries.

Steps to reproduce
The following job can be used for tests and analysis:

static void CrossCompanyTest(Args _args)
{
    InventItemGroup inventItemGroup, inventItemGroupJoin;
    
    select generateonly forceLiterals crossCompany ItemGroupId 
        from inventItemGroup
        where inventItemGroup.dataAreaId      == 'USMF'
        notExists join inventItemGroupJoin
        where inventItemGroupJoin.dataAreaId  == 'USRT'
           && inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId;
    
    info(inventItemGroup.getSQLStatement());
}

Hints
Try to modify predicates of join and use Query class instead of select statement.