Mastering BC 2.0

If you like enormous books that are chockfull of information on a wide variety of technical topics related to Business Central, you must drop everything that you are doing, RIGHT NOW, and let me tell you all about this book. I’ll even give you a link where you can order it.

Book Review Number Twelve

In early 2023 I got a message from Packt with a question whether I would like to help review another book. Over the years I have reviewed a bunch of books for Packt (this is number 12). It had been a while since the last one, so I was excited to put on my reviewer hat. This one was going to be a new and updated edition of a book called “Mastering Microsoft Dynamics 365 Business Central”. This is a very well-known reference book that is written by the dynamic Italian duo of Stefano Demiliani and Duilio Tacconi, both well known members of the BC community. Do yourself a favor and go follow their Twitter feeds (this one and this one), I’ll wait here.

The first edition came out in 2019, so it was about time that we got an updated version. For some reason I never got around to buying the first one, so I was fresh into this book without any preconceived notions about the content. Packt had sent me a table of content with a whopping 18 (yes, EIGHTEEN – and yes that is really spelled with just one ‘t’) chapters. VERY ambitious indeed…

It took most of 2023 to review these chapters. The sheer amount of content made it so that it regularly took entire weekends to process all the stuff and the code that was written. The last one was submitted in mid October, and the guys spent months processing all the feedback to finally release the book. They spared no effort to write the best book that they could, and it shows.

All the Things

You get 18 chapters on the most important technical topics in our BC world. I was going to write something specific about the content, but I’ll refer you to Stefano’s blog post to read more about that.

What I appreciate about this book is that each one of the chapters goes into enough meaningful detail that you feel you have a good grasp of the topic at hand. At the same time, you never get the feeling that you are taken too far into the matter. Wherever that is relevant, each chapter comes with a repository of sample code, to illustrate the topic. If you want you can write your own, and if you want to just follow along with the samples in the book, you can just open the code and poke at it.

Don’t let the sheer size of this book discourage you. Yes the book itself is MASSIVE, but the chapters individually are very manageable. There are other books that go into much more detail, but you will want to have this one to get yourself started on the topics. Another great feature is that you get access to all the book’s sample code in a GitHub repository.

My one ‘negative’ feedback was that in many instances the chapters contained the full object’s code. Sometimes you get pages of source code, which I personally think is a distraction. When I read a book like this, I have VSCode open with the sample code so I don’t need the full object in the book. I would have chosen to only include parts of the source code and highlight the important bits. When you only have the book in your hand that it can be useful to see the whole object though, so there’s an argument for whole objects anyway. Either way, you get lots of samples and that makes me very happy.

What if I already have the First Edition?

The thing is, I have not read the first edition, so I can’t speak for the content of that book. What I CAN tell you is that BC has evolved a TON since 2019, and all of the new capabilities get their due attention in this second edition. Things like Telemetry and AI were not yet available in those days, and even well-established topics have gotten a serious update, such as VSCode extensions and how DevOps can be done, and a ton of other stuff.

If you have the First Edition, it is time to retire it. This Second Edition deserves a spot on every technical BC person’s desk, or at least within reach.

Where can I Get it?

As per usual, two places where you can get it:

  • The Packt website – Packt’s online reader is really nice and searchable, I prefer it to Kindle, and there seems to be a discount on the eBook right now. Plus, being able to start reading right away is also nice, because Packt is not known for the best delivery times
  • Amazon – WAY better delivery times than Packt, so that’s where I would get it if I only wanted the paper version. The Kindle edition is more expensive than the eBook on Packt.

Mad respect and ALL the credit goes to Stefano and Duilio. In addition to having families and successful careers, they did a phenomenal job of writing this book for us. Writing this book was a monster of a task and they pulled it off. I am super proud to be a small part of making this the book that it is. Now click one of the two links and order the book!

NAV/BC Version Numbers

Many others have posted this information before. I’m just putting this on my own blog because I have noticed a decrease in the number of ‘old’ blogs out there and this is getting harder to find. I’m just securing the information so that I know I’ll have it for as long as I keep my blog up.

When I started as a Navision developer in March of 2000, version 2.5 had just come out. Most of the clients that I worked for were on 2.01. The version that is current today (v21 for the 2022 Wave 2 release, and v22 is just a couple of months away) directly derives from that version back in early 2000. There are earlier versions than that, and you can search for the history yourself, but I have never encountered anything older than 2.01. I just wanted to have a handy list as a quick reference.

