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.

Formatting Booleans

As you know, boolean fields are presented as a checkbox in BC. The issue with that is that checkboxes themselves have no formatting properties. There are a couple of different ways to change the appearance of boolean fields, read on if you want to know what they are.

One of the apps I work on has a custom journal, in which we use the StyleExpr to color fields based on a number of checks on the record. Over time, we’ve added this functionality to a bunch of fields. You have to scroll to the right to check all the fields, so we added a field called ‘Has Alerts’ to tell the user that something is up with this record. My client didn’t like the standard checkbox and asked if we can show the field as ‘Yes’ or ‘No’.

I asked Twitter for help and got a few responses:

Use the OnDrillDown Trigger

Some of the old quirks of NAV made their way into BC, and one of them is that the simple act of adding a trigger can modify the behavior of page controls.

Image
Using the OnDrillDown trigger

As you can see, the code in the trigger is absolutely meaningless. All we are doing is setting a dummy variable, and the mere presence of this trigger causes the boolean to be presented as a text. The StyleExpr property is tied to a variable that is set in the OnAfterGetCurrentRecord trigger of the page itself. I don’t like this solution because the trigger has no meaning. The field values become clickable, but because the code is bogus, the user will want to click something that does nothing. Of course there could be situations where you actually want to do something in the drilldown, in which case this is perfectly acceptable.

Format the Field

The winning suggestion was to include the Format command in the SourceExpr of the control. So elegant, and makes sense right. The page recognizes that the source is a text, and presents the Yes/No variation of the boolean.

Image
Formatting the SourceExpr

I like this because the field value is not clickable, just a text value. Just a quick tip, nothing earth shattering, but something that I know I’ll want to use some time in the future.

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.

PowerShell Duration

This is a really quick tip just for myself to save the script where I can easily get to it, this time a quick way to output the duration of a PowerShell script. When a script takes longer than expected, in my mind I am waiting HOURS for it to complete but it is probably just a minute or two. I’ve run into this before, where I spend WAY too much time trying to locate a good, easy way to output the duration of a script. Without further ado, here’s the PowerShell code:

$startTime = Get-Date

<insert your script here>

$myTimeSpan = New-TimeSpan -Start $startTime -End (Get-Date)

Write-Output ("Execution time was {0} minutes and {1} seconds." -f $myTimeSpan.Minutes, $myTimeSpan.Seconds)

The key elements here being

  • I was looking for a ‘Time’ function and didn’t realize that in PowerShell you actually need to use the ‘Date’ object to access the time. Get-Date returns a DateTime stamp
  • Coming from BC, I was looking for something called ‘duration’, so it took quite a bit of time to find out about New-TimeSpan. This creates a ‘TimeSpan’ object that has its own members for easy concatenation of a user-friendly message. I’m only using minutes and seconds here

Hopefully next time I need this I will remember to search my own blog 🙂

Reports to Excel

Just a quick post about the new reporting capability to use Excel for the layout. It’s been out for a while now (it came out in v20 or 2022 wave 1), but there was a tweet earlier this year to point out some FAQs and some samples to get you started on it. Personally I haven’t had the time to really get into it but I wanted to share this so it doesn’t get lost.

Kind of a Big Deal

I have a feeling that this new capability is being underrated, to me this is really kind of a big deal. We all know how difficult it is to use RDLC, and I’ve been able to keep report development at a minimum. Now that we have the Excel layout as a new target, I can’t help but feel like this is the main direction that reporting will go (that and PowerBI of course, but that’s a whole other story). I would not be surprised if Excel will be playing a much more important role now that we can use it as the target for report objects.

OK so here’s the tweet, it was posted back in March and I had bookmarked it at the time to look at later.

What I think is really cool about the way some people in the BC team interact with the community, is that they will keep track of what the community is sharing, and actually promote these posts in their own documentation. Granted, the GitHub repo is no official documentation, but they DO link to community blog posts in there.

Like I said I still haven’t taken the time to dig in, but I wanted to get this posted not to lose the link. This will surely be enough to get started and get your feet wet with Excel Layouts.

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.

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.

