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.
Hi,
ResponderEliminarPost 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.