Excel Buffer for the Cloud

One of my clients asked me to help them convert an add-on that they developed in C/CIDE into an AppSource app. This add-on includes the functionality to export some data into an Excel file, using the Excel Buffer table.

The Excel Buffer table is also available in AL, but one of the issues is that as soon as you set the target of the extension to ‘Cloud’ (Which as you know is an attribute in app.json), the compiler will scream at you that you can’t use certain functions of the Excel Buffer, because their Scope has been set to on premises. So if your C/AL object uses the ‘OpenExcel’ function, for instance, you can’t use that for AppSource apps because its scope is OnPrem. This type of thing usually takes me days to figure out, so I thought I’d ask Twitter with my favorite community hashtag #bcalhelp

Within a day I received a bunch of helpful suggestions, I just love this community! The one that put me over the top was a phone call with my good friend AJ, who not only showed me, but he also sent me some sample code that he was working on. He’s working on a blog post about this topic himself, so I’ll let him share that and I’ll post a link to his blog once he puts it online. I want to mention Owen too because he had sent me essentially the same suggestions, but to an email address that I hardly ever use anymore, so I didn’t see that until days later.

As you can see by the trigger name, I had to put this into a report object (which I’ll share when I find time to put it in a repo). My main problem was that I needed to be able to provide a way for the user to open the Excel file. For this to work, you use the OpenExcel function. This actually does not open Excel, but what it does instead is it downloads the Excel file into the Downloads folder on your computer, and then you can open that file from there.

Some additional pointers:

  • CreateNewBook creates a new file, with a new sheet. If you already have the file created, and you need to add a sheet to the existing file, then you would use the SelectOrAddSheet function
  • TheWriteSheet function writes the records from the Excel Buffer table into the sheet. Each record represents a cell value
  • You will need to use the NewRow, AddColumn functions to ‘walk the grid’ of the cells in your sheet. Also very useful functions: ClearNewRow and SetCurrent. I ended up adding a GetCurrentRow function to an Excel Buffer table extension
  • The CurrentRow and CurrentCol variables in the Excel Buffer table are your friend. Forget about the letter/numbers of the Excel file itself, just use the row/column numbers
  • SetFriendlyFileName is not mandatory, but otherwise the file will be called ‘Book1’ or something

Like I said before, AJ is working on a post for this as well, and he said he was going to offer a repo with the objects as well. If I don’t forget I’ll create a sample report and offer that as a PR to AJ’s Excel repo.

Translation File Names Must Match App.json

This is a quick follow-up on my previous post about creating a container for modified Base App development, about the translation file issue. After publishing that post, I also reported the error message to the AL repo on Github and to the MicrosoftDocs repo.

As @NKarolak suggested, the names of the translation files must match the name in app.json. I was very skeptical about this, because this was never the case in any of the AppSource apps I’ve worked on, and the Doc for the translation files specifically says that there is no enforced naming of the translation files. It might be a new requirement though.

When I first created my AL workspace by exporting it from my container, the translation files were named as follows:

The name in app.json is ‘Base Application’ so the space character is replaced with ‘%20’ which is the html representation for the space character. Since the original error message did not mention the file name, I did not think that the file name itself was the problem.

I decided to try Natalie’s suggestion and replaced the ‘%20’ with a regular space, and voila, it published the app as expected.

Next, I changed the name in my app.json to ‘Super Base Application’ and it errored out again. Once I changed the translation files to match the name in app.json, it worked again.

Moral of the story: when developing a modified Base App, you have to match the translation files to the name in app.json.

Modified Base App on Docker

How to get started with modifying the Base Application using Docker

Many partners are still focused on doing custom development for their customers with their one-off implementations. MANY of those customers are existing customers with existing NAV systems with existing customized objects. As much as everyone wants to go to extensions only, and most partners see the need and are more than willing to make the necessary changes, the reality is that many of these existing customers do not want to pay for migrating all of their custom modifications. This reality comes with the need to modify the base app. Since C/SIDE is no longer available, the only way to do this is to use VSCode. This post will explain how you can create a Docker container, and use that container to do modifications on the Base Application.

To get started, click here to read the article on docs.microsoft.com. I say ‘get started’ because it was not enough to get me all the way there, which is the reason why I wrote this post. This article seems to have been written for an actual installation from the product DVD, and there were some additional things you need to know to make it all work if you want to use Docker. At least, that is per the date of this post, because things may change :). I’ll try to revisit this post if it does change.

