Wednesday, May 18, 2022

Business Central Custom Fields and Default Field Values

by Steve Endow

When you add new fields to an existing Business Central table, what happens?  

What does Business Central do in the database?  How does Business Central handle default field values?  

I didn't know the answers to these questions, so I decided to do a few tests.  For fun, obvs!

To keep things simple, I created a Table Extension object in AL.  I assume this applies to creating custom tables and adding fields to an existing custom table, but I figured a Table Extension would be simpler and easier to test with, and I learned a few more things about table extensions in the process.

Simple Customer Table Extension 

I installed the extension in my BC Docker Container, and then took a look at the modified Customer table.  I went to the Table Information page and searched for Customer. 

Table Information Page

I then clicked on "5" in the No. of Records column.  When I scrolled to the right, I found my custom fields.

Custom Fields from Table Extension

What may not be obvious at this point is that Business Central is choosing a value for the "Allow Nulls" setting on these new fields. This may sound obvious, simply because Table Extensions just work, but if you think about this further, it has some technical implications, and gives us some insight into what BC is doing behind the scenes when it adds new fields to the database.

Notice that the 5 existing customer records have some blank custom field values, while a few have a value.  So the TestCode, TestDate, and TestText fields are blank, but TestInteger, TestGuid, and TestBool fields effectively have a value of 0.


Look at TestDate again.  It's a Date field.  That is blank for existing records.  Blank, like an empty string.


So what's going on behind the scenes?  What are the actual properties of the fields that are being created by Business Central for this Table Extension?

Well, let's take a look.

I opened SQL Server Management Studio and connected to the SQL Server instance in my Docker Container.

I then expanded the "default" database, and expanded Tables.  I found two tables with "Customers" in the name, and it turned out the first one had the custom fields for my Table Extension.

Table Extension Fields in Separate Customer Table

Huh.  Look at that.  Instead of attempting to append my custom fields to the real Customer table, Business Central creates another table, effectively also called Customers, to store just the custom fields for my Customer Table Extension.

I can imagine lots of benefits to this design, but can only think a few potential downsides.

If I query the records in this table, I see a row for each existing Customer, and most of the fields have a default value.

Records in Custom Customer Extension Table

That's interesting.

Notice something about the "Columns" listed under the custom Customers table in the prior screen shot?  Notice the words "not null" after each field?

Let's think about what that means.  First, it tells us that Business Central apparently does not allow NULL fields.  This is similar to Dynamics GP, which I worked with for many years--no NULLs allowed.  But I also realized Business Central created a new table with fields that do not allow null values, but it appears to have added new records to that table.  So that tells me that Business Central has provided some default values for the field.

How did it do that?

One way that I'm aware of is to define a default value for the field through the Default Value or Binding field property.

Default Value or Binding is Blank

Business Central does not seem to have used that property--Default Value or Binding is blank for the TestDate field, but we saw that customer records had a value of 1753-01-01 in our TestDate field.

So how did it create records with default field values in fields that do not allow nulls and do not have a default value defined?

My guess is that it used a command like this that provides a default value during creation of the field.

Add Field with Default Value

That doesn't explain the full sequence of steps it must perform to create all of the records in the new custom table, but I am guessing it's one step in the process.  (a topic for a future blog post)

Interestingly, we see that the Date field has a default value of 1753-01-01 (the SQL Server minimum date value), but when we view the contents of the customer table, the TestDate field is blank.  That seems to indicate that Business Central is automatically replacing the minimum date with a blank value in the UI.

If I missed any details about table extensions, custom fields, or custom tables, let me know!

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube, or through these links:

Thursday, May 5, 2022

Mitigating WiFi deauth attack with Protected Management Frames in Unifi (aka 802.11w Management Frame Protection MFP)

by Steve Endow

Note:  In case the title didn't make it obvious, this post has nothing to do with Business Central.  I wanted to document my learning because I found very little reliable or current information on mitigating WiFi deauth attacks, as of May 2022.  If I got anything wrong or if you find a good resource covering this topic, please post a comment and let me know.

When I'm not trying to learn Business Central, I like to learn about computer security.  I'm not a computer security expert by any means, just someone who finds it interesting as a side hobby.

While learning about password cracking several months ago, I learned about Kali Linux, an amazing distribution that includes lots of different computer and network security tools pre-installed.

In order to learn how to use Kali Linux and the security tools it includes, I decided to try to learn how to crack my WiFi password.  It seemed like a fun exercise, and it was.  But it just happened to teach me something entirely unexpected.

While following the instructions in this "Hacking Wi-Fi" guide (which had some minor omissions--I recommend using this version instead), I was quite surprised when I read this step:

"...disconnect the clients connected to the target network..."

Wait, what?

Friday, April 22, 2022

