Category Archives: Tips and Tricks

Tips and Tricks

Tip #66 | UTC Calculation

I have not blogged as much as I want to recently. Not because there is nothing to write about, but because I am so insanely busy.

However, today I ran into a question that required first Google and then Statical Prism to solve.

UTC Calculation in Dynamics NAV (Or Business Central)

This used to be cumbersome and required dirty tricks. Alternatively (and this is how I managed to solve it until today) you can setup a UTC service tier.

But, with the help of Prism I found that Codeunit 358 now has this code

[External] ConvertToUtcDateTime(LocalDateTime : DateTime) : DateTime
IF LocalDateTime = CREATEDATETIME(0D,0T) THEN
  EXIT(CREATEDATETIME(0D,0T));
DotNetDateTimeOffset := DotNetDateTimeOffset.DateTimeOffset(LocalDateTime);
DotNetDateTimeOffsetNow := DotNetDateTimeOffset.Now;
EXIT(DotNetDateTimeOffset.LocalDateTime - DotNetDateTimeOffsetNow.Offset);

So no more stupid workarounds, just use this instead.

Please hold on for more stuff on my blog. I recently converted one of my customers to SQL Azure with Windows Virtual Desktop, Azure Logic Apps, Blob storage & Azure Functions. So much to blog about. #ServerlessComputing

I’m also starting on a new contract next week that will help me multiply my skills in an efficient way. Very enthousiastic to try that out for the first time.

So hang on and keep coding….

Tip #61 | .gitignore for AL projects

The Business Central Community loves Git, but GitHub does not seem to even know we exist. It recognises our projects as perl projects and there is no suggestion for a .gitignore file.

Why .gitignore?

It’s generally considered best practice to use Git for managing uncompiled code, but not to store the result of a project, nor it’s dependencies.

Also, settings that may vary from developer to developer are best not to be stored since doing to would continously lead to conflicts with pushing and merging.

For AL projects this means we need to exclude our .app file (the result), the alpackages (our dependencies) and the vscode settings file.

Or if you want to copy & paste

.alpackages/
.vscode/
*.app

NOTE: You should create your .gitignore before the initial commit. Removing files later is a tedious process.

Tip #60 | Suppress Warnings in Visual Studio Code

One of the most anoying things about writing AL code in Visual Studio Code is getting warnings that you cannot fix. Simply impossible.

My “favorite” warning is this one

For almost a decade it’s been possible to sort on flowfields from code and in reports and in most cases it works fine. On larger datasets it might require a covering index for performance.

This warning is a joke because it suggests to add a flowfield to the key’s. Even if it were a normal field; in extensions you cannot influence keys in Base Application anyway.

It’s recommended to fix this per project and to do this you need to add a settings.json file to your .vscode folder like this:

The content of this file should be something like this:

{
    "name": "ForNAV",
    "description": "ForNAV Rules",
    "generalAction": "Hidden",
    "rules": [
        {
            "id": "AL0432",
            "action": "Hidden",
            "justification": "Marked for removal, be careful with this rule…"
        },
        {
            "id": "AL0254",
            "action": "Hidden",
            "justification": "Not possible to solve"
        },

Tip #59 | Multiple Start Configurations in Visual Studio Code

When developing extensions for Business Central you have a wide array of publishing options to choose from.

My most used options when working on the ForNAV Customizable Report Pack are our Sandbox and Docker.

Testing is best on the Sanxbox for two reasons. First because all the Azure Active Directory stuff actually returns something which is useful for licensing scenario’s. Second because you can easily share the result with the team since everyone is on the same Sandbox.

Docker is useful when you don’t want to test on current but on an older or vNext instance.

Lastly it’s also possible to install Business Central on your own infrastructure altough this is a dying species.

In your Visual Studio Code project you can specify how you want to publish in the launch.json file but did you also know you can setup miltiple configurations and then choose one at the time of publishing.

This is how it could look:

{
     "version": "0.2.0",
     "configurations": [
         {
             "type": "al",
             "request": "launch",
             "name": "Docker",
             "authentication": "UserPassword",
             "startupObjectType": "Page",
             "breakOnError": true,
             "launchBrowser": true,
             "server": "http://bcsandbox",
             "serverInstance": "NAV",
             "enableLongRunningSqlStatements": true,
             "enableSqlInformationDebugger": true,
             "schemaUpdateMode": "Synchronize"
    },
    {
        "type": "al",
        "request": "launch",
        "name": "Microsoft cloud sandbox",
        "startupObjectId": 6188475,
        "startupObjectType": "Page",
        "breakOnError": true,
        "launchBrowser": true,
        "enableLongRunningSqlStatements": true,
        "enableSqlInformationDebugger": true,
        "schemaUpdateMode": "Synchronize"
    }
] 
}

Now if you publish your code Visual Studio Code will ask for the correct configuration.

NOTE: Your credentials cache is shared accross these configurations. You will need to clear the credentials cache if you switch.

TIP: You can also use this to create a seperate config for Syncronize and Recreate.

Tip #58 | Run Extension Objects

One of the quirks of working with extensions is that you cannot run an object from the object designer. This is true for V1 and V2.

With V2 you can start an object (page) after deploy but this only works once and only in the WebClient.

If you just quickly want to check our a page or codeunit in the Windows client you can write a codeunit against an object that does not exist.

An example is the TowersOfHanoi app that Microsoft ships as example. This does not have a page extension to execute itself.

Works all the time.

Want to learn more about extensions? Contact me today!

Why SQL Server 2016 Management Studio Understands Navision

I spend a lot of my time in SQL Server Management Studio. I rarely ever run tables anymore from Object Desingner and run most of my data conversions directly in T-SQL.

Why? Because it is so much faster, I can script things, save and restore scripts, share accross customers etc.

One of the pain points of working with SQL Server Queries has Always been the full companyname in front of the real table name. For this reason Intellisense was more or less useless for NAV databases.

This is fixed in SSMS 2016!

When you type

select * from Custom

SQL Server Management Studio will show this

sql-2016

How cool is that!

You can download SSMS 2016 for free here

 

 

Tip #56 | Fix Error : Sorting cannot be done based on the XX field(s)

Today I’ve spent some time in NAV2016 figuring out this error message:

Sorting

As you know in NAV2016 you can sort on each column even if there is no key and even if it’s a flowfield. So why am I getting this error?

In my case the Test field is a Flowfield of the type COUNT to another table. This should work fine, so why doesn’t it work here.

Reason is that the SQL Data Type is Integer instead of VarChar. A very old trick to make a SQL Database sort a Code field just like the Native database.

The SQL Data Type in the “Other” table does not match this SQL Type hence the sorting fails.

Since I could not find any info online on this message I thought it was worth sharng.

Sorting2