Recently one of my assignments, I have to explore and use Cross company queries to fetch data. Although cross company queries have performance overhead, for example, if my custom AIF service return result in 4 seconds , Cross company returns in 30 seconds. I found multiple design pattern, The fastest cross company query are by setting allow cross property to set true in static AOT Query or by setting Adding In Query Object. Lets explore these patterns
Static AOT Query:
You can set Allow cross company true By properties of AOT Query .
X++ Query object:
One way to create query in dynamics Ax is Query object. You can add by following way.
Query query; QueryRun queryRun; QueryBuildRange queryBuildRange; QueryBuildDataSource queryBuildDataSource; query = new Query(); queryBuildDataSource = query.addDataSource(TableNum(CustTable)); query.allowCrossCompany(true); //Executes query built so far queryRun = new QueryRun(query); queryRun.allowCrossCompany(true);
This is most fastest way to get cross company data.
While select Query:
There two ways for Get cross Company query and or Query inside the change company statement.
Cross company Keyword:
Add cross company query inside the while select statement to extract data all legal entities in Dynamics AX as follow code.
Custtable _CustTable;
While select crosscompany * from _Custtable
{
Info (_Custtable.AccountNum + “ : “ + int642str(_Custtable.DataAreaId);
}
If you want to restrict the query to fetch data from limited legal Entities you have to use a container as follow.
Container _ListOfLegalEntites ={ ‘mazang’,’Jubergi’,’Urdu Bazar’};
While select crosscompany:_ListOfLegalEntites * from _Cust
{
Info(_Custtable.AccountNum + “ : “ + _Custtable.DataAreaId);
}
Change Company Keyword:
One method is to use change company keyword. It is most time consuming method. To reduce its time, we have rest the table to null when Change Company happened. In Dynamics All legal entities name stored at DataArea table we use this table to get the list of datarea and used inside the changeCompany query on it as follow.
DataArea _DataArea;
Custtable _Custtable;
While select Id from _DataArea
changeCompany(_DataArea.Id)
{
_Custtable= null;
While select * from _Custtable
{
Info(_Custtable.AccountNum +” : “ + _Custtable.AreaId);
}
}
If you did not reset the table buffer , result will be too heavy, reason buffer contains old data as well. To reset or set null inside the change company statement and before query on buffer will be performance bust as well as design pattern.