Here They Are

  • Navision in various incarnations (Financials, Attain, Microsoft Business Solutions) 1, 1.1, 1.2, 1.3, 2, 2.01, 2.5, 2.6 (also versions with manufacturing and another with Advanced Distribution, as well as specialty versions for the first NAS 2.65 a, b, c, d, and e), 3.00, 3.01, 3.10, 3.60, 3.70, 4.0, 4.0 SP1-SP2-SP3, 5.0
  • Dynamics NAV 2009 is version 6 – also had SP1 and then R2
  • Dynamics NAV 2013 is number 7 – also had R2
  • Then there were NAV 2015 (8), 2016 (9), 2017 (10), and 2018 (11)
  • Version 12 was the first BC version, AKA Business Central 2018. For a while there (at least through BC14) was a bit of a split personality with the splash screen saying “Microsoft Dynamics NAV Connected to Dynamics 365 Business Central”, a very catchy and easy-to-remember 24-syllable name
  • From then on there is a release every 6 months:
    • 13 – October 2018
    • 14 – April 2019 (this was the last version with C/SIDE)
    • 15 – BC 2019 wave 2
    • 16 – BC 2020 wave 1
    • 17 – BC 2020 wave 2
    • 18 – BC 2021 wave 1
    • 19 – BC 2021 wave 2
    • 20 – BC 2022 wave 1
    • 21 – BC 2022 wave 2
    • and so on and so forth

update 2023-02-14: added some missing versions

Save Report Filters

Going back into the stone age, report objects have always remembered the filter values for the next time that you run the report. What I never noticed was a capability to actually save filter values (yes I now realize that it’s been in there for years now). It’s similar to saving a view of a list page, but for reports. It works a little hinky but let me try and explain.

What I Am Talking About

You may have noticed it in some (but not all) reports, processing-only or actual printed ones. Right at the top of the request page, you see a dropdown box with the words “Last used options and filters”. Click the dropdown arrow and that should show you an option to “Select from full list”.

This opens the “Select – Report Settings” page. The page actually has nothing to do with any settings, but it does show you a list of saved values for the options and filters of the report object. In my mind I am calling it a ‘view’.

I just created a bogus empty report just to show you the options, so ignore the content of the report itself. The interesting part of this page is what you can do. When you click the ellipses, you get a number of options.

  • Delete – obviously to delete the currently selected option
  • New – creates a new record, where you can give the saved value a label and you can define whether this view is for all users or not. Assigning the view to a specific user is done on the list page itself
  • Copy – creates a new record with the same values as the one that’s called “Last used options and filters”
  • Edit – This only works on custom views, and it runs the report’s request page where you can enter the options and filter values for the current view

You can assign the view to a specific user or share it with all users. If this capability is enabled for a report, you should be able to pick from the list of views right from the request page.

How To Enable This

You need to do two things to enable this capability.

  • First, you set the ‘SaveValues’ property on the report’s requestpage. If the report doesn’t have a requestpage, you can add one with just the property and no controls
  • Second, you need to run the report. When you first deploy the report with SaveValues turned on, it does not yet have a view saved. Run the report with any filter/option value and it should create this record for you
requestpage
{
    SaveValues = true;
}

A Little Hinky

To me it feels a little unfinished, like it was rushed into the system. To begin, it’s inconsistent in labeling. On the requestpage it is called “Use default values from” which is incorrect. This is about ‘saved’ values, not ‘default’ values. Then when you open the full list, it is labeled “Select – Report Settings” which again doesn’t feel right. I don’t consider filter values a ‘setting’. Then when you click ‘New’ it opens a screen that is captioned “Edit – Pick Report”… I mean come on… Finally, having to run the report in order to even get the dropdown is not very user friendly. When I first tested this I thought I had done something wrong because the dropdown would not show.

Another big drawback is that the SaveValues property is not available in Report Extensions. You’ll have to copy the standard object in order to provide the capability.

Regardless of its hinkiness and shortcomings, the feature itself is great, especially when you have periodic activities to run for different sets of filters. it’s really nice to have the ability to preconfigure sets of option/filter values. It has the potential to increase productivity and eliminate typos in entering filters. In my opinion, this feature should be enabled by default.

Document Attachments

One of my clients had asked me to add Document Attachments to Bank Deposits. Thinking this was a quick and easy one I added the factbox, set the link, and went on with my day. When my client said they see all attachments for all records, I realized it is a little more involved. It took some time to figure out how it actually works, and this post explains the whole thing, including how to get document attachments through the posting process.

How Does it Really Work?

The reason why it’s not so simple is because the Document Attachment works with RecRef instead of a hardcoded table relationship. Take a look at the Document Attachment table (table number 1173 in the base app) for the field definitions. I’ll focus on single field PK records in this post, so tables where a single Code20 field has the unique identifier of the record. The more complex compound PK works the same, you just need to set more fields.

Standard BC has a limited number of tables that have document attachment capabilities. If you want to add another table, whether a custom table or another standard table, you will need to subscribe to some events to make that work. Let’s first look at how standard document attachments work.

Open Document Attachments