Alright, so to make this work, you need a few things:

  • A Docker container that is based on the latest Business Central Docker image.
  • Configure the Service Tier in the container
  • Extract the objects from the container into a new AL workspace
  • Uninstall and Unpublish the Base Application and its dependencies

Create a new Container

For Business Central development I always use the NavContainerHelper module, so before you use any of the commands in this post, update your module:

Update-Module navcontainerhelper

To get the latest Docker image for Business Central I will be using the ‘mcr.microsoft.com/businesscentral/onprem:na-ltsc2019’ image. You can leave the ‘ltsc2019’ part out if you are not sure about the host OS or if you are on Windows Server 2016. You can substitute ‘na’ for your own localization, or leave that tag out altogether if you want to be on the W1 version. To read about which image to use, visit Freddy’s blog here and follow the links to what you need to know. Here is the script that I used to create my container:

$imageName = 'mcr.microsoft.com/businesscentral/onprem:na-ltsc2019'
$licenseFile = '<path to your BC 15 developer license>.flf'
$ContainerName = 'mysandbox'
$UserName = 'admin'
$Password = ConvertTo-SecureString 'Navision4ever!' -AsPlainText -Force
$Credential = New-Object System.Management.Automation.PSCredential ($UserName, $Password)


New-NavContainer `
    -accept_eula `
    -containerName $ContainerName `
    -imageName $imageName `
    -licenseFile $licenseFile `
    -auth NavUserPassword `
    -alwaysPull `
    -Credential $Credential `
    -includeAL `
    -updateHosts `
    -additionalParameters @("-e customNavSettings=ExtensionAllowedTargetLevel=OnPrem")

I use the ‘-alwaysPull’ switch to make sure that I always have the latest version of the Docker image. The ‘-includeAL’ switch is necessary to include references to the DotNet assemblies in the Docker container. The ‘-additionalParameters’ switch (h/t @tobiasfenster) is used to set the ExtensionAllowedTargetLevel property to ‘OnPrem’. I’ll explain how to set this with a simple PowerShell Cmdlet in a minute.

One more important switch is the ‘-useCleanDatabase’ switch, which can be used to uninstall and unpublish the Base Application and its dependencies, as I will discuss in a little bit. At this point, you have a vanilla Docker container with the latest on premises version of Business Central.

Configure the Service Tier

As the Doc states, there are three things you need to set. It is not very clear exactly how to do that, and not at all how that works on Docker, so let me just explain from scratch.

First, you need to know how to look at, and modify, the Service Tier settings inside the container. Some of these types of commands are available in the navcontainerhelper module, but some of them are not. I did find a Cmdlet to see the settings, but I could not find one to actually modify them. So, to cover all of it, I will show you how you can connect to the container and run regular BC PowerShell Cmdlets from inside the container.

Open a PowerShell ISE window as administrator, and run the commands in the screenshot

Our container name is ‘mysandbox’, and you connect to it by using the ‘Enter-BCContainer’ Cmdlet. You can see how the prompt changes to show you that you are inside the container. At this stage, the navcontainerhelper does not work, so you will have to use the regular BC PowerShell Cmdlets. The next Cmdlet shows you all the properties of the Service Tier that runs inside your container, which in this version of Business Central is called ‘BC’.

According to the Doc, the following settings are important. I am using the names that are used in PowerShell rather than the names in the Doc.

  • ExtensionAllowedTargetLevel should be set to ‘OnPrem’, although it seems that the value ‘Internal’ also works.
  • DeveloperServicesEnabled should be set to true. This should be the default value of this particular setting
  • There is also a mention of the EnableSymbolLoadingAtServerStartup property in the Doc, but I’ve received confirmation (h/t @freddydk) that this property was meant for hybrid C/AL and AL environments, so that is not needed anymore for BC 2019 wave 2

To modify these settings, use the following PowerShell command

