Move Bespoke Symbols to Production

Today I had a small talk with my brother about generating custom symbols from C/Side and how to manage that.

Here is how I did it.

At the company I work for we have a DTAP environment and we (normally) only code in Development. Fobs and Extensions are moved from Development to Test, Acceptance and Production.

The quesion is how to control your symbols and move them together with each iteration.

Surely you can generate symbols in your production database but that might not be super smart. Alternatively you can move your Application App file together with the rest.

You need two PowerShell commands.

Unpublish-NAVApp -ServerInstance NAV2018PROD -Name "Application"

Publish-NAVApp -ServerInstance NAV2018PROD -Path "\\Symbols\Application.app" -PackageType SymbolsOnly -SkipVerification

You need to make sure that the flags for Development in the Server Config are false.

Dev Endpoint

Breaking Symbols

Sometimes Symbols can break if someone in a Development database changes an object in C/Side without saving the changes.

Another developer doing an extension can be hurt by that if he decides to refresh the symbols at the same time.

The simplest and official answer is to introduce the concept of distrubuted development where each developer has their own Development system and you have a build server creating the test envinronment if the build passes and execute the automated tests.

But in our ecosystem many partners seem to prefer centralised development and then you could consider to also disable the loading of the symbols on object changes and introduce a symbol database. (SDTAP).

Versioning

Another alternative might be to introduce versions of the Application symbols but this is something I haven’t tried myself.

Not sure if that would work but I thought it was worth sharing.

Question: How do you manage your custom Application Symbols?

Performance Measuring of Large Reports

In the ForNAV standard report pack we have a few reports that are traditionally slow when running. One of my design goals when developing these reports was to see if I can increase performance.

The names of the challenged reports will sound familiar to those in our channel for a longer time.

  • Aged Accounts Receivables & Payables
  • Inventory to G/L Reconcile

The latter only exists in the North American localization but whomever spends a lot of time on MiBuSo has seen the questions on performance of these guys.

Why are they slow?

Both reports have slow performance because they loop through the entry tables one-by-one which means they get slower over time. Both reports were created a long time ago. In case of the Aged Accounts Receivables & Payables report it was done before we had detailed entries.

Exactly how slow?

So, this is the question everybody asks and the only true answer is “it depends”. It depends not just on the size of your database but even more on the ratio between Master Data and Entries.

Also, you need a reasonable amount of data to test this, not just a CRONUS database with Microsoft Demo data.

Long live the upgrade business

When I started my freelance career 12 years ago I decided to step into upgrades. Not alone, but with the help of my good friend Tom Wickstrom. Tom has probably done thousands of upgrades over the last decades.

Tom picked two databases for me that I’ve used to test with. One database is about 60 GB and the other is about 50GB. This is a good representation of a professional bespoke NAV system.

The ratio’s in these databases are different, especially at an Item level.

50GB System 10 Years of Posting Data System A
No. of Customers No. of Cust Ledg. Entries Ratio No. of Detailsed Entries No. of Items No. of Item Entries Ratio No. of Value Entries Ratio
2741 71583 26,1 160287 380 1948702 5128,2 8198945 4,2
60GB System 11 Years of Posting Data System B
No. of Customers No. of Cust Ledg. Entries Ratio No. of Detailsed Entries No. of Items No. of Item Entries Ratio No. of Value Entries Ratio
9463 269694 28,5 552562 134114 1146037 8,5 2015607 1,8

On average each customer has made between 25 and 30 purchases in 10 years. The number of sales per item is the biggest difference as is the amount of value entries per item entry.

How do we Measure

The databases are installed on the same SQL Server. The servers are warmed up. We run the report once before we measure the results and then we take the average of three adjacent runs. We run using the Windows client. No Azure, No Docker, No VMWare or HyperV. Pure iron, bare metal. Each drive is an individual 500 gig ssd drive 

SQL Version                       2012
NAV Version                      2017
ForNAV Version                3.1.0.1460
Memory                              32GB
CPU                                       3.40 Ghz. Intel Core i7-4770
Disks                                     C Drive  SQL installed here. w. Database & server executables
                                              E Drive  MDF database file is here
                                            F Drive  NDF database file is here
                                            G Drive  LDF database file is here
 

 

Microsoft’s Performance

Inventory to G/L Reconciliation System A 12:20 Minutes/Sec
  System B 7:09 Minutes/Sec
