viernes, 10 de febrero de 2012

My first CrossCompany update

Objective: From one 'template' company within Ax 2009 apply two of the InventTable columns to multiple other companies.  All companies possess the same articles/items identifier data, it was just a case of updating the Item Group, and the Name Alias fields which we use as a 'translation' from the parent company - they have companies in Poland, Russia, Morocco,..

Here I was interested in learning:
  • Use a CrossCompany declaration inside the query, instead of using a ChangeCompany(...) method before calling the query.
  • Test if we could apply a transaction across companies - Yes we can!  At the database level all data in the below example sits in the same table anyway.
static void Update_InventTable()
{
    CompanyId               companyDestination, companySource;

    InventTable             tableSource, tableDestination;
    ItemId                  itemId;
    ItemGroupId             itemGrpId;
    ItemNameAlias           nameAlias;

    Dialog                  dialog;
    DialogField             field1;

    List                    lstCosicas;
    ListIterator            iterator;
    #define.splitter        (',')

    SysOperationProgress    progress = new SysOperationProgress();
    #AviFiles

    #define.companySource       ('BAI')    //curext()
    #define.companyDestination  ('AML,GLO,ITA,JML,MAP,MAR,MARR,MPOL,POL,RUS,SANT,SID,TOW')
    container               conDestination, conSource;
    int                     iCnt;
    ;

    dialog          = new Dialog("Apply inventory item update");
    dialog.addText(strfmt("¿Apply Article Group and Name Alias fields from company '%1' to:", #companySource));
    dialog.addText("     " + #companyDestination);
    field1          = dialog.addFieldValue(typeId(CompanyId), #companySource, "Choose source company");
    dialog.run();
    if (dialog.closedOK())
    {
        companySource = field1.value();
    }
    else
    {
        return;
    }

    progress.setCaption('Updatind Articles...');
    progress.setAnimation(#AviUpdate);

    // Create company containers for use in some exciting nested cross-company queries
    lstCosicas  = strSplit(#companyDestination, #splitter);
    iterator    = new ListIterator(lstCosicas);
    while (iterator.more())
    {
        companyDestination      = iterator.value();
        conDestination          += companyDestination;
        iterator.next();
    }
    conSource       += #companySource;

    // Let's update the InventTable in the multiple companies, across only one fat transaction
    ttsbegin;
    // Obtain the fields from the Source Company
    while select crosscompany : conSource ItemId, ItemGroupId, NameAlias, DataAreaId from tableSource order by ItemId
    {
        itemId      = tableSource.ItemId;
        itemGrpId   = tableSource.ItemGroupId;
        nameAlias   = tableSource.NameAlias;

        iCnt        = 0;

        setprefix(          strfmt("From Company %1, applying article '%2' across other companies", tableSource.dataAreaId, itemId) );
        progress.setText(   strfmt("From Company %1, applying article '%2' across other companies", tableSource.dataAreaId, itemId) );
        while select forupdate crosscompany : conDestination tableDestination where tableDestination.ItemId == itemId
        {
           changecompany(tableDestination.dataAreaId)
           {
                      tableDestination.ItemGroupId     = itemGrpId;
                      tableDestination.NameAlias       = nameAlias;
                      tableDestination.update();
           }
           iCnt++;
        }
        info(strfmt("Article '%2' updated in %1 companies.", iCnt, itemId));
    }
    ttsCommit;

    info('End');
}

My doubts and thoughts are:

  • Changing company has a certain 'cost' involved and so I assume applying the CrossCompany feature at the query level will be more efficient than nesting a 'find' method in a ChangeCompany?
  • How to apply the above update in an even more efficient manner? I can't see CrossCompany in the update_recordset documentation, and it explicitly states that CrossCompany is not implemented within the delete_from and insert_recordset clauses.
  • Aside from having to build the containers for the CrossCompany feature, I like it for legibility/readability within the code.  The ChangeCompany method, however, is more 'obvious' when skimming through someone else's code.  It also seems to be obligatory if you need to update the data. 

1 comentario:

  1. Hi,
    Post is old, but I tried something more efficient :
    Do a changeCompany on your target company and put in this scope a update_recordset query. This is much more faster than update() or doupdate().
    Don't forget to clear() your table buffer just before your update_recordset.


    ResponderEliminar