Let’s take a look at the Customer Card page in standard BC. The “Attachments” action has the following OnAction trigger code:

trigger OnAction()
var
    DocumentAttachmentDetails: Page "Document Attachment Details";
    RecRef: RecordRef;
begin
    RecRef.GetTable(Rec);
    DocumentAttachmentDetails.OpenForRecRef(RecRef);
    DocumentAttachmentDetails.RunModal();
end;

The “Document Attachment Details” page is where the magic happens. If you drill down into the ‘OpenForRecRef’ function, you will see that it takes a RecRef variable (which in this example is looking at the current Customer record) and sets filter on the table ID of the RecRef and the value of the PK field of the record itself. This is the function that defines all the tables in the standard BC app that have Document Attachments.

The important thing in this function is the call to OnAfterOpenForRecRef, which is an event publisher that we will subscribe to later. This event gives you the capability to set a filter to any table. Note that this is just a filter on a page. All that this does is make sure that you only see the document attachments for this particular record.

The Document Attachments Factbox

Another way to give the user access to the document attachments is the “Document Attachment Factbox” that you can see in the factboxes area. In the standard app on the Customer Card, the SubPageLink property links the “Table ID” field to a hardcoded value 18 (which is Customer table’s object id). If you want to create your own link, you should use the table name instead of its number. So we will be linking to the “Bank Deposit Header” table, so our constant value will be Database::”Bank Deposit Header”.

Take a look at the OnDrillDown trigger of the NumberOfRecords field in the factbox. It first sets up the RecRef, which is again hardcoded for the standard range of tables that have document attachments. Then it executes the same logic on the Document Attachment Details page as the action mentioned above.

Note the OnBeforeDrillDown function call in the ‘else’ leg of the ‘case’ statement. This is the event that you need to subscribe to in order to properly filter the document attachments for non-standard tables.

What is important to understand about this particular factbox is that records are not entered directly into the factbox. You have to click an action that adds the record, and as a result the “No.” field in the factbox is NOT populated by the page link.

Creating New Document Attachments

So far we’ve only looked at how to display the proper document attachments to the user. What’s left is how BC actually stores these records. The part that is tricky is not about getting the file itself, but how BC gets the value from the RecRef. The function that stores the values from the RecRef into the document attachment is called InitFieldsFromRecRef. You can see that this function again goes through all of the same hard coded standard BC tables that we’ve seen before, and it provides an event publisher called OnAfterInitFieldsFromRecRef that you can use for additional tables.

Document Attachments for Additional Tables

Alright, now let’s put it all together for a new table. I recently added this for Bank Deposits for a client of mine, so I’ll use the same table to illustrate. I’ll focus on the new ‘Bank Deposit Header’ and its Posted sibling. The new implementation of bank deposits can be found in the “_Exclude_Bank Deposits” app that is part of standard BC.

To get started, create a page extension for the Bank Deposit and the Posted Bank Deposit pages, and add the Document Attachment factbox. You can copy this from the Customer Card and change the links appropriately. Note that the records that you will see when you drill down into the details are filtered on the table but not by the PK value of the record that you are looking at. In other words, just adding this factbox will only give you a list of all the Document Attachments that are linked to ALL (Posted) Bank Deposits.

Finally, create a new codeunit, I called mine ‘DocAttachmentSubs’.

Filter the Details

First, we need to make sure that the “Document Attachment Details” page is filtered on the correct Bank Deposit number. For this we subscribe to the OnAfterOpenForRecRef event.

