Cross Company Query

Sometimes we need to fetch data from several companies.


Straight forward way is to use a crossCompany keyword in a
select statement:
container
conCompanies = ['cee', 'cec', 'ceu'];


while select crossCompany : conCompanies * from CustTable
{
...
}


More elegant and flexible method to select cross company would be to use a query with a parameter AllowCrossCompany set to Yes.





This parameter enables additional tab in a query selection dialog which allows user to select companies.

In the same manner cross-company fetch of data can be done in X++:

static void crossCompanyQuery(Args _args)

{
Query q = new Query();
QueryBuildDataSource qbds;

QueryRun qr;

CustTable ct;

Container con;

;

qbds = q.addDataSource(tableNum(CustTable));


qbds.addSortField(fieldNum(CustTable, dataAreaId));

// by default adds all companies to which current user has access rights

q.allowCrossCompany(NoYes::Yes);


//q.addCompanyRange("cec");
// <- in case if you need to add more companies


con = q.getCompanyRange();
// <- check which companies are in the range


qr = new QueryRun(q);


while (qr.next())

{

if (qr.changed(tableNum(CustTable)))

{

ct = qr.get(tableNum(CustTable));

info(strFmt("%1 %2", ct.dataAreaId, ct.AccountNum));
}
}
}