How to move data to extensions in business central from my physical table fields in navision while upgrade

(0) Share

Report

Posted on by Umang1112

Customer Field

Create a new Table 90000 Customer UPG with two fields, the Primary Key of Table Customer ( "No.") and Field 50000 Foo.

Your Upgrade Codeunit 90000 should have following entry to copy that data:
DataUpgradeMgt.SetTableSyncSetup(DATABASE::"Customer",DATABASE::"Customer UPG",TableSynchSetup.Mode::Copy);

If you import the new Customer Table, where Field Foo was deleted, the Customer UPG Table and the Upgrade Codeunit, the Table Synchronisation (Schema: Now) would copy every entry of the Customer Table with the value of Foo into the Upgrade Table Customer UPG before deleting the Field Foo from the Customer Table.

With a second Upgrade Codeunit 90001 and Upgrade Function (Subtype UpgradePerCompany) inside you AL extensions, you can move the Data back.

if CustomerUPG.FINDSET then begin
  repeat
    Customer.GET(CustomerUPG."No.");
    Customer.Foo := CustomerUPG.Foo;
    Customer.MODIFY;
  until CustomerUPG.NEXT = 0;
  CustomerUPG.DELETEALL;
end;

Custom Table

Create new Table 90001 Foo UPG with the same Fields of Foo.

Your Upgrade-Codeunit 90000 should have following entry:
DataUpgradeMgt.SetTableSyncSetup(DATABASE::"Foo",DATABASE::"Foo UPG",TableSynchSetup.Mode::Move);
After you import the new Foo UPG Table and the Upgrade Codeunit, the Table Synchronisation (Schema Now) for deleting Table Foo should move all that Data into Foo UPG.

Also in the second Upgrade Codeunit 90001 and additional Upgrade Function inside you AL extensions, you can move the Data back into the new Table Foo.

if FooUPG.FINDSET then begin
  repeat
    Foo.TRANSFERFIELDS(FooUPG);
    Foo.INSERT;
  until FooUPG.NEXT = 0;
  FooUPG.DELETEALL;
end;

@DanielGoehler we followed the above step but we are stuck at a point where we had moved the data of customized filed of table in UPG Table which is created in NAV development environment, but we are unable to find that table in the visual studio code while trying to move data from UPG table to my extended table.

Originally posted by @DanielGoehler in #1512 (comment)

umang11 commented 16 hours ago

So to achieve the above I had followed thee below steps but stucked at last point

Created a table Customer UPG in development environment of Business Central
Now created a upgrade code-unit and written code DataUpgradeMgt.SetTableSyncSetup(DATABASE::"Customer",DATABASE::"Customer UPG",TableSynchSetup.Mode::Copy);
Now deleted the field from Customer table with run sync now.
By doing the above field deleted from Customer and data moved to the Customer UPG Table.
Now we had installed the extension which we had made for Customer table.
Now we are stuck at this point(As we are unable to write code by using Customer UPG table in visual studio code and also we are unable to find the field of extended table of customer in NAV Development environment so where we can write code to move data from Customer UPG to extended field of customer table in extension.)

Categories:

Business Central

Data Migrations

Other

I have the same question (0)

All responses (15)

Answers (0)

Community Member on at

Like (0)

Report
Copy link

Link copied!

Stuck at the same point, Unable to write code to move data from CustomerUPG to extension table.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!
let me complete the comments:

first of all, you have done all on BC 14. you must transfer all data from the customized fields to extension and after upgrading all to extension you may upgrade to BC 15 version.

Create a table with the name of "Customer UPG" in the development environment of Business Central 14

create an upgrade code-unit and written code DataUpgradeMgt.SetTableSyncSetup(DATABASE::"Customer",DATABASE::"Customer UPG",TableSynchSetup.Mode::Move);

replace the Customer table with the original one (from CRONUS objects) and sync with Forcesync.

By doing the above field deleted from Customer and data moved to the Customer UPG Table.

installed the extension which has the Customer table extension with the customized field.

go to the installation folder of BC 14, run command prompt as administrator and run the following command:

finsql.exe Command=generatesymbolreference, Database=<DatebaseName>, ServerName=<ServerName>\<Instance>

When you run the command, the console returns to an empty command prompt, and does not display or provide any indication about the status of the run. However, the finsql.exe may still be running in the background. It can take several minutes for the run to complete, and the symbols will not be generated until such time. You can see whether the finsql.exe is still running by using Task Manager and looking on the Details tab for finsql.exe.

When the process ends, a file named navcommandresult.txt is saved to the Dynamics NAV Client connected to the Business Central installation folder. If the command succeeded, the file will contain text like [0] [06/12/17 14:36:17] The command completed successfully in '177' seconds.

on VisualCode, create a new project with the name of "Upgrade Data". connect to BC 14 and download the symbol.

now you have "Customer UPG" on the symbols.

do not forget to make the dependency on this project with your extension app to make sure you have the customized field on extension before transferring the data

create the following code unit in "Upgrade Data" project:

codeunit 51001 DataInstall_E