[EventSubscriber(ObjectType::Page, Page::"Document Attachment Details", 'OnAfterOpenForRecRef', '', true, true)]
local procedure DocAttDetailsPageOnAfterOpenForRecRef(var DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    MyFieldRef: FieldRef;
    RecNo: Code[20];
begin
    if RecRef.Number in [Database::"Bank Deposit Header", Database::"Posted Bank Deposit Header"] then begin
        MyFieldRef := RecRef.Field(1); // field 1 is the "No." field in both tables
        RecNo := MyFieldRef.Value();
        DocumentAttachment.SetRange("No.", RecNo);
    end;
end;

Filter the Factbox

Next, we need to make sure that the details are filtered properly when the user clicks the DrillDown from the document attachment factbox. For this we subscribe to the OnBeforeDrillDown event in the factbox.

[EventSubscriber(ObjectType::Page, Page::"Document Attachment Factbox", 'OnBeforeDrillDown', '', true, true)]
local procedure DocAttFactboxOnBeforeDrillDown(DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    BankDepositHeader: Record "Bank Deposit Header";
    PostedBankDepositHeader: Record "Posted Bank Deposit Header";
begin
    case DocumentAttachment."Table ID" of
        Database::"Bank Deposit Header":
            begin
                RecRef.Open(Database::"Bank Deposit Header");
                if BankDepositHeader.Get(DocumentAttachment."No.") then
                    RecRef.GetTable(BankDepositHeader);
            end;
        Database::"Posted Bank Deposit Header":
            begin
                RecRef.Open(Database::"Posted Bank Deposit Header");
                if PostedBankDepositHeader.Get(DocumentAttachment."No.") then
                    RecRef.GetTable(PostedBankDepositHeader);
            end;
    end;
end;

So now when the user clicks the drilldown, BC will set the RecRef to look at the (Posted) Bank Deposit, which is then sent into the details page, which then knows how to properly filter.

Set the Right Link

The last thing we need is to make sure that new document attachments have the (Posted) Bank Deposit number, by subscribing to the OnAfterInitFieldsFromRecRef event of the Document Attachment table itself.

[EventSubscriber(ObjectType::Table, Database::"Document Attachment", 'OnAfterInitFieldsFromRecRef', '', true, true)]
local procedure DocAttTableOnAfterInitFieldsFromRecRef(var DocumentAttachment: Record "Document Attachment"; var RecRef: RecordRef)
var
    MyFieldRef: FieldRef;
    RecNo: Code[20];
begin
    if RecRef.Number in [Database::"Bank Deposit Header", Database::"Posted Bank Deposit Header"] then begin
        MyFieldRef := RecRef.Field(1); // field 1 is the "No." field in both tables
        RecNo := MyFieldRef.Value();
        DocumentAttachment.Validate("No.", RecNo);
    end;
end;

You should now be able to create new document attachments for unposted and posted bank deposits. All that’s left is to get document attachments to flow through the posting process.

Posting

Document Attachments are not intrinsically difficult. In the end they are just records in the database. The records are identified by their Table ID and their PK values. For Bank Deposits the PK is a single Code20 field, and there are two versions of the table. All we need to do is write a little loopyloopy that reads the records for the unposted record, copy it into the posted record, and get rid of the old ones. Would be cool to just change the records, but since both the Table ID and the record identifier are all part of the PK, you can’t do a ‘Rename’ because then you’d have to sit there and click confirmations all day long.

For Bank Deposits, you can use the OnAfterBankDepositPost event in the “Bank Deposit-Post” codeunit.

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Bank Deposit-Post", 'OnAfterBankDepositPost', '', true, true)]
local procedure BankDepositPostOnAfterBankDepositPost(BankDepositHeader: Record "Bank Deposit Header"; var PostedBankDepositHeader: Record "Posted Bank Deposit Header")
begin
    MoveAttachmentsToPostedDeposit(Database::"Bank Deposit Header", BankDepositHeader."No.",
                                    Database::"Posted Bank Deposit Header", PostedBankDepositHeader."No.");
end;

local procedure MoveAttachmentsToPostedDeposit(FromTableId: Integer; FromNo: Code[20]; ToTableId: Integer; ToNo: Code[20])
var
    FromDocumentAttachment: Record "Document Attachment";
    ToDocumentAttachment: Record "Document Attachment";
begin
    FromDocumentAttachment.SetRange("Table ID", FromTableId);
    FromDocumentAttachment.SetRange("No.", FromNo);

    if FromDocumentAttachment.FindSet() then begin
        repeat
            Clear(ToDocumentAttachment);
            ToDocumentAttachment.Init();
            ToDocumentAttachment.TransferFields(FromDocumentAttachment);
            ToDocumentAttachment.Validate("Table ID", ToTableId);
            ToDocumentAttachment.Validate("No.", ToNo);
            ToDocumentAttachment.Insert(true);
        until FromDocumentAttachment.Next() = 0;
        FromDocumentAttachment.DeleteAll();
    end;
end;

I have this in a separate function because I also had to make it work for the old Deposit implementation. You could totally combine this into a single event subscriber.

I’m thinking about creating a video about this topic. Let me know if this was useful in the comments and if you’d like to see the video.

Obsolete But Not Gone

This post was born out of a bit of an embarrassing situation, in which I had to obsolete some fields from an AppSource app. Just a quick one to let you know what the official process is, and also that you don’t have to wait for the next major version of BC to fully obsolete a field.

The ‘Situation’

This app has table extensions for the Sales Line table and the posted sales line tables (invoice and credit memo). Not until their first live implementation did we find out that the field numbers in the credit memo line table were not aligned, so at that point BC will yell at you that field types are not compatible. Oops….

For those that don’t understand, let me explain. Field values in the Sales Line table are copied to their posted counterparts by a command called ‘TransferFields’. This command basically copies all the field values from the source table into the target table. Couple things to note: the fields must have the same field numbers, and they must have the same data types. In our case, we had fields 1 and 2 in the Sales Line table, and their corresponding fields in the posted credit memo line had field numbers 2 and 3. The posting process tried to put a number 2 value into field number 1, with the predictable result that there was a data type mismatch.

Why is this a problem? Can’t you just change the field number? Well no, because once an app is published on AppSource, you are not allowed to make any ‘breaking changes’, and renumbering a field is considered a breaking change. Doesn’t matter how I personally feel about that (I don’t agree that field numbers are breaking) and so we did not have any other choice but to use the obsoletion process.

As a side note – yes this exposed a serious issue in our process. Posting a credit memo had clearly not been tested, and matching field numbers is something that a BC developer with my experience should never have a problem with. To my client’s credit: no fingers were pointed, we addressed the issue, and we shared the cost of fixing it.

Obsoleting a Field

Alright, so the official process of obsoleting a field is described in the ‘deprecation guidelines‘. Skip the preprocessing piece if you have not seen that before, and focus on the steps in making code obsolete. There are plenty of blog posts that explain the process itself, so I will not go into any detail.

My main concern was how fast can we get this into AppSource. The process to make a field obsolete has two stages:

  • First, the ObsoleteState of the field is set to ‘Pending’.
    • This means that the field has been marked, but it can still be used in the app
    • The purpose of this stage is to flag the code to any party that has an extension of the code, so that they can take steps to address the change
    • All references to the field will be shown in the VSCode problems window with a warning. There is a reason and a tag property that is used to define which in BC version the change will become permanent
  • Second, the ObsoleteState of the field is set to ‘Removed’.
    • In this state, the field still exists but it cannot be used any longer
    • All references to the field will show up as errors in the problems list

There is a perfectly valid reason why there are two steps. My concern was how long it would take us to get the fields to be removed. The documentation does not address the required AppSource timeline, and I could not find any definite answers in Yammer. The only timeline reference that I could find in any Microsoft documentation was that code must be in ‘Pending’ state for one entire major version. This issue came up in early October, days after 2022 wave 2 was released. If this was true, we would have to wait until 2023 wave 1 (April next year!!) to get the fields obsoleted.

What We Did

Some partners assured me that there is no mandatory wait time for a whole major cycle. The intention of that timeline is not to limit partners but a practice that Microsoft uses. This is to make sure that the partners always have at least one full release cycle to address any compatibility issues due to obsolete code in the base app.

So, with that in mind we went to work. What saved us is that this particular app only had one implementation live, so all we had to do is make sure that they upgrade the app to the ‘Pending’ version as soon as humanly possible. Depending on the number of live implementations you have, this could take longer. I actually don’t even know if there is a dashboard where you can see the live versions that are in use.

I have to say that I was very impressed with how smooth the process of pushing a new version of an app into AppSource is now. After properly testing the change, we pushed the ‘Pending’ app to AppSource, and that was done and ready to publish within a day. The end user then upgraded the app, and we pushed the ‘Removed’ app to AppSource right away. We were able to address the issue within days, and the client lived happily ever after.

Repair Companion Tables

One of my clients has been seeing these weird data issues when migrating databases to the cloud. The most important purpose of this post is to point out a handy data tool that is buried deep in the application, read on to learn what I am talking about.

The problems that my client is experiencing mostly have to do with various ways in which they get data from OnPrem to the cloud. One way is to use the cloud migration tools, another is configuration packages. In addition to using different tools, it feels like there may be some issues with the BC platform’s capability to properly manage data in table extensions. I’ll tell you why I think that.

Missing Data

The first indication was a problem where invoices were migrated into the cloud. The migration process seems to have completed, and the posted invoice list shows a list of invoices. The weird part is that when you try to open an invoice, it shows an empty invoice page. Click ‘View Table’ from the page inspector, and you get nothing, a list of zero records even though the posted invoice list shows the records. Go to the admin center to look at the capacity, it tells us there are like 1154 invoices but when you drill down into the number you get another empty list.

This feels very familiar to me, very much like when a lot of companies tried to migrate straight into SQL Server and we would see null field values. As we all know, BC can’t handle null values. Instead of getting an error saying ‘there are null values!! I don’t know what to do!!’ you get weird behavior like empty lists for tables that you know have plenty of records.

Solving The Problem

With the explosion of moving functionality into separate apps, I had a feeling that the problem had something to do with table extensions (which as you know have added fields in what is called ‘Companion Tables’). I had posted the question on Twitter and there were suggestions to uninstall and re-install apps. This worked sometimes but not all the time.

The original Tweet asking for help on this issue

What it feels like to me is that either there are null values in records, or maybe records are missing in companion tables altogether. We don’t have access to Azure SQL so there is no way for me to actually prove that there is an issue with table extensions. SOMETHING is wrong here though, and for the longest time the only way that I thought we could fix it was to uninstall/re-install apps until the issue was fixed. The reason why this works is that each time you install an app, it will update the schema and make sure that data integrity remains intact. In other words, it will make sure that all records in the main tables have corresponding records in all companion tables.

And then I became aware of a VERY handy little tool. This tool is not available when you are working in local containers, but when you are in a cloud tenant, you will have access to it. I’m talking about the “Repair Companion Table Records” process in the “Cloud Migration Management” page.

The tool will go through all tables that have table extensions, and make sure that each record in extended tables has a corresponding record in each companion table. I still can’t prove my theory but I do know that running this process fixed the problem for my client.

Partial Records with SetLoadFields

Fetching and updating records has historically been the greatest culprit of performance problems. The standard way that BC retrieves records is very expensive, since it will always get ALL the fields of a table (and its brethren companion tables). This post covers a (relatively) new option called SetLoadFields, which is used to specify the fields that you want to retrieve.

So What’s the Problem?

The database engine for BC is SQL Server for OnPrem and Azure SQL for SaaS; the business logic translates database operations into T-SQL statements at run time. By default, it issues a SELECT * and that means that for every standard database call, BC retrieves ALL fields from the table. Good for us developers because we never have to think about which fields to fetch. From a performance point of view though this causes a MASSIVE superfluous overhead in data traffic. Some of the most used tables in BC have bazillions of fields, and in any business logic scenario you never need more than a handful of fields.

The problem is exacerbated by the presence of table extensions. Each table extension is represented in the SQL database by a companion table that shares the primary key with the main table. Every time that you retrieve records from the main table, the system also retrieves the fields from the companion tables by issuing a JOIN on the PK fields. You can imagine a popular table like the Sales Line with a dozen table extensions; a SalesLine.FindSet command generates a SQL statement that includes a dozen JOINs.

The problem is that the number of fields that are included in SQL statements has a disproportionate effect on query performance. Read the post that I link to below for more details, but what you need to know is that the same query with all fields can take hundreds of times more than retrieving just half a dozen fields.

Only Get What you Need

To eliminate this overhead, we now have a SetLoadFields command. Basically, what you can do with this command is to define the fields that are included in the SQL statement. Instead of getting all fields and get data that you will never use, you tell BC that you only want your handful of fields.

Need an address from a Vendor? The external document number from an invoice? An Inventory Posting Group from an Item? You don’t have to read 6 million fields to do that anymore.

procedure ShowSomeCode()
var
    Vendor: Record Vendor;
begin
    Vendor.SetLoadFields(Address,"Address 2",City,"Post Code");
    Vendor.Get('10000');
    // do stuff with the address fields and ignore the rest
end;

This code sample generates a SQL statement with a SELECT on just those few fields that are defined in the SetLoadFields command, and it should ignore the companion tables altogether since these are all main table fields.

Read the documentation here and make sure you read how to use it here. For more technical in-depth information on what to do and what happens under the hood (way above my head there), read Mads’ posts here and here.

New Habits

In my day-to-day life as a BC developer I don’t normally see SetLoadFields commands. I even checked the standard objects and it’s actually quite surprising how little it is used there. In a previous life I did a LOT of performance troubleshooting, and this would have been a tremendous help in solving lots of performance problems. I know I will try to make using this command a habit.

Create JSON in AL

Last year I developed an integration to an external REST API from Business Central. One of the things that I had to learn is how to deal with JSON. We now have a bunch of different JSON data types, and if you’re just getting into them, they are hard to keep apart. With this post, I’ll try to explain as easy as I can make it, how to create a JSON object in AL.

JSON Basics

First, of course, the basics.

  • JSON is short for ‘JavaScript Object Notation’, follow this link to read the actual standards but if you’re new to this don’t though, it will only confuse you
  • It’s kind of like XML in concept but much easier to read. No attributes, no formatting rules, just a collection of key/value pairs
  • The equivalent of an ‘XML document’ is called a ‘JSON Object’. The start and end of these JSON objects are marked by curly braces (these {}).
  • Keys and values are always between double quotes (unless they are in non-text data types, but you can always put values in double quotes), separated by a colon. Multiple key/value pairs are separated by commas. This is a very simple JSON object with some key/value pairs:
{
    "A Key": "Example One",
    "Another Key": "Another Value",
    "Number Value": 42,
    "Boolean Value": false
}
  • Nesting is done by adding a new JSON object as a value, like this:
{
    "A Key": "Example Two",
    "Nested thing": {
        "First nested Key": "nested value",
        "Second nested key": "some other value"
    }
}
  • A list of JSON objects is called a ‘JSON Array’. The only restriction here is that each of the objects in an array must be structured the same, otherwise it would not be an array. The start and end of a JSON array is marked by square brackets. Each object has its own start/end curlies, and they are separated by commas. Here’s a simple example:
{
    "A Key": "Example Three",
    "List of Things": [
        {
            "name": "thing 1",
            "age": 10
        },
        {
            "name": "thing 2",
            "age": 12
        }
    ]
}

Note that the objects inside the array are identical in structure. This is really it, you should now be able to decipher any JSON response from any web service.

JSON Data Types in AL

In AL, there are 4 different JSON data types. All of these data types are .NET types that are wrapped in AL data types. You don’t have to worry about constructing these variables, they take care of themselves. All you have to do is make sure that you start with a fresh one, so pay attention to the scope of your variables. If you are not sure about that, use the Clear keyword to empty it out.

  • JsonObject – this data type represents an actual JSON object. It has properties and methods, and you use those to build the object as shown in the code examples below
  • JsonArray – this data type is also an object with properties and methods, and it contains the stuff that is between the square brackets
  • JsonValue – this represents a single value of a simple data type like a Text, an Integer, or a Boolean
  • JsonToken – in the JSON world, the ‘Token’ is similar to a Variant, and it can be either one of the other three Json data types. If you are not sure about the content of a JSON element, you can always use a Token as a data type

Show Me The Code!

When I started this section I was going down a rabbit hole of long sentences and complicated explanations. As I was reading what I wrote I realized that describing these things actually was not making any sense at all. Let me just give you the code, and if you have any questions about this please leave a comment below.

Each example refers to the JSON examples in the ‘JSON Basics’ section above.

Example One

The first example is very straight forward. This object only has simple key/value pairs, so we can simply add those to the object, like this:

    procedure ExampleOne()
    var
        MyJobject: JsonObject;
    begin
        MyJobject.Add('A Key', 'Example One');
        MyJobject.Add('Another Key', 'Another Value');
        MyJobject.Add('Number Value', 42);
        MyJobject.Add('Boolean Value', false);
    end;
Example Two

The second example has another JSON object as one of its values. The ‘Add’ method of the JsonObject data type takes a string as the Key name, and a JsonToken as the value parameter. In the first sample those were simple datatypes, and for this second sample we’re going to create another object to use as the value parameter, like this:

    procedure ExampleTwo()
    var
        MyJobject: JsonObject;
        NestedJObject: JsonObject;
    begin
        MyJobject.Add('A Key', 'Example Two');

        NestedJObject.Add('First Nested Key', 'nested value');
        NestedJObject.Add('Second Nested Key', 'some other value');
        MyJobject.Add('Nested thing', NestedJObject);
    end;
Example Three

The final example includes an array of objects, so we’ll build those one step at a time. Everything just coded in there, I hope you see how the array objects should be refactored into a re-usable function.

    procedure ExampleThree()
    var
        MyJobject: JsonObject;
        ThingJObject: JsonObject;
        MyJArray: JsonArray;
    begin
        MyJobject.Add('A Key', 'Example Three');

        Clear(ThingJObject);
        ThingJObject.Add('name', 'thing 1');
        ThingJObject.Add('age', 10);
        MyJArray.Add(ThingJObject);

        Clear(ThingJObject);
        ThingJObject.Add('name', 'thing 2');
        ThingJObject.Add('age', 12);
        MyJArray.Add(ThingJObject);

        MyJobject.Add('List of Things', MyJArray);
    end;
What about JsonToken?

We did not use a variable of type JsonToken directly, because when we are creating a JSON Object we already know what we have. Indirectly though, we definitely ARE using a Token. The ‘Add’ method of both the JsonObject and the JsonArray data types use a Token as the value parameter. Since the Token can be any simple type or any other Json type, you can simply throw any of those in and it knows what to do with it.

The JsonToken type will come in very useful when you start processing incoming JSON objects, and you need to make sure you correctly convert values into compatile data types in AL.

What’s Next?

These are just some very simple examples of how to build a JSON object, but this is really all the logic you’ll ever need to create your own. The JsonObject will take care of the curlies and the commas, the JsonArray will take care of the square brackets and all the other stuff. The only slightly complex thing you’ll ever need to do beyond this is to now take the JsonObject and set it as the body of an HttpRequest.

Let me know in the comments if this is helpful. There are other super useful posts about this topic, but I wanted to explain this in my own words. I will also write one how to read an incoming JSON object, and at some point I’ll share some helper logic that I’ve created to help take care of conversions and such.

Insider Builds on Collaborate

As a Microsoft partner, you have to create a bunch of accounts to get into a bunch of systems. Microsoft is on a continuing mission to make things easier by putting most of it into a single portal called “Partner Center”. In this post I will focus on one particular thing – how to get access to insider builds of BC, which you need to validate your extension against future releases.

I’m writing this because the engagement was not listed for one of my clients. Microsoft support was no help, and I could not find this information in some of the obvious places. Just a quick post, mostly for my own reference but I can’t imagine that I am the only one that is having difficulties getting into the program. Thanks to our little Twitterverse for helping me out.

Collaborate

First, you need access to Collaborate itself. From your Partner Center portal dashboard, you should see a rectangle with the word ‘Collaborate’, the shortlink is aka.ms/collaborate. If you do not have access yet, this is where you would have a link to where you can submit a request to be admitted.

Insider Builds

To get to the insider builds, you have to be enrolled in something called an engagement called ‘Ready! for Dynamics 365 Business Central’. As a Business Central partner, this engagement should be listed and all you do is hit the ‘Join’ button and you should get access within a couple of days.

Access to the insider builds is buried a little bit. Select the Ready! engagement and click the ‘Packages’ button. The one you need is the document called “Working with Business Central Insider Builds”. The thing that you need to get the actual build is the $sasToken. This value is needed to be able to download the artifacts for the insider build. It’s the password to validate that you are allowed to use those artifacts. By the way, don’t forget to take the time to read the material in there, it’s important information.

What if I don’t see the engagement

Normally, when you register as a development partner for Business Central, you are supposed to see the “Ready to go” program in the available engagements, and getting access is as easy as clicking the ‘Join’ button. This does not always happen for some reason. The screenshot below shows the available engagements for one of my clients, and as you can see the program did not show up, so we could not click the join button.

If for whatever reason you do not see the engagement, do not submit a support ticket from Partner Center. These tickets go to a central support group that does not know about program-specific issues, and you’ll spend weeks going back and forth. You’ll ask for access, and they will tell you to click the Join button. You’ll tell them there IS no join button to click and they’ll say click the button anyway. This is not a knock on them, they just don’t seem to know the details.

Access to the BC programs is handled by the product team themselves, and their email is Dyn365BEP@microsoft.com. This will get your issue directly with the BC team, and they will know exactly what to do. Just provide your publisher name, your MPN ID, and first name, last name and email for the person that needs access. Once I did this, the issue was solved within a few days.

Adding new users

Once you have access to the engagement, you should be able to provide access to your own people by first adding them to the Partner Center account, and then by enrolling them into the engagement.

THE Book on Automated Testing in BC

It has taken well over a year to write, and a good 8 months to review (and revise, and rewrite certain parts), and it has been delayed almost 3 months. I am SUPER proud to say though that the second edition of THE BOOK on automated testing in Business Central has been published!

As a reviewer of course I knew this was coming, and Luc finally shared the news

What if I Already Have the First Edition?

Of course you do! You are a BC professional, so therefore you’ve been adding automated testing to all of your projects right from the start, and you purchased Luc’s first book right when he wrote that. There are a few reasons why you should purchase the second edition

First of all, at 387 pages the second edition is almost twice the book as the first edition, which counts *only* 206 pages. Luc has added about a metric ton of stuff to the second edition. Not just expanded information on existing topics, but chapters about completely new topics altogether.

Major Improvements

One of the things that I thought was lacking in the first edition was more in-depth information about Test Driven Development (TDD for short). The mechanics of automated testing were solidly covered, and I was able to apply this knowledge in my work. What I was missing was how to take this to the next level. I knew there was a large methodological body of work out there about TDD, and I did not know where to start looking how that would be relevant for ME.

The second edition has filled that gap. Not only does Luc write eloquently about the methodology itself (there’s a whole chapter on TDD itself now), he puts it into the context of Business Central development specifically. He explains HOW you can use TDD in Business Central, he shows you step by step how to approach this, and he even provides a handy set of tools to support this methodology.

Luc spends a LOT of time writing about all aspects of TDD. More than just the nuts and bolts of creating test apps, he covers how to integrate automated tests in your daily development practice.

Advanced Topics

The brand new section called ‘Advanced Topics’ addresses some lesser known things such as refactoring your code to create more re-usable components, utilize standard components in more complex scenarios, the approach to testing web services and even how to make YOUR code more trestable.

In short, this second edition goes much more in-depth in just about every aspect of the book, plus it provides a wealth of information into a number of valuable topics that were not addressed in the first edition.

I am VERY proud to have played a part in writing this book, Luc did a phenomenal job in making the second edition a much more mature volume of THE book on automated testing in Business Central. Even if you already own the first edition, your money will not go to waste if you buy the second one.

Where can I get it?

Two places that I know of that you can get it:

  • The Packt Publishing website. Some people complain about delivery delays and such, but I don’t mind waiting a few days. When you get it from Packt directly, you have the option to have the book in print as well as e-book. The online reader on the Packt website is on of the best online readers I know. Plus with online access you can start reading right away, so to me well worth the handful of extra days for delivering the print book.
  • Amazon of course. Can’t beat delivery time, but Amazon does not bundle print + eBook and Packt does.