Using HTML inside ForNAV reports

Earlier this week I was conducting a Reports ForNAV training in Bremen. As I was showing my students ForNAV multilanguage labels with HTML formatting we started talking about the other features that ForNAV has regarding HTML. My students suggested that it would be useful if users could write their own HTML code, save it in a blob field and then display it in a report. That is perfectly possible! In this blog post I use a Dynamics 365 Business Central Cloud ForNAV report with a per tenant extension but this will work with an On Prem Extension or plain old C/Side just as well. So, how does HTML work in a ForNAV report?

A while ago I was working with another ForNAV customer who wanted a flag for every item on a catalogue report so customers could see the origin of that item. We ended up with a simple ForNAV text box with this source expression:

Annotation 2019-10-17 205301

That results in a report that looks like this:

Annotation 2019-10-17 205451

So that is fun right? No messing about with saving images anywhere, just instant prettyness in our report. But we wanted something a bit more intricate, something we can use across multiple reports. Yes I know ForNAV has a template function. Please indulge my love of coding.

First thing I did was to create an extension with three objects, a table with the blob fields, a page to edit the blobs, and a standard ForNAV sales invoice. You can find the extension on my github page. I won’t go into detail here as it is pretty standard stuff.

I then used an online HTML editor to create a simple report footer text. I wrapped that in the HTML doctype labels and saved it in my new HTML blob table. Please note the %1 text I use as a placeholder for my company name. Yes, I could have created a nice ReactJS WYSIWYG HTML editor on my page but I really wanted to get this blog post out. Maybe later.

<p style="text-align: center;"><span style="background-color: #00ff00;">We at %1 do so enjoy your custom</span>. <strong>Please</strong> come back and spend <span style="color: #ff0000;">more</span> cash.</p>

Next step is to get this blob field and use it as a source expression in ForNAV. To do that I first added the record in the Records property of the report. To do this open the properties of the ForNAV report and drill down into the Records property and add the tables you want to get data from.

Annotation 2019-10-17 210857

The next step is to get the record from Business Central. For that we write some JavaScript code in the ForNAV OnPreReport trigger.

HTMLBlobRED.Get(1);
HTMLBlobRED.CalcFields('HTML');

decodeBase64 = function(s) {
var e={},i,b=0,c,x,l=0,a,r='',w=String.fromCharCode,L=s.length;
var A="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
for(i=0;i<64;i++){e[A.charAt(i)]=i;}
for(x=0;x<L;x++){
c=e[s.charAt(x)];b=(b<<6)+c;l+=6;
while(l>=8){((a=(b>>>(l-=8))&0xff)||(x<(L-2)))&&(r+=w(a));}
}
return r;
};

The decodeBase64 function is needed because in Business Central Cloud I get the content of a blob field as a base64 string.  In On Prem situations this is not needed.

Finally I created a new text field in a footer with this source expression:

decodeBase64(HTMLBlobRED.HTML).replace('%1', CompanyInformation.Name);

What this does is it decodes the base64 string in my blob field and in the resulting string it replaces the %1 placeholder with the CompanyInformation.Name field which is part of the dataset of this report.

The result is this:

Annotation 2019-10-17 213021

Some final thoughts. In this example I used a new ForNAV report that is part of my extension. That is not strictly necessary. The records property in a ForNAV report can access all installed extension tables without the need for dependencies. I just wanted a complete stand alone example set so I chose not to use the phenomenal ForNAV Report Pack.

Full disclosure, I do work for ForNAV as a freelance trainer/consultant. They have not encouraged nor paid me to write this blog. I write about products and technology that I love working with and I love working with and wholeheartedly endorse ForNAV.

Two more ways to break your extension

I realize I have been remiss in sharing with you my continued experiences with creating extensions for our Hybrid NAV 2018 database. This is mostly because I have been busy doing exactly that, building extensions. Mostly it goes very well. Sometimes it does not, usually because we mess something up.

Recently we ran in to two problems, both of which can be fixed in the same way.

As I said before, most of our problems arise from us creating extensions in a heavily modified NAV2018 database. This is a so called hybrid system where we have both C/Side modifications and extensions. It is not something we particularly like but it is something we, and many others, have to live with. This problem arose in us moving tables from C/Side to extensions.

