Al: the OnNavAppUpgradePerCompany does not work as expected (it is not possible to save data)

[RedFoxUA]

Good day.

Sorry in advance for dublicate, but I can't find answer into the MS sites and MS support CAN"T answer directly on the question - HOW TO write this codeunit with simple case. The reason, I think, they don't know how to do it. If someone from MS can help me and inform what do I need to do additionally to show bug, please, inform.

The issue is (it is very simple description of the small situation to avoid possibility to go-out of the clear response):

1. I have NAV database with 2 Companies where a Ledger table exist;
   it is Ledger table exist with some fields and current installation has many lines;
2. I'm using VSC (version 1.29.1) and "schemaUpdateMode": "Synchronize" in the JSON-file;
3. I re-designed the solution a little and want to update (type or name) or delete 2 fields. When I tried to publish it I received that I can't publish it from VSC.

The Bug - it is not possible to publish application without loosing real data in the cloud! if you need more information about internal data, please, inform how do I need to share it with you.

I need to have real database and I need to write Upgrade codeunit with conditions:

• I need to have ALL records with SAME table number;
• All records should have 100% same data (but exclude 2 fields)
• 2 fields should gone

It is not a question like "how-to", but just write small test codeunit and you receive SAME issue.

[StanislawStempin]

It is not supported to run upgrade in the Cloud Sandbox. You can test upgrade in the Docker Sandboxes for now. This is similar to the issue reported here: https://github.com/Microsoft/AL/issues/4221

On the production tenant if you upload an extension with a newer version the upgrade will be executed.

[RedFoxUA]

@ StanislawStempin - Stanislaw, thank you for your answer and you are 100% right!
The bug comes when you are trying to run the upgrade codeunit (you wrote "the upgrade will be executed") for case from description.

[StanislawStempin]

For production tenant you cannot use VS Code to upload any code. You have to use Extension Management -> Upload button.

[RedFoxUA]

And when I use it I receive error

[StanislawStempin]

What is the exact error and repro steps? A minimal AL extension that reproduces it would also help

[RedFoxUA]

OK. I changed source code in VSC (temporary) and want to understand a sequence of the work if I want to re-install Extension:
part of the script in the powershell is:

if($InstalledApps) {
    $InstalledApps | % {
        $InstalledTenantApps = (Get-NavAppTenant -ServerInstance $ServerInstance -Name $_.Name -Version $_.Version)
        $AppName_name = $_.Name
        if ($InstalledTenantApps) {                
            $InstalledTenantApps  | % {
                Uninstall-NAVApp -ServerInstance $ServerInstance -Name $AppName_name -tenant $_.ID -Force -ErrorAction Stop
            }
        }

        #unpublish old app versions '--- Unpublish extension   ---'
        $InstalledApps | % {
            Unpublish-NAVApp -ServerInstance $ServerInstance -Name $_.Name -Version $_.Version -ErrorAction Ignore
        }

        #publish new app version '--- Publish extension   ---'
        Publish-NAVApp -Path $FullPath -ServerInstance $ServerInstance -SkipVerification -ErrorAction Stop

        #synchronize the schema of a tenant database to the extension '--- synchronize extension ---'
        #Sync-NavApp -ServerInstance $ServerInstance -Path $FullPath -Force -ErrorAction Stop
        if ($InstalledTenantApps) {                
            $InstalledTenantApps  | % {
                Sync-NavApp -ServerInstance $ServerInstance -Path $FullPath -Tenant $_.ID -Force -ErrorAction Stop
            }
        } else {
            Sync-NavApp -ServerInstance $ServerInstance -Path $FullPath -Force -ErrorAction Stop
        }

        #runs the upgrade logic that is defined by the upgrade codeunits in the extension '--- upgrade logic extension ---'
        if ($InstalledTenantApps) {                
            $InstalledTenantApps  | % {
                Start-NAVAppDataUpgrade -ServerInstance $ServerInstance -Tenant $_.ID -Path $FullPath -Force -ErrorAction Stop
            }
        } else {
            Start-NAVAppDataUpgrade -ServerInstance $ServerInstance -Path $FullPath -Force -ErrorAction Stop
        }
    }
} 

the 2 types of errors are:

Sync-NavApp : Cannot synchronize the extension NAV Plugin because the tenant database and the application database are not synchronized. You must synchronize the 
databases before you synchronize the extension.
At C:\Users\navuser\Desktop\Customer Scripts\Main - Install.ps1:84 char:13
+             Sync-NavApp -ServerInstance $ServerInstance -Path $FullPa ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Sync-NAVApp], InvalidOperationException
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$DynamicsNAV110/nav-systemapplication,Microsoft.Dynamics.Nav.Apps.Management.Cmdlets.SyncNavApp

Start-NAVAppDataUpgrade : Cannot upgrade the extension 'NAV Plugin by YGo 1.0.0.1' because its version must be newer than the current version. The current 
version is '1.0.0.1'
At C:\Users\navuser\Desktop\Customer Scripts\Main - Install.ps1:91 char:17
+ ...             Start-NAVAppDataUpgrade -ServerInstance $ServerInstance - ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Start-NAVAppDataUpgrade], InvalidOperationException
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$DynamicsNAV110/default,Microsoft.Dynamics.Nav.Apps.Management.Cmdlets.StartNavAppDataUpgrade