Aged Accounts Receivables System A 0:17 Minutes/Sec
  System B 1:07 Minutes/Sec

 

ForNAV Performance

Inventory to G/L Reconciliation System A 1:25 Minutes/Sec
  System B 4:00 Minutes/Sec
Aged Accounts Receivables System A 0:04 Minutes/Sec
  System B 0:08 Minutes/Sec

 

Conclusion

The ForNAV reports are up to 8 or 9 times faster than the Microsoft RDLC reports. The difference gets smaller as the ratio between Master Data and Entries gets lower which makes perfect sense.

How did we do this?

Well, although it is not a secret, I am not going to tell you. We wrote this blog post to trigger you to look at our product.

There are a lot of goodies in our report pack if you are a modern programmer. Where feasible we use the MVC pattern, Dependency Inversion and Polymorphism. This means that the Aged Receivables and Payables report use the same code where possible which then is reused in the Statement report.

ForNAVLayout

JavaScript Objects

We use JavaScript Objects to show grand totals. In ForNAV you can code in JavaScript which includes creating objects that help you have clean and fast front/end (report-side) code.

Prevent C/Side from using ID’s used by Extensions

Last week the inevitable happened. I created a page in C/Side with an ID that I had already been used by an extension.

Microsoft is aware of this issue but does not want to prevent it from happening.

The problem is that at first everything seems to work. Your new C/Side page will run just fine. I only noticed it after a restart of the Service Tier because this actually does a check but you have to dive into the Windows Event log to find it.

The Fix

Extension objects are stored in the NAV App Object Metadata table. You can write a SQL Trigger that checks if a record exists in that table with the same ID and Type. This should show a message like this.

Error Extension

The Trigger can look something like this:

USE [NAV] -- change DB Name here 
GO

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[CheckExtensionObject]')) 
DROP TRIGGER [dbo].[CheckExtensionObject] 
GO 
CREATE TRIGGER [CheckExtensionObject] ON [dbo].[Object] 
AFTER INSERT
AS 
SET NOCOUNT ON 
 
DECLARE @ins_count int 
SELECT @ins_count = COUNT(*) FROM inserted 
 
IF (@ins_count <> 0) --BEGIN

IF ((select count(*) from [inserted] inner join [dbo].[NAV App Object Metadata] obj 
 ON obj.[Object Type] = inserted.[Type] AND obj.[Object ID] = inserted.[ID]) <> 0)
 RAISERROR('Object Already Exist as an Extension Object', 18, -1, '');
 
SET NOCOUNT OFF 
GO

With thanks to Jorg Stryk.

Translate Stuff the right (hard) way

Looking for feedback as always.

As you might know (or not) we ship a set of standard reports with ForNAV which are optimized for the product.

With these reports you’ll also get a set of tables, pages, codeunits and in the next version even a query.

Out of the box, ForNAV is translated to all the NAV 2018 languages plus Portugese.

For sure we also like to translate the standard reports and we want to do it right.

The LCS Translation Service

I’ve taken a look at the LCS Translation Service provided by Microsoft. This service runs on XLIFF files which is an optional format for Extensions.

Since our product still runs best on C/Side (you could run it as an extension if you so desire) I figured I could still use LCS and convert XLIFF back to C/Side as long as the translation is ok.

If you google on this subject you land on the blog from Gunnar and he has an example result on his GitHub.

Translate2

The screenshot shows the result of the translation by LCS to Dutch and this would not be something I would enjoy shipping.

The Alternative

Now I am not sure if I will make a complete fool out of myself but let me share how I get my translations.

As you know standard NAV has a lot of standard tables, pages and codeunits that are translated by Microsoft. Microsoft inherrited most of the translations from Navision as the base application never changed much since 2002.

The Navision translations were always very consistent and could be trusted.

You can export the translations from C/Side and that is what I did. I’ve downloaded NAV 2018 in the languages I needed and exported the translations into a file.

The next thing I did was import these files into some tables I’ve created in NAV. Now I have my own database with translations.

Translate

Codepages

There is one caveat. C/Side is not unicode. In order to make sure I get the correct characters in my strings I have a virtual machine that runs in Danish Codepage. My Danish improves with every report I translate.

Discipline & Common Sense

The next thing I did was to go through my objects one-by-one and searched for translations. As some of our reports are from the North American version of NAV such as the Inventory to G/L Reconciliation report some of the captions don’t exist.