In doing this we ran into a situation where an object was removed that an installed extension depended on. In another situation an object was added in C/Side that had the same type and ID that already existed in an installed extension. Normally this will just result in errors when you try to run these objects. However, if all your service tiers are down they won’t start up again so there is no possible way to remove either objects or extension. The only possible way to start the service tier is to uninstall the extension from SQL.

DISCLAIMER, do not attempt to remove records in SQL without first making a back-up! If you are not sure of what your are doing please ask someone who does!

Fortunately for us there is a table in SQL that holds the information on installed extensions. It is called ”NAV App Installed App” and you can access it from SQL Server Management Studio. It is also the only NAV App table that you can remove records from. Any changes to the other NAV App tables will result in you having to restore a backup. Be warned.

After removing the records for the offending extensions we managed to start our service tiers, remove or add the C/Side objects and reinstall said extensions. No data from said extensions was lost as they were just uninstalled.

I hope this helps you in creating your own extensions. Please share your own extension stories, I’d like to read them.

 

Interface extensions with workflows

A while ago I wrote about our Extension Design Principles. In it I discussed various ways to interface between extensions without having to take dependencies. One of these interface methods was the workflows Business Central is equipped with.

Because you can configure these workflows at run-time you can configure Workflow Events and responses from different extensions.

But how can you create new workflow events and responses? Quite easily in fact. I suggest you start by reading how you can configure existing Workflows by reading this excellent piece by ArcherPoint. That will give you a good idea about what you can do with workflows.

In the aforementioned Extension Design Principles I also spoke about how we want to move our email address validation to an API. As I said, we want a consistent check across all our ERP systems, web shops, and other platforms. But we don’t want to program a call to this API in all our extensions. And we don’t want our extensions to have to take a dependency on a base extension or something. As I said, we want to be able to have all our extensions as stand alone units in the App Source.

How to do it then? With a workflow of course. To the code!

Create the check email Azure Function

First thing I did was create an Azure Function for our email address check. As you can see in the code below it checks the formatting of the email address first and, if that is correct, checks to see if the email addresses hostname has an MX record. So a bit more reliable than the CheckValidEmailAddress function in CodeUnit 9520.
The DNS check is done with an online API. If you would consider taking something like this to production you should build or host something like it yourself. Or just use a DNS check library. Also, bear in mind that a domain does not need to have a MX record in order to be email enabled.

For the curious, you can read how to create an Azure Function in VS Code here.

After this I created a CodeUnit that calls this Azure Function. In it I work with Variants. As I said, this function needs to be callable from many different extensions and many different source records. This function is quite simple, just a simple call to the Azure Function. If it gets a response code 200 it shows a message saying the email address is correct. If the response code is 400 it throws an error.

Create the workflow response

Then of course the real issue of this blog post, how do I call this function from the workflow? For this you need to create a workflow response. Have a look in CodeUnit 1521. If you follow the code in there you see you need to do a few things to add a workflow response:

  • Create a function to add a response to the library;
  • Call that function from the event that is triggered when workflows are added to the library;
  • Call the response from the workflow using the event that is triggered when a workflow runs.

As you can see in the above image the steps are quite simple. A few highlights.

In the call to the WorkflowResponseHandling.AddResponseToLibrary you can see me use ‘GROUP 6’. These groups are used to add arguments to your workflow step. GROUP 6 adds a Field No argument. This is used to identify the email address field on the triggering record. Other groups are found on Page 1523:

  • ‘GROUP 0’: No Arguments
  • ‘GROUP 1’: Selects General Journal Template and Batch
  • ‘GROUP 2’: Selects the linked target page to open
  • ‘GROUP 3’: Create Notification
  • ‘GROUP 4’: Create Message
  • ‘GROUP 5’: Selects approval arguments
  • ‘GROUP 6’: Selects field
  • ‘GROUP 7’: Applies selected values to selected field
  • ‘GROUP 8’: Selects Response Type and User ID”

I have added a function to delete the added workflow responses from the library. This should be called on uninstallation of the extension. Unfortunately this is not yet possible with installer CodeUnits.

Once we have added the response to the library we can create an event subscriber that will run our code.

