Dynamics AX Hints: August 2015

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.