Inventory Value is an example of that, and so is Received Not Invoiced.

However, Inventory Value (Calculated) exists. How hard is it to remove the one word from a string and use that translation. It feels a lot safer for good results than using the LCS (which I did not try).

Receive also exists as does Shipped Not Invoiced. Common sense helps me to create a decent translation.

Conclusion

Translation sucks but it is a requirement for the succes of your ISV solution. It is to be handled with care because there is only one first impression of your application and a weird translation can destroy that.

Clean code also helps. If you try to normalize reusable code into libraries you can re-use the translations once you have it.

Please share your idea’s in the comments.

Business Central-as-a-Framework

Let’s recap on the blog posts from the last three days.

Why C/Side deserves one more round. At least…

Visual Studio Code is an awesome editor but due to the nature/legacy of Business Central and many ISV solutions it can be less productive when moving large monolitical solutions one-to-one.

Solution is to break them up into smaller extensions with dependencies.

And then there are “Object Numbers”??

When taken out of the context of C/Side the old object ID’s are not so powerful as they once were. Adding the hybrid capabilities with C/Side and the nature of extensions to be decoupled it backfires quickly and silently.

Solution is to add a Namespace to each extension to make the ID unique. The ID itself can stay or be moved to auto-numbering in the back-end.

AL vs. JavaScript

Compared to other programming languages AL can be less intuitive to get started with and it behaves unexpectedly for modern developers. It requires a lot of effort for the Business Central team to maintain a language dedicated for only one application.

Solution is to move AL to TypeScript to reduce the learning curve for new developers and add modern programming capabilities.

Awesome… right?

Taken as constructive feedback I hope it is worth something. If not it can serve as food to digest and explanation for those new to our community for legacy bits and pieces.

Now what do we do…

One of the questions I got on my blog was how to take this product that was shipped by Microsoft and make the best of it.

Let’s try to point out the points that make Business Central stand out from other frameworks. It has a number of nice built-in features that not a lot of other frameworks have.

Reporting

Not something on the top of everyone’s list, but most business applications require some form of reporting or printing of documents. Think of shipping documents, labels, invoices or customs paperwork.

For most modern solutions printing to PDF is enough but sometimes printing to a real printer can be a requirement.

Business Central has that built-in without requiring a plugin.

Security

In frameworks like Angular there is no built-in security. You need to build it yourself and with modern requirements such as GDPR this is something you want to make sure this is done in a solid way.

Business Central requires very detailed security, maybe a little too detailed if you are unfamiliar with the datamodel.

Web Services

Everything is connected these days and a simple file-via-ftp is no longer considered high-tech, let alone safe. (Remember GDPR?). Web Services allow a safe and real-time way of transferring data. Business Central supports sending and consuming SOAP, REST and OData(4) and is shipped with a very solidly designed out-of-the box API.

Office 365 Integration

Within our community we take Azure Active Directory (AAD) and Open Authentication (OAuth) for granted but everyone who has ever try to build in these into their own platform understands that it’s not an easy task.

Because the Web Framework parts of Business Central are perfectly aligned with the technology used by the Office team the integration between the two is a seamless as possible. No other framework can match the level of integration.

Transaction Integrity

Last but not least. Business Central is one of the few frameworks that guarantees transaction integrity across extensions.

I was very unsure to mention this as a plus because it can also create huge problems since Business Central is built on SQL Azure and the main posting routines run on transaction isolation.

If you call a Web Service in your extension during a transaction there is a fair chance other people cannot use similar transactions in the same company.

To my experience, solutions that are architected in a more modern fashion tend not to need long running database transactions but I want to avoid another debate here.

Hybrid is the future

As mentioned earlier, I don’t believe in 1-on-1 porting of traditional business solutions designed for C/Side to be moved to a monolith extension.

The legacy solution should be re-evaluated and reflected to modern requirements.

In one of the next posts I will dive deeper into the possibilities given by the use of the API in combination with embedding parts of Business Central in your Web Application or the other way around.

I think we should start with explaining the design difference between using an API and using Extensions.

This will not be posted this week since I have my wedding anniversary to prepare for. So prepare for a quiet time ahead from my end. At least on my blog, not if you are my neighbor.

AL vs. JavaScript