What happens here is really quite simple. The code gets the workflow response and its arguments. If the Function Name of the workflow response is one of ours we execute the code that belongs to it. The Variant and xVariant come from the workflow event and represent the record that triggered the workflow. As you see I use the Workflow Step Argument to get the Field No. that we want to use.

If the workflow response has executed, the code needs to switch the ResponseExecuted variable to true. This lets the event publisher know that the event has been dealt with.

Creating the workflow event

On the events side things work more or less the same way. First we create an event that can trigger the workflow. This particular event is added in an extension that adds a notification email address on the Job Queue Entry. Once we have created the event all we need to do is make sure that the OnValidate event on our new field triggers the workflow:

Configuring the workflow

Now we have created the workflow event and response we can configure them. The events that add the event and response to their libraries is triggered when you open the Workflows page. Nothing needs to be done for that.

Just create a new workflow. Set the event to the newly created event. Set the response to the new check email response. Just make sure that you select the email address field in the Workflow Responses.

As a bonus you can even use your response with the standard Dynamics events. As you can see in the image below the workflow trigger I use here is the change on the customer record. Unfortunately the standard language of my Business Central sandbox is Dutch so all the events are added in that language. Not a problem for most users but still something I feel Microsoft should address. As you can easily regenerate all the events and responses by clearing tables 1520 and 1521 and opening the workflows page it is not a big problem.

Conclusion

That is pretty much it. I’m sure we can all delve deeper into the workflows as I have not yet touched upon workflow templates of event predecessors yet. That is something for another day.

For now I want to urge you all to explore the workflow module and start adding events and responses to your extensions. That will make my job of integrating with them much easier.

As always, all the code is on GitHub. Happy coding!

Slaying the behemoth, Extension Design Principles

Fifteen minutes reading time.

Once upon a time my ancestors roamed the icy plains of Northern Europe. They hunted behemoths, a single mammoth that could feed and clothe a tribe for a long while. But even though there was a lot of food on a single mammoth it was a bit of a pain to catch one. And once it is dead it is dead, and you need to eat it sharpish. So slowly they became smarter and started raising cows. Cows are standalone animals. Kill one, eat it, and the rest of the herd will continue to munch grass, produce milk, and be generally useful. So, we became cowboys. And survived.

Cool though the new job title of cowboy is it is nowhere near as cool as Slayer of Behemoths. That is why I offer a free Slayer of Behemoths t-shirt to the first 10 developers to successfully migrate a Dynamics NAV codebase to extensions. Details at the end of this post.

If you have been following me at all for the past months you will already know that at The Learning Network, we have been busy creating our first extensions. We have had our first extension in production since June and the grand total is now at eight, and several more in the works. It has not always gone easily though, but you probably read about that as well.

Related: Confession day, I messed up our extension

Now then why eight, and counting, extensions? Why not one big one? Why not wait and embed an ISV style extension in AL? Why even bother?

That is a lot of whys. We have been asking ourselves the same thing. Or not actually, we just enjoy building cool stuff and extensions are cool. Yes, they are. Period. So, we just started building. But just building cool stuff is not enough, it needs to be well engineered, well programmed, repeatable, reusable, testable, and maintainable. So, when we started planning the migration of one of our bigger business units to NAV2019 Business Central I set to work working out our design rules for building extensions. And since everyone is talking about migrating to extensions these days, I thought I would share them here.

Before I delve into the tech stuff some housekeeping. This list is by no means complete or accurate. It is a living guide. That means I will continue to update this post as I make lots of mistakes learn more. I you have tips or tricks please let me know.

1, Identify what to move into an extension by looking for the edge cases.

What is to be an extension? We don’t have time to redesign everything in one go. Therefore, we start looking for edge cases. An interface with our parcel delivery company. An interface with our order picking software. A redesign of our method of renting books that was happening anyway. Adding functionality to the job queue. Nothing that really touches on anything vital.

As we move the edge cases to extensions, we will find that the really complicated core modifications start to reveal themselves. There is simply less code for them to hide in. And when they do, we will have learned a ton about moving functionality to extensions.

2, Design to market

Working at an end user it is easy to create extensions for our own use only. But designing and building them for the marketplace will force us to not only design and build them better. Our extension will become a standalone microservice by default.