Set-NAVServerConfiguration `
      -ServerInstance BC `
      -KeyName ExtensionAllowedTargetLevel `
      -KeyValue OnPrem

After modifying those settings, restart the service tier using the ‘Restart-NAVServerInstance -ServerInstance BC’ command. At that point, the service tier in your container should be configured for doing on premises development. The next thing you need to do is get the application objects out of the container.

Create AL Workspace from Base App

This step is easy, using a navcontainerhelper Cmdlet, so you need to first exit the container (type ‘exit’ and then enter). Then, run this Cmdlet:

$ContainerName = 'mysandbox'
$UserName = 'admin'
$Password = ConvertTo-SecureString 'Navision4ever!' -AsPlainText -Force
$Credential = New-Object System.Management.Automation.PSCredential ($UserName, $Password)

Create-AlProjectFolderFromBcContainer `
    -containerName $ContainerName `
    -alProjectFolder 'C:\MyProjects\BaseApp' `
    -useBaseAppProperties `
    -credential $Credential 

One thing to note here is that the ‘-useBaseAppProperties’ switch uses the properties from the container. You will end up with a fully functioning AL workspace, with an app.json and launch.json that is configured to look inside the container for the objects and the DotNet probing path. You will need to configure this yourself if your configuration needs to be different. But, since we’re making this work for a standard container, we’re going to use the standard configuration as well.

One other important thing to note…. As I am writing this post, I’ve had a persistent error message that prevented me from compiling the app, which I narrowed down to having to remove the translation files. The annoying part is that the error message itself does not mention the translation files, but it started working again after I removed them. In your new BaseApp folder, there is a folder called ‘Translations’. Remove all files from that folder, except the ‘*.g.xlf’ file.

Update 2019/11/27 follow up on the translation file issue

One final thing to note is that this is just a simple AL workspace. In a real life situation, you are doing this for a particular customer, so you need to think about source control, workspace settings, things like that. There are some capabilities in the Cmdlet, so take a look here to see all the available parameters of the Cmdlet.

The last thing you will need is to download the symbols for the system apps from the container. The Doc also mentions adding the assemblyProbingPaths to the workspace settings, but if you used the ‘-useBaseAppProperties’ switch, that is already taken care of for you and the setting will point to one of the container’s shared folders.

Uninstall / Unpublish Base App

In the previous step, you’ve created an AL workspace with all of the objects from the Base Application. Now, your container already has a Base App, so in order to create a modified Base App, you will have to get rid of the standard one first. You can be a PowerShell warrior and run the Cmdlets in this section, or you can also use the ‘-useCleanDatabase’ switch in the New-BCContainer Cmdlet in the first section. This will remove the Base App and all its dependencies from your container right away.

On to the PowerShell… In the Doc, under bullet 11, you will find the functions to accomplish this. These are regular NAV PowerShell Cmdlets, so you will need to enter the container first:

function UnpublishAppAndDependencies($ServerInstance, $ApplicationName)
{
     Get-NAVAppInfo -ServerInstance $ServerInstance | Where-Object { 
    # If the dependencies of this extension include the application that we want to unpublish, it means we have to unpublish this application first.
    (Get-NavAppInfo -ServerInstance $ServerInstance -Name $_.Name).Dependencies | Where-Object {$_.Name -eq $ApplicationName}
 } | ForEach-Object {
    UnpublishAppAndDependencies $ServerInstance $_.Name
 }

 Unpublish-NavApp -ServerInstance $ServerInstance -Name $ApplicationName
}

function UninstallAndUnpublish($ServerInstance, $ApplicationName)
{
    Uninstall-NavApp -ServerInstance $ServerInstance -Name $ApplicationName -Force
    UnpublishAppAndDependencies $ServerInstance  $ApplicationName

}

This loads the functions into memory, and then you can run the script:

UninstallAndUnpublish -ServerInstance BC -ApplicationName "Base Application"

This will completely remove the Base App and its dependencies.

Ready to Start Developing

That’s it, you should now be ready to start your development. See how that works. Add a field to a table, add that field to its Card page and hit Ctrl+F5. It will probably take a while to compile, but you should see your new field on the page.

Now I do need to say that I completely and wholeheartedly agree with the entire community, and code customizations should really not be done anymore. All development should be done using extensions instead of change the Base App itself. It makes everyone’s life a lot easier if you minimize the amount of development done to the Base App, so even if you have no other choice, try to design the development in such a way that most of it is in an extension, and only modify the Base App for the parts that you can’t figure out how to do in an extension.

Update 2019/11/27: created a GitHub repo with the scripts