Microsoft Dynamics NAV: Copy Table Data from one Company to another Company

In Microsoft Dynamics NAV you can setup separate distinct companies. Unless a table is setup to share data across all companies, by setting the DataPerCompany property to No, each company’s data is separate and distinct from the other. In many cases it may be desirable to have tables that contain the same values across companies. Due to validation and performance considerations the setting of the DataPerCompany property may not be an option.

Fortunately in C/SIDE we can use the CHANGECOMPANY function to specify the company that a record variable references. Using this method we can easily copy data from one company to the other. It is simple enough to explicitly define two variables for a particular record and write a few lines of code to copy all of the data from one company to the other. I am all about reuse and prefer not modifying code for each table or scenario. Fortunately in C/SIDE we can make use of the RecordRef variable type. This allows us to dynamically specify the tables to reference and create a neat little function:

CopyTableFromTo(FromCompanyName : Text[30];ToCompanyName : Text[30];TableNo : Integer)

IF GUIALLOWED THEN BEGIN
  Window.OPEN('#1###############\' +
              '@2@@@@@@@@@@@@@@@');
END;

// Open source table
FromTableRef.OPEN(TableNo, FALSE, FromCompanyName);
IF GUIALLOWED THEN BEGIN
  Window.UPDATE(1, FromTableRef.NAME);
END;
// Open destination table
ToTableRef.OPEN(TableNo, FALSE, ToCompanyName);
RecCount := FromTableRef.COUNT;

// Loop through the fields
IF FromTableRef.FINDFIRST THEN REPEAT
  RecNo += 1;
  IF GUIALLOWED THEN BEGIN
        Window.UPDATE(2,ROUND(RecNo/RecCount * 10000, 1));
  END;
  ToTableRef.INIT;
  FOR i := 1 TO FromTableRef.FIELDCOUNT DO BEGIN
    FromFieldRef := FromTableRef.FIELDINDEX(i);
    ToFieldRef := ToTableRef.FIELDINDEX(i);
    //TODO: Check field types
    ToFieldRef.VALUE := FromFieldRef.VALUE;
  END;
  IF NOT ToTableRef.INSERT THEN ToTableRef.MODIFY;
UNTIL FromTableRef.NEXT = 0;

ToTableRef.CLOSE;
FromTableRef.CLOSE;

IF GUIALLOWED THEN BEGIN
  Window.CLOSE;
END;

With the variables:

 

This function can be use in ways similar to:

//This is intended to be called in a function:
//CopyFromSource(CompanyName : Text[30];TableNo : Integer)

// Verify company is valid
Company.GET('CRONUS USA, Inc.');
// Set the table number; in this case Country/Region
TableNo := 9;

Company.SETFILTER(Name, '<>%1', 'CRONUS USA, Inc.');
IF Company.FINDSET(FALSE, FALSE) THEN REPEAT
  CopyTableFromTo(CompanyName, Company.Name, TableNo);
UNTIL Company.NEXT = 0;

Note: This function does not trigger and code (or data) validation. When using the CHANGECOMPANY function if you run validation code on a variable for Company B in Company A, it will modify the record in Company B, but it will run the trigger in Company A.

Leave a Comment