And because we build for the marketplace, we must do proper setup, testing, translation, and documentation. Remember, Microsoft does not require those things to annoy us. They require it to help us create the best extensions we can build.

3, Design all extensions as standalone microservices

This is the core of all our design principles. Moving to Business Central is just a small part of an overall redesign of our application landscape. For our landscape we decided on a system of standalone microservices connected using an events bus. So for Dynamics we also decided on a microservice extension architecture. Doing so enables us to create vertical and isolated extensions that interface neatly with our other microservices. That means a small multidisciplinary team can work on those isolated features as a single project.

In order to move our NAV2018 add-on to a microservice extension architecture we decided on a few ground rules.

  • An extension is a standalone unit. Either a vertical unit that extends certain functionality or a horizontal unit that extends the technical working of the Business Central platform;
  • All extensions will be built as an extension on the standard Dynamics NAV2018 codebase (our current version, to be upgraded to Business Central on premise soon). We do build new Business Central events into our NAV2018 codebase where needed;
  • No extensions will have dependencies on other extensions except where we extend third party extensions. This is because it is harder to maintain an extension when it has dependencies all over the place, every time you upgrade an extension you need to rework all its dependents. Also it is harder to install updates;
  • Extensions will be as small as possible but not smaller. There is no problem with a 500-object extension if that is the smallest it can be.

Using those rules, we end up with this model. It should be recognizable for those who attended Tech Days or Directions.

Would be Architechture

That is great, but it leaves two fundamental problems

  1. How to connect with the remaining custom C/AL objects;
  2. How to connect different extensions to each other.

We can solve these two problems in several ways.

Interface Extension.

An Interface Extension is a new extension that takes a dependency on the two separate extensions that it wants to connect. It is only used to connect extensions, no business logic will be in the interface extension.

An example of this would be an interface between our order import and our rental model. Both are vertical units but when importing an order with rented items on it the rental model business logic needs to do something.

To do this we use the events in the extensions we want to interface with. Because the interface extension is the one that is dependent our microservice extension remains a stand alone unit. That makes it much easier to maintain the entire application.

Interface extensions are also used to connect our new extensions with what remains of our old C/AL code. For smaller interfaces you might even use RecordRefs instead of dependencies.

You might want to check out James Pearson’s excellent 3 part series on integration.

https://jpearson.blog/2018/11/12/integration-between-extensions-in-dynamics-365-business-central/

https://jpearson.blog/2018/11/19/part-2-integration-between-extensions-in-dynamics-365-business-central/

https://jpearson.blog/2018/11/28/part-3-integration-between-extensions-in-dynamics-365-business-central/

Workflows

Business Central has a workflow module that can be configured by the users. Our extensions will add their events and actions to the workflow module, so our application managers can configure the desired integration between extensions. More on this in this blog post.

ForNAV

ForNAV is the reporting tool we use to create output like invoices and packing slips, or management reports. ForNAV can use tables and fields from all extensions without having to create a dependency.

Web services and API

An extension can have its own Web service or API that other applications or other extensions can interface with without the need for dependencies. You might even subscribe to a Business Central API or web service in an Interface Extension.

Our model then looks something like this:

Final Architechture

Some final remarks on microservice design.

  • We will probably have blocks of code that will be cloned in several extensions. If this is something we absolutely want to avoid then this code should be moved to Azure functions or an API;
  • We might end up duplicating data. That does not matter. A key feature of a microservice is that it keeps on working if all other applications fail. We don’t want our extension to stop working if someone deletes an extension that ours depends on for data;
  • We will probably find there are others that have already created an extension that does what we need. We will use them, so we can focus on what we uniquely need. Don’t forget that we won’t have control over those extensions though. We use them as an API.

Some more information on microservice design:

https://martinfowler.com/articles/microservices.html

4, Everything that can’t be done in an App Source extension gets moved out.

Do we just move everything we have to an extension? No, we don’t. I once said that we can do everything we should do with extensions. Or we will be able to in the very near future. But what is we should and should not do?

The three principles we use to decide if something stays in Business Central.

  1. We do not modify the standard code;
  2. We don’t use Business Central as a development platform;
  3. We don’t use Business Central for supporting functions like string manipulation.

