Al: Question: Data Conversion

[bwdeleeuw]

I was looking for a forum to ask this question, but there doesn't seem to be much available yet, so I thought I'd ask it here instead.

Concerning Data Conversion from older NAV versions, what would be the best way to approach conversion of data to the extension structure?

Of course, we could simple export data to excel, copy/paste, write an xmlport, but there might be better/smarter/faster ways to convert data from a physical table in an old version to an extension table in NAV 2018?

[tfenster]

Your scenario is an on-prem db with tradtional C/AL changes and you now want to move to extensions, right?

I would say your install Codeunit should handle that as it should have access to the old (C/AL) tables and could fill the new (AL) tables as needed

[MikeGlue]

If you want the new extension fields to be available at the same time as the old fields you need to handle naming of fields by either renaming the old fields first, or using new field names in the extension so that there's no overlap. I'd recommend the latter as it would be better practice to use a prefix in your new fields and then use the Caption property to set the friendly name. This not only lets you have both old and new fields in order to move data, but also helps future-proof your extension in the event another extension is installed with the same field names.

[bwdeleeuw]

@tfenster
That makes a lot of sense, and then using the Data Version to figure out if this is the initial install before triggering code that looks for the old tables and any data that might be there. Thanks!

@MikeGlue We have been adding the prefix to all AL development and Conversion, so that works perfectly. Thanks!

[bwdeleeuw]

@tfenster @MikeGlue this worked perfectly, thank you both for your help. I created two procedures to handle Tables as well as Table Extension Fields in a fairly generic manner during the initial installation.

Closing the Issue.

[tfenster]

@bwdeleeuw Thanks for letting us know. If you have a generic solution, do you mind sharing it? I would guess a lot of people are creating something along those lines

[bwdeleeuw]

@tfenster

Sure, I made something like this:

````
codeunit 60000 "Installation Template"
{
Subtype = Install;

trigger OnInstallAppPerCompany();
var
    ModuleInfo: ModuleInfo;
begin
    NavApp.GetCurrentModuleInfo(ModuleInfo);
    if ModuleInfo.DataVersion = Version.Create(0, 0, 0, 0) then
        HandleFreshInstallAndConversion;
end;

local procedure HandleFreshInstallAndConversion()
var
begin
    TransferTable(50000,Database::"New AL Table 1");
    TransferTable(50001,Database::"New AL Table 2");
    TransferField(18,50000,60000);
    TransferField(27,50001,60001);
end;

procedure TransferTable(SourceTableID: Integer; TargetTableID: Integer);
var
    SourceRef: RecordRef;
    TargetRef: RecordRef;
    FldRef: FieldRef;
    FieldsSource: Record Field;
    FieldsTarget: Record Field;
    TempFieldsNoToTransfer: Record Integer temporary;
begin
    FieldsSource.SETRANGE(TableNo, SourceTableID);
    FieldsSource.SETRANGE(Class, FieldsSource.Class::Normal);
    FieldsSource.SETRANGE(Enabled, TRUE);
    if FieldsSource.FINDSET then
        repeat
            if FieldsTarget.GET(TargetTableID, FieldsSource."No.") then
                if(FieldsTarget.Class = FieldsSource.Class) AND
                    (FieldsTarget.Type = FieldsSource.Type)
                then begin
                    TempFieldsNoToTransfer.Number := FieldsSource."No.";
                    TempFieldsNoToTransfer.INSERT;
                end;
        until FieldsSource.NEXT = 0;

    if TempFieldsNoToTransfer.ISEMPTY then
        exit;

    SourceRef.OPEN(SourceTableID);
    TargetRef.OPEN(TargetTableID);
    if SourceRef.FINDSET then
        repeat
            TempFieldsNoToTransfer.FINDSET;
            repeat
                FldRef := TargetRef.FIELD(TempFieldsNoToTransfer.Number);
                FldRef.VALUE := SourceRef.FIELD(TempFieldsNoToTransfer.Number).VALUE;
            until TempFieldsNoToTransfer.NEXT = 0;
            TargetRef.INSERT;
        until SourceRef.NEXT = 0;
end;

procedure TransferField(SourceTableID: Integer; SourceFieldID: Integer; TargetFieldID: Integer);
var
    SourceRef: RecordRef;
    FldRef: FieldRef;
begin
    SourceRef.OPEN(SourceTableID);
    if SourceRef.FINDSET then
        repeat
            if SourceRef.FieldExist(SourceFieldID) then begin
                FldRef := SourceRef.FIELD(TargetFieldID);
                FldRef.Value := SourceRef.FIELD(SourceFieldID).Value;
                SourceRef.Modify;
            end;
        until SourceRef.Next = 0;
end;

}
````

There is a caveat to this code, concerning the TransferField procedure. If you have multiple fields in the same table, this code would not be optimal, because it runs through each record for each field repeatedly. It works for my current needs, but of course there would be a smarter way to handle multiple fields in the same base table at the same time. I was playing with the idea of a List, but didn't need it yet.