Dynamics AX Hints: 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.

Solution
1. Let's run the test job, T-SQL statement is:

SELECT T1.ITEMGROUPID, T1.DATAAREAID, T1.RECID 
FROM INVENTITEMGROUP T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=N'USMF')) 
AND NOT (EXISTS 
    (SELECT 'x'
     FROM INVENTITEMGROUP T2 
     WHERE ((T2.PARTITION=?) AND ((T2.DATAAREAID=N'USRT') 
      AND (T2.ITEMGROUPID=T1.ITEMGROUPID AND (T2.DATAAREAID=T1.DATAAREAID) AND (T2.PARTITION=T1.PARTITION))))))

Let's ignore PARTITION predicate so far and pay attention to the following predicate:
T2.DATAAREAID = T1.DATAAREAID
The predicate makes NOT EXISTS predicate always true, it means that the query selects all records of InventItemGroup table in USMF company regardless of existing records in USRT company. It is not what is expected.

2. Let's change the test job by modifying the line:
where inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId
to:
where inventItemGroupJoin.ItemGroupId != inventItemGroup.ItemGroupId
T-SQL statement looks different:

SELECT T1.ITEMGROUPID, T1.DATAAREAID, T1.RECID 
FROM INVENTITEMGROUP T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=N'USMF')) 
AND NOT (EXISTS 
    (SELECT 'x'
     FROM INVENTITEMGROUP T2 
     WHERE ((T2.PARTITION=?) AND ((T2.DATAAREAID=N'USRT') 
      AND (T2.ITEMGROUPID<>T1.ITEMGROUPID AND (T2.PARTITION=T1.PARTITION))))))

The strange DATAAREAID predicate is gone.
Note: the same result is returned for other operators in predicate:
>, >=, <, <= .

3. Let's change the test job by modifying the line:
where inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId
to:
where inventItemGroupJoin.Name == inventItemGroup.Name
The results are the same.

Note: the same results are returned for other join types: exists, join, outer.

4. Let's try to implement the same logic with Query class, the job can look like:

static void CrossCompanyQueryTest(Args _args)
{
    Query                   q;
    QueryBuildDataSource    qbdsInventItemGroup, qbdsInventItemGroupJoin;
    QueryRun                qr;
    
    q = new Query();
    q.allowCrossCompany(true);
    //q.addCompanyRange('USMF');
    //q.addCompanyRange('USRT');
    
    qbdsInventItemGroup = q.addDataSource(tableNum(InventItemGroup));
    qbdsInventItemGroup.company('USMF');
    
    qbdsInventItemGroupJoin = qbdsInventItemGroup.addDataSource(tableNum(InventItemGroup));
    qbdsInventItemGroupJoin.company('USRT');
    qbdsInventItemGroupJoin.joinMode(JoinMode::NoExistsJoin);
    qbdsInventItemGroupJoin.relations(true);
    
    qr = new QueryRun(q);
    while (qr.next())
    {
        // create new record in USRT company
    }
}

The job results in an error:
Filtering at the data source level is not allowed for a cross-company query.

Actually cross-company Query class limitations are described in msdn, but nothing is mentioned for select statement.

Conclusion
Although no error is thrown, different company filtering per table in cross-company select statement with join is not supported. There is a solution for the problem and it will be described in the next post.

Dynamics AX Hints

24 August 2015

Non-documented feature of cross-company keyword

2 comments: