How to read records from different company accounts with one select statement

A Dynamics Ax installation can hold information for different company accounts, all in one database. If you wanna share information between different company accounts, you can use Table Collections and Virtual Companies. Maybe you can use the InterCompany module of Ax, in order to automate the information exchange between different company accounts in your setup.

But even if the data is not shared and you don't use InterCompany, you can retrieve data from different company accounts with a simple select statement. In Ax 2009, you can use the crossCompany keyword for this.
All you have to do is add this keyword to your select statement.
Code sample 1: Select records from all company accounts in the database.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;
;

while select crossCompany CustTable
order by AccountNum asc
{
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));
}
}

In code sample 2, we limit the company accounts we want to read from.
For this, we can add a container after the crossCompany keyword, that limits the company accounts. The container holds the different wanted company identifiers.

static void CrossCompanyTest(Args _args)
{  CustTable CustTable;
container companyAccountcontainer = ['ca1','ca2'];
;
while select crossCompany : companyAccountcontainer CustTable
order by AccountNum asc
{
info(strfmt('%3 - %1 %2',CustTable.AccountNum,CustTable.Name,CustTable.dataAreaId));
}
}

Note that you cannot use the crossCompany keyword with data modification commands like insert_recordset or delete_from. But with a little workaround and the help of the changeCompany statement, you can still use the crossCompany keyword. Together, they can perform the data updates. More of that later on...