but I uninstall&unpublish "old version"?
And another situation - you released the new version, but found a big bug? How can I install the previous version back??

P.S. it is good to know what do I need to do to re-install?

[RedFoxUA]

Can someone help me with the issue?

[ianscho]

We had a similar issue as @RedFoxUA
Performing a Clean to eliminate the schema+data is not an option in the production database.
In our case we launched NAV 2018 Development environment and synchronized all of the tables, with the validate option...

[CptBlaubart]

I think MS wants you to keep the undesired fields and use the Obsolete-properties to flag them as discontinued/removed. This triggers VSCode to flag every use of the Field, so it is basically out of use. After that, you can delete the Fields content in an Upgrade Codeunit. (I recomend using NavApp to track Your version, so you can have one ever expanding Upgrade-Codeunit for the whole lifecycle of your extension. Here is an example from a Prototype i built.

codeunit 50100 "Customer Ranking Upgrade"
{
    Subtype = Upgrade;
    trigger OnUpgradePerCompany()
    var
        Cust: Record Customer;
    begin
        if DataUpgNeeded('2.0.0.0') then
            if Cust.FindSet() then begin
                repeat
                    // migrate old data
                    case Cust."Ranking" of
                        Cust."Ranking"::Bronze:
                            Cust."Ranking Code" := 'BRONZE';
                        Cust."Ranking"::Gold:
                            Cust."Ranking Code" := 'GOLD';
                        Cust."Ranking"::Silver:
                            Cust."Ranking Code" := 'SILVER';
                    end;
                    // save if neccessary
                    if Cust."Ranking Code" <> '' then
                        Cust.Modify();
                until Cust.Next() = 0;
                // clear obsolete fields
                Cust.ModifyAll("Ranking", Cust."Ranking"::" ");
            end;

    end;

    local procedure DataUpgNeeded(_VersionStr: Text): Boolean
    var
        ModInfo: ModuleInfo;
        KeyVersion: Version;
    begin
        // Check if the data is in an older state than the Key Version
        NavApp.GetCurrentModuleInfo(ModInfo);
        KeyVersion := version.Create(_VersionStr);
        exit((ModInfo.DataVersion() < KeyVersion))
    end;
}

In a productive environment, I'd probably refactor the actual data adjustment into separate procedures. But you get the Idea; have all your Data-Patches in consecutive upgrades for each version. In the same codeunit. That way, you'll never have to worry older states of data except for the one step, you're currently creating. - As long as you keep your old code from the previous iteration.

[RedFoxUA]

CptBlaubart, could you explain HOW your message (with a strange answer) is linked with the question?
What part of the question your "message" answers? Or you wanted just to write something here?

[CptBlaubart]

It's a response to your initial problem. You want to have two Fields gone.
I was trying to show you how microsoft intended to handle such situations, where Fields turn obsolete during the lifecycle of an extension. And how you could solve the issue using the intended methods.
At the root of your problem, you're trying to do a destructive schema upgrade. Which is in conflict with backwards compatibility paradigma.
If your request for "help" only appreciates solutions that follow the exact path you demand. I'm sorry for wasting your time and whish you all the best while waiting for Microsoft to tailor their product exactly to your personally stated requirements. - but I understand now how "MS support CAN"T answer directly on the question"

[RedFoxUA]

about "You want to have two Fields gone." - I never wanted that 2 fields gone.
Thank you for "I was trying to show you how microsoft intended", but you don't understand the source of the bug :)

Next, about "Microsoft to tailor their product exactly to your personally stated requirements." - again please, read my question more precisely and you will understand what the point of the question (I think when you change the side of the field it is NOT my personal requirement :) ).
But thank you again for your attempt to help with. Have a nice day.

[CptBlaubart]

Quoted from your inital issue raise:

I re-designed the solution a little and want to update (type or name) or delete 2 fields.

First claim you're trying to do a destructive schema change

2 fields should gone

Confirmation that you are trying to do a destructive schema change

I really don't know how anyone could interpret this as

• I never wanted that 2 fields gone.

as you stated in your last comment.

But yeah. Good luck upgrading that extension without dataloss. I'm not going to explain to you how destructive schema changes might affect your ability to sync the schema without losing the data. Because This:

it is not possible to publish application without loosing real data

probably has nothing to do with my answer either.

[qutreson]

I will close this issue as it appears to have been answered and has not gotten any activity in a while. We do not allow breaking changes in SaaS and we advise against doing it in general. Instead of removing/renaming or changing the type of fields, you can mark them as obsolete and handle the data transfer in upgrade code.

This said, this repository no longer handles general questions and is only dedicated to bug reports for the latest preview version of the AL compiler and developer tools.

To get help for your question please use one of the following resources:
Business Central Community
mibuso forum
Dynamics User Group