Category Archives: Tips and Tricks

Tips and Tricks

Tip #68 | Using Read-Scale Out in Business Central to improve Performance

Another quick tip for something I’ve used this week to help out a QBS partner with performance issues on Business Central.

Since the last release it’s possible to issue read-only commands on a real-time copy of your Business Central database by using the DataAccessIntent property.

This allows API Pages, reports and queries to be executed outside of your production database which is ideal for Power Apps, Power BI and websites that for example only show status information on outstanding orders.

Then I remembered, “off course” we can also use that with the ForNAV report pack for financial reports that run longer, like the Inventory to G/L Reconciliation. (Which already runs 10 times faster than the out of the box version).

So here is a screenshot:

DataAccessIntent = ReadOnly

And the documentation

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/performance/performance-developer#using-read-scale-out

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/properties/devenv-dataaccessintent-property

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/database-read-scale-out-overview

IMPORTANT NOTE!!

You cannot use this for document reports! Document Reports in Business Central write to the database for logging purposes and VAT/Sales Tax calculation.

Document Reports are not good candidates for this feature since they are more or less WYSIWYG. They print litteraly what is in their source tables.

Also they leverage the Service Tier caching so they probably don’t even take a roundtrip to the database for most of the data they print.

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