Power Automate Business Central Journal Line Import Error

by Steve Endow

NOTE:  I'm encountering this error as of April 22, 2022.  If this ends up being a bug in the Power Automate BC connector that is fixed, I'll post an update.

A customer is interested in importing General Journals into Dynamics 365 Business Central using Power Automate.

Simple, right?  Just one Power Automate flow, right?  How hard could it be, right?

Well, it depends.

This is the error that I get in my Power Automate environment.

You must specify a journal batch ID or a journal ID to get a journal line

When I ask why it doesn't work, I get responses that seem to indicate that it should work.

"It works fine for me"

"You must be doing it wrong"

"Did you try doing it this way?"

But no matter what I try, the flow gives me the same error:

ErrorCode: Application_DialogException  You must specify a journal batch ID or a journal ID to get a journal line.  

Thursday, April 7, 2022

Resolving Business Central AL Warning AL0603 - Implicit conversion to a value of type Enum

 by Steve Endow

While copying a standard Business Central Web API page to a new project to create a customized version of the API page, I encountered this compiler warning:

AL0603 - Implicit Conversion to a value of type Enum

An implicit conversion is being performed from a value of type 'Integer' to a value of type 'Enum "Attachment Entity Buffer Document Type"'. This conversion can lead to unexpected runtime issues. This warning will become an error in a future release.AL AL0603
(field) "Document Type": Enum "Attachment Entity Buffer Document Type"

I am vaguely familiar with this error, and I think I resolved it months ago in a different Business Central project, but I completely forgot how I figured it out and how I resolved it.  So I'm writing this post to remind me when I forget again in 6 months.

Friday, March 25, 2022

Getting Access to The Top Secret Business Central Insider Program

by Steve Endow

If you are a Dynamics 365 Business Central developer or ISV, you should definitely get access to the Business Central Insider program.

Business Central Insider Program

I don't know why the BC Insider program is so cumbersome to join, but I'm guessing it involves lots of lawyers. Which is unfortunate, since I think BC Insider is an essential program for any BC developer.

I thought I had a link to the BC Insider program on my BC Resources page, but apparently not, as it seems to require several steps, and is not available through a single URL, unfortunately.

I did a few DuckDuckGo searches, but came up with nothing.  I then did several Google searches, but still couldn't find any Microsoft pages explaining how to join the BC Insider program.

After several searches, I finally found Daniel Rimmelzwaan's excellent blog post from February 9, 2022:

Thankfully, he documented the process for signing up for Business Central Insider after going through the same puzzling search for one of his clients.  

Friday, March 4, 2022

Business Central Web API Error When Using $expand

 By Steve Endow

I recently responded to a Business Central Community Forum post here:

While researching that question, I learned something from that forum post:  $expand=*

Huh, I didn't know that was possible!

If you aren't familiar with the $expand OData query option, check out my blog post on the topic:

So it seems that you can use a wildcard with $expand to automatically expand all Navigation Properties available in a Business Central Web API endpoint. Very interesting!

I personally don't think I would ever recommend using such a query option in a real world API integration, but I was intrigued by the possibility!

So I fired up Postman and did some testing with my new found query option.

Query Option $expand=*

Friday, January 14, 2022

Is Agile Development viable for Business Central customizations?

 by Steve Endow

To the Agile Advocates (tm) who will immediately protest "Of course it is!" before reading on, I ask:  

Hear me out.

Reference:  What is Agile?

Reference:  What is Agile Development?

Reference:  Manifesto for Agile Software Development

Reference:  Agile Estimating and Planning

Reference:  Scrum: The Art of Doing Twice the Work in Half the Time

Disclaimer:  I'm not an Agile expert.  I'm someone who has tried to learn about Agile (on several occasions) and tried to understand if and how some Agile practices might be used in the projects I work on.  I'm asking lots of questions, for which I'm having a difficult time finding answers on my own through part time self-study.

"Agile Development". It sounds cool. It sounds compelling. If you've ever suffered on a long, complex project that was over budget and seemed like it would never end, you'll likely appreciate some of the benefits that Agile claims to offer.  You'll read the Principles behind the Agile Manifesto and say, "Yes, please!"

It definitely appeals to me.

But then there is the reality that I personally work in.  Over the last 26 years, I have done consulting and development work mostly for "mid-market" customers in the US.  Based on my years of experience, admittedly with organizations and teams that didn't have "best practices", I'm having a difficult time trying to map Agile practices to the mid-market ERP projects that I typically encounter.

My inquiry is not about whether Agile is good or bad.  I'm trying to understand if Agile is a good fit, or even a viable option, for my customers and my projects.

Business Central Custom Fields and Default Field Values

by Steve Endow When you add new fields to an existing Business Central table, what happens?   What does Business Central do in the database?...