Import Media Files for SaaS

One of the standard ‘Problems’ when you’re in an AL workspace in VSCode is a warning that you are no longer allowed to use BLOB as a datatype for images. This has been at the bottom of my priorities list until I had a request to create a new image for a standard field. With this post I’ll show you how easy it is.

Media Field

The first element that you need is a field in the table. Instead of a BLOB field with subtype Bitmap, you now need a field of type ‘Media’. There is also a data type called ‘MediaSet’ but that’s not what we are going to use. Go to Docs to read about the difference between Media and MediaSets. The field is not editable directly because we will be importing the image through a function.

In addition to the field itself, you need a function to import an image file into the field. In the object below I have a simple table called ‘Book’ with a number, a title and a cover. We use the ImportCover function to do the import, and implement that as an internal procedure, so it can only be used internal to the app. You can of course set the scope as you see fit.

table 50100 BookDnStr
{
    Caption = 'Book';
    DataClassification = CustomerContent;

    fields
    {
        field(10; "No."; Code[20])
        {
            Caption = 'No.';
            DataClassification = CustomerContent;
        }
        field(20; "Title"; Text[100])
        {
            Caption = 'Title';
            DataClassification = CustomerContent;
        }
        field(30; Cover; Media)
        {
            Caption = 'Cover';
            Editable = false;
        }
    }

    keys
    {
        key(PK; "No.")
        {
            Clustered = true;
        }
    }

    internal procedure ImportCover()
    var
        CoverInStream: InStream;
        FileName: Text;
        ReplaceCoverQst: Label 'The existing Cover will be replaced. Do you want to continue?';
    begin
        Rec.TestField("No.");
        if Rec.Cover.HasValue then
            if not Confirm(ReplaceCoverQst, true) then exit;
        if UploadIntoStream('Import', '', 'All Files (*.*)|*.*', FileName, CoverInStream) then begin
            Rec.Cover.ImportStream(CoverInStream, FileName);
            Rec.Modify(true);
        end;
    end;
}

Factbox for the image

Similar to how Item images have been implemented, you can create a factbox to show the book cover and add that to the Book Card. Using a factbox also makes it easy to keep the related actions close to the control.

page 50100 BookCoverDnStr
{
    Caption = 'Book Cover';
    DeleteAllowed = false;
    InsertAllowed = false;
    LinksAllowed = false;
    PageType = CardPart;
    SourceTable = BookDnStr;

    layout
    {
        area(content)
        {
            field(Cover; Rec.Cover)
            {
                ApplicationArea = All;
                ShowCaption = false;
                ToolTip = 'Specifies the cover art for the current book';
            }
        }
    }
    actions
    {
        area(processing)
        {
            action(ImportCoverDnStr)
            {
                ApplicationArea = All;
                Caption = 'Import';
                Image = Import;
                ToolTip = 'Import a picture file for the Book''s cover art.';

                trigger OnAction()
                begin
                    Rec.ImportCover();
                end;
            }
            action(DeleteCoverDnStr)
            {
                ApplicationArea = All;
                Caption = 'Delete';
                Enabled = DeleteEnabled;
                Image = Delete;
                ToolTip = 'Delete the cover.';

                trigger OnAction()
                begin
                    if not Confirm(DeleteImageQst) then
                        exit;
                    Clear(Rec.Cover);
                    Rec.Modify(true);
                end;
            }
        }
    }
    trigger OnAfterGetCurrRecord()
    begin
        SetEditableOnPictureActions();
    end;

    var
        DeleteImageQst: Label 'Are you sure you want to delete the cover art?';
        DeleteEnabled: Boolean;

    local procedure SetEditableOnPictureActions()
    begin
        DeleteEnabled := Rec.Cover.HasValue;
    end;
}

Add to the Page

All that is left is to add the factbox to the page where you have the import action. In this case I have a very simple Card page for the book, and the factbox is show to the side.