{

Subtype = Install;

trigger OnInstallAppPerCompany();

var

begin

TransferDataInsert(<Cutomer UPG table ID>, <Customer Table ID>);

end;

procedure TransferData(TableFromID : Integer;TableToID : Integer);

var

Field : Record Field;

RecRefFrom : RecordRef;

RecRefTo : RecordRef;

FieldRefFrom : FieldRef;

FieldRefTo : FieldRef;

begin

CLEAR(RecRefFrom);

CLEAR(RecRefTo);

Field.RESET();

RecRefFrom.OPEN(TableFromID);

RecRefTo.OPEN(TableToID);

IF RecRefFrom.FINDSET() THEN BEGIN

Field.SETRANGE(TableNo,TableFromID);

REPEAT

IF Field.FINDSET() THEN BEGIN

RecRefTo.INIT();

REPEAT

FieldRefFrom := RecRefFrom.FIELD(Field."No.");

FieldRefTo := RecRefTo.FIELD(Field."No.");

FieldRefTo.VALUE(FieldRefFrom.VALUE());

UNTIL Field.NEXT() = 0;

IF RecRefTo.INSERT() THEN;

END;

UNTIL RecRefFrom.NEXT() = 0;

END;

end;

}

install the new extension.

data has been moved to extension, so uninstall the Data Upgrade extension

delete Customer UPG

upgrade to BC 15, if you want to stay on BC 14. go to the installation folder of BC 14, run command prompt as administrator and run the following command:
finsql.exe Command=generatesymbolreference, Database=<DatebaseName>, ServerName=<ServerName>\<Instance>
to remove the symbol of Customer UPG

Was this reply helpful? Yes No
THAKRJ 35 on at

Like (0)

Report
Copy link

Link copied!

Hi, I tried above process. It works really nice. But, faced the issue in Standard Tables Custom Fields.

As per the code IF RecRefTo.INSERT() THEN;, system does not update the Customer Table Records with the custom field values. So, when I tried IF NOT RecRefTo.INSERT() THEN RecRefTo.Modify();, then it updated the custom fields values but, made all other fields values BLANK. Which is dangerous and good that I tried on CRONUS. Do you have any suggestion towards that please ?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

there are 2 points.

first the standard table is empty and you are going to insert the data from table with old data to the standard table. therefore it must be insert not modified.

the second point is. the ID of the custom field in old table must be the same as the ID of the field on the extension

if the IDs are not the same you should create another funtion to transfer them like the following.:

treansferring the custome field data from old modified table (Job Planning Line UPG_EX) to extension. at first all data has been moved from the old table to the standard table and with this funstion it just moves some customized filed to the extension. look here it is using the name of the fields because the IDs are not the same.

procedure JobPlanningLineUPG_Ex()

var

TableUpg: Record "Job Planning Line UPG_EX";

TableOrg: Record "Job Planning Line";

begin

TableUpg.Reset();

if TableUpg.FindFirst() then

repeat

TableOrg.SetRange("Job No.", TableUpg."Job No.");

TableOrg.SetRange("Job Task No.", TableUpg."Job Task No.");

TableOrg.SetRange("Line No.", TableUpg."Line No.");

if TableOrg.FindFirst() then begin

TableOrg.Customizedfield1 := TableUpg.Customizedfield1;

TableOrg.Customizedfield2 := TableUpg.Customizedfield2;

TableOrg.Customizedfield3 := TableUpg.Customizedfield3;

TableOrg.Customizedfield4 := TableUpg.Customizedfield4;

TableOrg.Modify(false);

end;

until TableUpg.Next() = 0;

end;

Was this reply helpful? Yes No
THAKRJ 35 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your response Steve !

I have Customer Table filled up with the Standard Data and also with Custom Data. So, as per the above steos, I moved the Custom fields data to Temp Tables with the same IDs. After that, I am trying to perform the reverse data transfer from Temp Table data to Customer Table fields in Extension.

Customer Table with Standard Fields is having Data.

IDs are same in both.

Still Insert should work ? In my case it's not copying the data in Customer Table Custom Fields of Extension.

While the same procedure worked for Custom Tables.

Can you please, suggest ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

upon inserting the data into the standard table it inserts data into the extension table as well. I mean it sees one table (which is the combination of the standard and the extension table)

did you put your extension and dependency of upgrade data extension?

you suggest having 2 extensions, application extension and data upgrade extension. the data upgrade extension must have th edependency of application extension on app.json

Was this reply helpful? Yes No
THAKRJ 35 on at

Like (0)

Report
Copy link

Link copied!

Yes, That's correct ! I have 2 Extensions ! Custom Fields Extejnsion and also a Upgrade Extension. But, it seems, like when Upgrade extension checks, if Customer Record already exists, it does not update other fields too using INSERT.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

it is true. but it shouldn't be the case because the table is empty at the beginning.

if you want to modify, you should first use Setrange according to the Keys and find the filed then change the field data and modifying.

it seems you didn't find the field, it created a new record with the customized fields and the rest with blank.

same as the last code

Was this reply helpful? Yes No
THAKRJ 35 on at

Like (0)

Report
Copy link

Link copied!

I am working on Upgarde from CAL to AL. Now, There are few Standard Tables having Custom Fields with data. So, I have to move those custom fields data to Extension fields from CAL. So, e.g There are 100 Customers in the Table. So, I just have moved only custom fields data to a temp Table with the Primary of Customer No. as well. So, that means, my Customer Table is not Empty bu having all 100 Customers inside. We just have to update the Custom fields data into that Table's extension.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Copy link

Link copied!

there are 2 different ways:

copying all data to temp, deleting all records from the standard table, upgrading and then restoring from the temp to standard and extension

another way:

copying all data to temp, upgrading the tables, then restoring the customized code from temp to extension.

the first code with insert in using for the first approach.

for the second way, you need the filter the records on the table. insert the customized fields and modify

Was this reply helpful? Yes No