X++ Job to duplicate Table records in Multiple companies/ legal entities.

During development in AX some times there comes the requirement to Duplicate records of a specific Table in Multiple Companies (if the table is not shared), this requirement also occurs during Data Upgrade from AX 4.0 / 2009 to AX 2012 in the multi site activation scenario when we are requitred to remove some tables from table collection (specified during readiness step) and once the table is removed from table collection , it is required to duplicate its data in all those companies / entities which were the part of that particular table collection (virtual companies of the table collection):

there could be multiple approaches to Duplicate records in Multiple companies , the approach i will be discussing in this blog is very simple and suitable in the scenario where there is huge data (more than million of records) for better performance it can also be used if there is less data :-) .

Steps are mentioned as below:

1. First duplicate the desired table (i.e. in my case i want to duplicate data in CustVendExternalItem Table so i duplicated CustVendExternalItem Table as CopyofCustVendExternalItem, this duplicate table will be deleted after the job is done :-)).

2. For the Duplicate table i.e CopyofCustVendExternalItem (in my case) set the "Save Data Per Company" property to NO to make it shared.

3. Execute the below X++ job to fill the Duplicated table (make sure you are standing in the company which contains data) :

static void InsertIntoDuplicateTable(Args _args)

{

CustVendExternalItem testFrom;

CopyofCustVendExternalItem testTo;

insert_recordset testTo (itemid,ModuleType,CustVendRelation,ExternalItemTxt,ExternalItemId, ABCCategory, description,inventdimid)

        select itemid,ModuleType,CustVendRelation,ExternalItemTxt,ExternalItemId, ABCCategory, description,inventdimid from testFrom;

        select count(recid) from testTo;

        info(strfmt("Records : %1,Company : %2",testfrom.recid,strCompany ));

}

4. once the data is inserted in the Duplicate (Shared table), now execute the below X++ job to Duplicate Records in The required companies (The conatiner conCompanies must be set accordingly also make sure that your Duplicate table is Shared and should be deleted after the Job is done):

static void DuplicateCustVendExternalItem(Args _args)

{

CustVendExternalItem testTo;

CopyofCustVendExternalItem testFrom;

int recordCount;

Container      conCompanies = ['C1', 'C2'];

    str            strCompany;

    int            conLength, conIndex;

 conLength = conLen(conCompanies);

    conIndex = 1;

    while (conIndex <= conLength)

    {

        testFrom = null;

        testTo = null;

        strCompany = '';

        strCompany = conPeek(conCompanies, conIndex);

        changecompany(strCompany)

        {

     

        insert_recordset testTo (itemid,ModuleType,CustVendRelation,ExternalItemTxt,ExternalItemId, ABCCategory, description,inventdimid)

        select itemid,ModuleType,CustVendRelation,ExternalItemTxt,ExternalItemId, ABCCategory, description,inventdimid from testfrom;

        select count(recid) from testTo;

        info(strfmt("Records : %1,Company : %2",testTo.recid,strCompany ));

        testTo = null;

        }

        conIndex += 1;

    }

}

5. Verify data and delete your table which you duplicated (in my case CopyofCustVendExternalItem Table, as this table is of no use and was only created to facilitate and make our process faster and efficient).