Writing a blog post help me clear my mind and get feedback to my idea’s. Some of my readers are very loyal in their interactive feedback while others just sporadically leave a reply or contact me via the contact page.

For this post I also hope to get some feedback.

Before we get started, I am not a huge fan of either of the languages we are going to discuss today so it feels a bit like comparing two types of beer you don’t fancy while the good stuff is waiting in the fridge.

About Programming Languages

There is no such thing as the perfect programming language, every languages has good things and bad things and every language should be reflected to the era it was designed in.

Both JavaScript and AL are very old languages but AL beats JavaScript by almost a decade or so I guess which might explain some differences.

Some programming languages are backend languages or frontend languages. Both JavaScript and AL can be used for both serverside and clientside programming although AL, to my best knowledge, is always executed serverside even if it is programmed on the UI.

AL – The 1980ies

The AL programming language which is used in Microsoft Dynamics 365 Business Central is a product from the 1980ies which is important to know to be able to understand it’s context.

In those years there was a big debate about object oriented programming and computers where just getting fast enough to enable OOP from a memory perspective.

I cannot recommend the video’s and books from Uncle Bob often enough and he has a very good way of explaining working with the stack and how in OOP objects have constructors and why.

In those years OOP was considered complicated and inherritance was considered smart. We know now that inherritance should be handled with care and is a fast road to a huge mess then over-implemented. (Also look for video’s where Uncle Bob explains the diamond problem)

AL is based on Pascal and is not object oriented. This is for a good reason because Navision was not targeting the product to be customised by developers. Navision was targeting the product to be customized by accountants and end-users.

This is why the vast majority of the developers in our community, including me, do not have a proffesional programming background.

JavaScript – the 1990ies

JavaScript is a born in the cloud programming languages to solve the problem of cross plaform business logic.

In a sense the language could not be more different from AL even though it just looks like that.

By the time JavaScript was growing up Object Oriented programming was probably considered the only way to code.

Without going too much into details I do want to mention JavaScript is not type-safe (a Dynamic Language) and it uses prototypes rather than classes.

Why Did Microsoft Keep AL?

Millions of lines of code are scribbled down in AL. Probably much more than nessesairy because of the lack of clean code which I explain in this video.

All of these lines of code are at compiletime converted to C#. I explained a little bit of that in my previous blog.

A lot of new-generation developers have asked Microsoft for a long time why it is not possible to write C# code directly. I may explain more of that when I write a blog of how Extensions actually were supposed to work and where Microsoft went off-track with the initial design.

Pressure from the existing NAV partners is probably the reason why AL still exists.

Converting C/AL to AL

These days many, if not all, NAV partners struggle to convert their C/AL code to AL and Extensions. Most partners, or actually almost all without exception, find out that just converting the code is not going to make the magic happen.

A lot of solutions have to be re-evaluated. People have to go back to the drawing board.

A Perfect Momentum?

One could debate if it is smart to carry forward twenty year old code in a non-object oriented platform full of duplicated code.

If one has to rewrite most of the code anyway, in Business Central or outside, why do we not revamp the language too?

JavaScript & AL merged together?

About a year or so ago I was forced to learn JavaScript for my job as ForNAV. Michael Nielsen who is one of the developers of ForNAV decided to use JavaScript as a scripting language for the reports.

I was not super-exited because I had some failed attempts before to understand JavaScript. Lucky for me Michael was patient enough to teach me the basics and with YouTube and W3Schools I can find my way around.

Later on I was way more exited when Michael told me he had made C/AL database access work within JavaScript. I could not believe my ears.

But it is true. In ForNAV reports you can declare table objects as global variables and perform Get, SetFilter, CalcFields and what have you.

Imagine if one guy (true, not your average one) can make that work?

This video shows what how it actually works.

My Dream?

Ever since I learned Object Oriented programming I cannot write any AL code anymore without thinking “What if…”.

I try to mimic OOP as much as possible in my code. The database I work with has dozens of tables that I only use as a class and it makes my life somewhat easier. The amount of code that I refactored into classes in NAV is huge and the code became so much more robust, stable, performant and testable.

TypeScript

Last year I did a small project in TypeScript and I fell completely in love. It was as if all the good stuff from the languages I worked with were put together.

It will probably never happen, but boy, if you want to make me back enthousiastic about Business Central and Extensions we should move to TypeScript in small projects without object numbers.

Just kill AL, please.