How to insert records in all companies through X++ code

How to insert records through crossCompany key word:

Test-1: 

static void Job2(Args _args)
{
    TableTMP    tableTMP; // we can take a temp table for testing
    DataArea    dataArea;
    
    ttsBegin;
    while select 
        crossCompany 
            id from dataArea
    {
        changeCompany(dataArea.id)
        {
            tableTMP = null;
            tableTMP.AccountNum = "Test03";
            tableTMP.Name       = "Test3 name";
            tableTMP.insert();
        }
    }
    ttsCommit;
}

Test-2: 

Let’s try to understand how CrossCompany and ChangeComany working with a simple job in AX 2012/2009.

1) CrossCompany:

CrossCompany keyword is access data from more than one or all the companies data which company specific.

Lets see the select statements with crosscompany’s use.

All Companies through Select Statement:

This job will access all vendor records from all the companies.

static void CrossCompanyDemo(Args _args)
{
VendTable vendTable;
;
While Select CrossCompany * from vendTable
{
info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Few Companies(more than one and less than all) through Select Statement:

The below job will access the records of vendors of some specific companies.

static void CrossCompanySample(Args _args)
{
VendTable vendTable;
container con;
;
con = [ ‘TEST’, ‘AXT’, ‘DAX’];
While Select CrossCompany : Con * from vendTable
{
Info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Now, it’s time for queries objects..

Through Dynamic Query:

Lets see the use of CrossCompany in Dynamic query through a job.

Static void CrossCompanysam(Args _args)
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
VendTable vendTable;
;
query = new Query();
qbds = query.addDataSource(TableNum(VendTable));
query.allowCrossCompany(True);
query.addCompanyRange(‘TEST’); // You can ignore the range statements if you want all the companies.
query.addCompanyRange(‘DAX’);
queryRun = new QueryRun(query);
while(queryRun.next())
{
vendTable = queryRun.getNo(1);
Info(Strfmt(“%1 : %2”, vendTable.AccountNum, vendTable.dataAreaId));
}
}

Through Static AOT Query:

You can understand how to use the crosscompany in Static AOT query with the below image.

To access all the companies, you need to set AllowCrossCompany property to YES.

ChangeComany:

Aim: To find out the number of customers in each company by using ChangeCompany keyword.

Static void crosscompanyjob(Args _args)

{

Dataarea    area;

Custtable   cust = null;

;

While select area

Where area.isVirtual == noyes::No // this filtration is needed to avoid virtual companies.

{

ChangeCompany(area.id)

{

cust = null; // It is mandatory to clear the cust buffer because if you won’t clear it, cust will refer to the old data (first select(below) execution) and takes the same for all the next selections.

Select count(recid) from cust;

{

info(strfmt(“%1 — %2”, area.Id, cust.RecId));

}

}

}

}

Test- 3: 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.

Test-4: 

You can create a cross-company query by using the crossCompany keyword on the X++ select statement. You have the option of adding a container variable of company identifiers immediately after the crossCompany keyword (separated by a colon). The container restricts the selected rows to those with a dataAreaId that match a value in the container.

The following code example populates a table buffer with all the BankAccountTable rows that have a dataAreaId of either cm1 or cm2 or dat. This example assumes that the user has authority to access data for these three companies. The first two dataAreaId values that are found will be printed.

X++

static void JobDemoCrossCompany(Args _args)
{
    BankAccountTable tabBAT; // saveDataPerCompany == true.
    container conCompanies = [ 'cm1', 'cm2', 'dat' ];
    str 4 sCompanyPrevious = " "; // Maximum length is 4 characters.
    int iCountCompanies = 0;
    ;
    while select
        crossCompany
            : conCompanies
        * from tabBAT
        order by dataAreaId
    {
        if ( sCompanyPrevious != tabBAT.dataAreaId )
        {
            info( tabBAT.dataAreaId + " = tabBAT.dataAreaId" );
            iCountCompanies++;
            if ( iCountCompanies >= 2 )
            {
                break;
            }
            sCompanyPrevious = tabBAT.dataAreaId;
        }
    }
    return;
}

Query Class

In X++ code you can use the Query .allowCrossCompany property method to achieve the same result as you can with the crossCompanykeyword on a select statement. The calls to the Query .addCompanyRange method are the same as appending a container of companies to the crossCompany keyword.

You cannot perform data source level filtering by company in a cross-company query. This is why the call to qbds3 .company is commented out in the following code example.

X++

static void JobDemoAllowCrossCompany(Args _args)
{
    BankAccountTable tabBAT; // saveDataPerCompany == true.
    Query qry2;
    QueryBuildDataSource qbds3;
    QueryRun qrun4;
    str sCompanyPrevious = "   ";
    int iCountCompanies = 0;
    int iTableNumBAT;
    ;
    qry2 = new Query();
    qry2.allowCrossCompany( true );
    qry2.addCompanyRange( 'dat' );
    qry2.addCompanyRange( 'twf' );
    
    iTableNumBAT = tableNum( BankAccountTable );
    qbds3 = qry2 .addDataSource( iTableNumBAT );
    //qbds3.company( 'dat' );
    
    qrun4 = new QueryRun( qry2 );
    
    while ( qrun4.next() )
    {
        if ( qrun4.changed( iTableNumBAT ) )
        {
            tabBAT = qrun4.get( iTableNumBAT );
    
            if ( sCompanyPrevious != tabBAT.dataAreaId )
            {
                print( tabBAT.dataAreaId + " = tabBAT.dataAreaId" );
                iCountCompanies++;
                if ( iCountCompanies >= 2 )
                {
                    break;
                }
                sCompanyPrevious = tabBAT.dataAreaId;
            }
        }
    }
    pause;
    return;
}

Happy DAXing......