page 50101 BookCardDnStr
{
    Caption = 'Book Card';
    PageType = Card;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = BookDnStr;

    layout
    {
        area(Content)
        {
            group(General)
            {
                field("No."; Rec."No.")
                {
                    ToolTip = 'Specifies the value of the No. field.';
                    ApplicationArea = All;
                }
                field(Title; Rec.Title)
                {
                    ToolTip = 'Specifies the value of the Title field.';
                    ApplicationArea = All;
                }
            }
        }
        area(FactBoxes)
        {
            part(BookCover; BookCoverDnStr)
            {
                ApplicationArea = All;
                SubPageLink = "No." = field("No.");
            }
        }
    }
    actions
    {
        area(Processing)
        {
            group(Book)
            {
                action(ImportCover)
                {
                    Caption = 'Import Cover Art';
                    ApplicationArea = All;
                    ToolTip = 'Executes the Import Cover action';
                    Image = Import;
                    Promoted = true;
                    PromotedCategory = Process;
                    PromotedOnly = true;

                    trigger OnAction()
                    begin
                        Rec.ImportCover();
                    end;
                }
            }
        }
    }
}

This was a fun one to figure out. Let me know in the comments if it was useful to you

Containers And Bacpacs

A while ago an ISV client of mine was working on getting their app into the Embed program. Part of this process was to upload a bacpac with certain characteristics. The characteristics themselves are not relevant for this post but as I was helping them, but I thought I’d write this quick post to share how you can extract your bacpac files from a container, and how to use those bacpac files to create another one.

The setup

I’m starting out with a standard BC container, which was created using BcContainerHelper, and it is called DenSterDev. Coincidentally, I am also using BcContainerHelper to extract the bacpacs and to create the new container. I am using the “C:\ProgramData\BcContainerHelper folder to store the bacpacs, because that folder is recognized both inside and outside of the container.

Extract Bacpac Files

The container is multi-tenant, so there are two databases that we care about: one is the app database, and the other is the tenant database. Both of those are necessary to create the new container. If you have any apps installed on top of the standard container, those will be included in the bacpac file for the app database, and the bacpac for the tenant database contains the data itself.

The benefit of using BcContainerHelper is that we have very handy Cmdlets to get all this stuff in and out of containers, and the bacpacs is no exception. The command is very easy:

Export-BcContainerDatabasesAsBacpac `
    -containerName 'DenSterDev' `
    -tenant default `
    -sqlCredential $Credential `
    -bacpacFolder C:\ProgramData\BcContainerHelper `
    -doNotCheckEntitlements

The tenant name is the default name of ‘default’ that is created in each standard BC container. The sqlCredential is a PSCredential object that was created during the container generation, using a username and a secure string password. As stated above, the bacpacFolder is a folder that can be accessed both in and out of the container. The entitlement flag is to bypass the check and prevent an error. When you execute this script, the bacpac files will show up in the bacpacFolder:

Create New Container from Bacpac

We are going to use these same bacpac files to create a new container. I’ll use the same container name:

New-BCContainer `
    -accept_eula `
    -containerName 'DenSterDev' `
    -artifactUrl '<ProperArtifactURL>' `
    -auth NavUserPassword `
    -assignPremiumPlan `
    -updateHosts `
    -accept_outdated `
    -Credential $Credential `
    -additionalParameters @('--env appbacpac=C:\ProgramData\BcContainerHelper\app.bacpac','--env tenantbacpac=C:\ProgramData\BcContainerHelper\default.bacpac')

Same as before, the -Credential parameter contains a PSCredential object. Note that the -additionalParameters spans across multiple lines here, but that should go on the same line in your PowerShell editor.

This command will download all the necessary artifacts and create the same container as the standard. The only difference will be that the app and tenant databases will be created from the bacpac files in your folder, instead of the standard database from the artifact. You can follow along with the script in the terminal window.

Nothing earth shattering, and made super easy by BcContainerHelper, but it took me a while to find the information and make this work. Hat’s off to Dmitry in the BC team, he was very patient with me as I got familiar with this process. Let me know in the comments if this was helpful or if you want to add anything.