The first principle means that we will lose some functionality. We still have modifications in standard code. They have got to go. Even though we request events where Microsoft has not added them already we will have to live with some loss of functionality. If we cannot live with this then Business Central is not the right platform for us.

There is of course a caveat here. Microsoft is still refactoring the core application to make it easier for us to extend it. But we don’t wait until Microsoft is done with this. We move what we can into microservice extensions. When Microsoft is ready we can move the rest.

The second principle means that for us Business Central is a way to log transactions. A way to reliably track money and goods. And that is it. It is now so ridiculously easy to create a well performing and easy to use database application there is really no point in using Business Central as a development platform anymore. learn C#, .NET Core, JavaScript, HTML and CSS, and especially the Entity Framework. Everything that is either not in the Core Business Central application or too heavily modified to be moved to an extension should be deported from Business Central. Besides, if you build your own application you won’t ever have to worry about merging it into Microsoft’s code. Bonus.

If we must have data or even the UI of our web-based application available in Business Central, we bring functions from multiple applications together in a single interface in Business Central using control add ins and React (or Angular, or jQuery, etc). More on this in a future blog post.

The third principle means that we will do away with the tons of supporting functions we have in codeunits. We use Azure functions or APIs to do our string functions, date manipulation, e-mail sending, weird calculations, or whatever.

Case in point, we just found a function in one of our codeunits to check if an e-mail address is valid. That is something we want to use consistently in all our web shops, ERP systems, and other applications. We will stick it in an API. One less function to migrate to AL.

5, Design as an API

Our extension is someone else’s API. Someone somewhere will extend our extension. Someone will create an interface for it. They don’t want to redo their work every time we release an update. That is a sure way of building an unpopular extension no one will use. Some tips.

  • Use proper API design guides. See this post for details on this;
  • Don’t ever break API (the API of an extension is all tables and events but could be argued to include pages, queries, and external functions). If we must break API, we create a new major version of the extension while keeping the previous version available. Check out how Google publishes their JavaScript add-ins;
  • Be generous with events that others can subscribe to. Add events where needed. Check how Microsoft does that using their AL GitHub. They are doing it right;
  • Add web services and APIs for our extensions. Create many ways that others can interface with.

Rounding up

I hope I gave you some ideas on how we work with extensions. I’m sure there are many other ways to deal with the move to extensions. This one just happens to work for us. I am always open for feedback, let me know what you agree or disagree with.

Finally, to win your Slayer of Behemoths t-shirt all you need to do is share this post and send me evidence of that plus evidence of taking a number of microservice style extensions into production. All judging is done by myself and will be final. MVP’s and Microsoft employees are excluded from participation. Competition closed

Symbols of madness

As you know we have been happily developing extensions for a while now. Every once in a while I blog about some stuff I found out recently but it is mostly smooth sailing at the moment.

Today I was stumped though. For some reason my symbols were not loading. I make it a point to always develop extensions on a standard NAV2018 database (our current version, move to BC on prem is planned but not yet started) but sometimes I need to add functions or parameters that have been requested through Microsoft’s GitHub. And then of course load symbols. Now my symbols are always created automatically by using the finsql command line switch generatesymbolreference=yes as described here. And it always works. I thought.

So what had happened? I had updated my local NAV2018 to CU 9 a while ago. No problems there, just some bug fixes I needed for another extension. But when I added my C/Side function on my NAV2018 CU6 test server my symbols would not load. After checking my Launch settings about 100 times I finally checked my symbols files. That showed this.

Symbol versions

So my extension had symbols files for both CU6 and CU9. And of course the VS Code compiler uses the latest build. So I binned my .app files, downloaded symbols and published my extension without trouble.

As a bonus another symbols problem we found out a while ago. If you have multiple service tiers running your symbols wont generate with the standard batch script, even though it says it is working just fine.

finsql.exe Command=generatesymbolreference, Database="Demo Database NAV (11-0)", ServerName=.\NAVDEMO

What you need to do is add your service tier information.

finsql.exe Command=generatesymbolreference, Database="Demo Database NAV (11-0)", ServerName=SQLServer, navservername=Servicetier.domain, navserverinstance=DynamicsNAV110, navservermanagementport=7045

That should generate your symbols.

Have I forgotten something? Have tips and trick I don’t know yet? Please let me know.