The function Rec.ChangeCompany has been around for quite some in Navision/Dynamics NAV/Business Central. It is quite useful since it allows the user to quickly switch between companies and do record modifications in another companies in the same database. However, this is a very dangerous command especially now when events has been introduced. The question is if this command is still relevant today or if it should be passed to the cemetery.
Lets start investigate what happens when we do the following but before I can run this code I need to create a new company called ‘CronusCopy’. For this example it needs to be a copy of cronus in order to find customer with no. 10000 in the other company.
So what happens?
Customer.ChangeCompany('CronusCopy');
Customer.get('10000');
Customer.Name = 'New name';
Customer.modify(false);
Well, not that much really. The record steps “into” company ‘CronusCopy’, gets the customer 10000 and then changes the name to ‘New Name’.
In this scenario we had modify(false) but what if we have modify(true) and also have an eventsubscriber that listens to modification of the customer table? How will they work?
Triggers and Eventsubscribers
To test this scenario I created a new table:
table 50000 "Change Company Test"
{
Caption = 'Change Company Test';
fields
{
field(1; "Code"; Code[20])
{
Caption = 'Code';
NotBlank = true;
}
field(2; "Modify Description"; Text[100])
{
Caption = 'Modify Description';
}
field(3; "Trigger Description"; Text[100])
{
Caption = 'Trigger Description';
}
field(4; "Event Description"; Text[100])
{
Caption = 'Event Description';
}
}
keys
{
key(PK; "Code")
{
Clustered = true;
}
}
trigger OnModify()
begin
rec."Trigger Description" := 'OnModify ' + rec.CurrentCompany;
rec.Modify(false);
end;
}
The idea with this table is we should create two records, one in each company. Then we call the rec.ChangeCompany, do a modification, and run modify(true) on that record. And on top of that we have an eventsubscriber on the table that listens to modificatio on the same table. Then we can see what get changed where.
[EventSubscriber(ObjectType::Table, Database::"Change Company Test", 'OnAfterModifyEvent', '', false, false)]
local procedure ChangeCompanyTestOnAfterModify(var Rec: Record "Change Company Test"; RunTrigger: Boolean)
begin
if not RunTrigger then
exit;
rec."Event Description" := 'Event Modify Test';
rec.Modify(false);
The modification code will look like:
ChangeCompanyTest.ChangeCompany('CronusCopy');
ChangeCompanyTest.get(10000);
ChangeCompanyTest."Modify Description" := 'ChangeCompany Test';
ChangeCompanyTest.Modify(true);
We will also need to create a init function that deletes the records in those companies if it exists from previous runs and then create them. Just to keep the testing clean. Source code can found here on GitHub.
local procedure InitRecord(var ChangeCompanyTest: Record "NBA Change Company Test")
begin
if not ChangeCompanyTest.IsEmpty() then
ChangeCompanyTest.DeleteAll(false);
ChangeCompanyTest.Code := '10000';
ChangeCompanyTest.Insert(false);
}
Lets run the code from company “Cronus Sverige AB” it and see what result we get!
The top section shows how the record looks in the caller company (Cronus Sverige AB) and the bottom section shows the record in the target Company (CronusCopy).
The result looks what we hoped it would be. The changes to the parameter in the OnModify trigger and the eventsubscriber actually changed the data in the target company which is good!
But, what happens if the OnModify Trigger and the eventsubscriber changes another record?
Triggers Changing Other Records
To simulate this we can create the same record but with a different key and then add modifications to it in the triggers.
This is how our updated OnModify trigger will look like:
trigger OnModify()
var
SAChangeCompanyTest: Record "Change Company Test";
begin
rec."Trigger Description" := 'OnModify ' + rec.CurrentCompany;
rec.Modify(false);
SAChangeCompanyTest.get('20000');
SAChangeCompanyTest."SA Trigger Description" := 'OnModify ' + SAChangeCompanyTest.CurrentCompany;
SAChangeCompanyTest.Modify(false);
end;
And an updated version of the eventsubscriber looks like:
[EventSubscriber(ObjectType::Table, Database::"Change Company Test", 'OnAfterModifyEvent', '', false, false)]
local procedure ChangeCompanyTestOnAfterModify(var Rec: Record "Change Company Test"; RunTrigger: Boolean)
var
SAChangeCompanyTest: Record "Change Company Test";
begin
if not RunTrigger then
exit;
rec."Event Description" := 'Event Modify Test';
rec.Modify(false);
SAChangeCompanyTest.Get(20000);
SAChangeCompanyTest."SA Event Description" := 'SA Event Modify Test';
SAChangeCompanyTest.Modify(false);
end;
Here we can see the result:
The interesting part here is when the trigger/subscriber calls “another record” that hasn’t explicit been changed to the target company it will be executed in the source company(!). Let me give you an example why this is VERY important. Take sales order, it consists of header and lines. If you use the delete trigger in the header it will delete its lines. In this scenario the companies in the database uses the same no. serie.
SalesHeader.ChangeCompany('TargetCompany');
SalesHeader.Get('10000'); // An order no. that also exist in the source company
SalesHeader.delete(true);
The result of this is that it will delete the SalesHeader in TargetCompany BUT will delete the lines and comments in the SourceCompany! This can be extremely dangerous if you use the triggers.
Back in the old days we knew as long as we didn’t specify true in the triggers like modify(true), insert(true), delete(true) we were fine. But we had to code-clone the code in the triggers or do so the job queue/NAS executed the operation in the target company so it would do it with proper business logic.
Events That Changes Other Records
But now we have another dimension to this, events.
With events we can’t decide from the operation if they should be triggered or not. Well we can still specify that it shouldn’t run the trigger but then we can only pray that the one who subscribes to the table operation has specified that it shouldn’t be run if not trigger is called.
[EventSubscriber(ObjectType::Table, Database::"Sales Header", OnAfterDeleteEvent, '', false, false)]
local procedure OnAfterDeleteEvent(var Rec: Record "Sales Header"; RunTrigger: Boolean)
begin
If not RunTrigger then // Not always this is specified
exit;
RandomTable.Delete(true);
end;
Another example of an eventsubscriber that could prevent wrongful execution
[EventSubscriber(ObjectType::Table, Database::"Sales Header", OnAfterDeleteEvent, '', false, false)]
local procedure OnAfterDeleteEvent(var Rec: Record "Sales Header"; RunTrigger: Boolean)
begin
If rec.CurrentCompany <> CompanyName then
exit; // Don't continue if ChangeCompany is used
RandomTable.Delete(true);
end;
So in other words we can never be really sure if there will be a future subscriber to the table with modifications to other tables unless if we hide the tables with scope internal. But that will only hide it from other apps but it won’t hide if someone else creates a subscriber within the same app.
Alternatives
As we have seen ChangeCompany is good to get data from other companies and but can be very dangerous to use for changing data plus it is pretty worthless if you would like to do data changes that executes business logic in the target company.
The options are:
- Create transaction data in a “import” table and create a task in target company via taskscheduler, TaskScheduler.CreateTask(..,’TargetCompany,..) that modifies the data according to business logic via events/triggers.
- Consume api:s from target company and insert the data that way. That will also use the correct business logic in the company.
Summary
The ChangeCompany command can be an effective and quick command to read data from other companies. However it can also be EXTREMELY dangerous if you use it to write to tables in other companies. The reason for this is that modifications triggers or events can listen to the modification and change other records but those records will be in the source company and NOT in the target company which will lead to data loss or inconsistent data.
It is also hard to predict if some app/or functionality in the future will add an event that listens to that table for modifications and does changes to other tables. That is why I recommend that if you need to do data changes in another company it is better to do it via api:s or transaction table together with task scheduler in the target company that does the modifications. If you do it with that it will automatically be guaranteed that the modifications will be done according to business logic as it was intended to be used.
But if you really need to do modifications with ChangeCompany, do it in tables where there shouldn’t be a need for trigger code or event subscribers in the future. Another possibility is to hide the tables with changing scope to internal to protect from other apps to access the table.
Source code can be found here on GitHub.