Friday, November 26, 2021

Business Central API - Filter Child Array Values Using $filter Query Option

 by Steve Endow

I had a difficult time finding an example of this type of API filtering using the OData $filter query option, so I wanted to share an example that I needed for a project.

For context, this is a very simple example of a Business Central API "child array" that I wanted to filter.  Technically, I think that OData calls this an "expanded navigation property".

Customers with Expanded contactsInformation Navigation Property

First, if you are not familiar with the OData $metadata URL and the $expand query parameter, please see my blog post here.

Second, I'd like to thank AJ Kauffman for helping me understand how to use this type of filter.  If you want to learn more about how to work with Business Central APIs, I highly recommend AJ's class.

One slide that I constantly refer to from AJ's API class is a list of 7 common OData query parameters.

Know the 7 Basic OData Query Parameters

The OData v4 specification refers to these as "Query Options".

So let's explore a specific use case for the $filter query option.  This sample JSON shows a customer record and the expanded contactsInformation navigation property.

Here is the URL:


And here is the response, showing multiple contacts for the customer:

    "displayName""Adatum Corporation",
    "addressLine1""192 Market Square",
    "taxAreaDisplayName""ATLANTA, GA",
    "contactsInformation": [
            "contactName""Adatum Corporation",
            "contactName""Robert Townes",

So now that I have the contacts for the customer, what if I want to filter those contacts?  Perhaps I only want to see records with contactType = "Person".  How do you use $filter to achieve that?  

If you try this:

    /customers({{customerid}})?$expand=contactsInformation&$filter=contactType eq 'Person'

You will get this error:

        "message""Could not find a property named 'contactType' on type 'Microsoft.NAV.customer'."  

Because the $filter is applied on the "parent" customer record, there is no contactType field available.

So what if we try this:

    /customers({{customerid}})?$expand=contactsInformation&$filter=contactsInformation/contactType eq 'Person'

Well, that won't work either.  You'll get this error:

        "message""The parent value for a property access of a property 'contactType' is not a single value. Property access can only be applied to a single value."

So, how do we filter the records in a "child" array?  This is the way:

    /customers({{customerid}})?$expand=contactsInformation($filter=contactType eq 'Person')

That gives you this response, with only the Person contact type in the contactsInformation array:

    "contactsInformation": [
            "contactName""Robert Townes",

After AJ showed me how to do this, I did find an example in the OData documentation, but you really have to know what you are looking for to see it.

Like finding a tiny footnote in a giant book

I hope that made sense and helps someone else out there looking to use this type of array filtering!

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

You can also find him on Twitter and YouTube

Monday, November 15, 2021

Troubleshooting BcContainerHelper Docker Build Errors on Windows 10 versions 21H1 and 21H2

by Steve Endow

If you've been using Docker Desktop on Windows 10 to build Business Central Docker containers with BcContainerHelper, you have likely encountered build errors for some reason.

Freddy Kristiansen has been constantly working to improve BcContainerHelper to deal with weird Windows version issues and Docker Desktop quirks, but it seems that Windows 10 versions 21H1 and 21H2 are currently causing some build errors, despite Freddy's best efforts.

If you are unsure which Windows 10 "version" you have, you can click on the Windows Start button and type "winver".  

Windows 10 - Version 21H2

I've reviewed a few issues on the BcContainer GitHub repository while helping a few people troubleshoot BC container build issues recently, and it appears there are currently 3 techniques to try and resolve errors when using Docker Desktop and BcContainerHelper on Windows 10 versions 21H1 and 21H2.

As of November 2021, if you are using Windows 10 version 21H1 or 21H2, you should see this warning from BcContainerHelper during the build process.

Windows 10 21H1 / 21H2 Warning Message

If you do see this message during your build, do not be surprised if the Docker Container build process fails.

Here's an example of one type of error that may occur:  "Docker Build failed with exit code -1"

Docker Build failed with exit code -1

Here are the 4 workarounds that seem to help resolve errors on the newer versions of Windows 10.

1. Temporarily disable Windows Defender Real-time Protection (under "Windows Security").  It seems that Windows Defender real-time virus scanning is interfering with the Docker Container build process, and either causing some type of file lock, or a quarantine, that is interfering with the build process.  Disabling the Real-time protection stops the file scanning, which may resolve your build error.

After the container is successfully created, you can re-enable the Real-time protection.  (but you'll probably need to turn it back off again temporarily the next time you create a new container)

Windows Defender Real-time protection setting

2. Completely disable Windows Defender temporarily (under "Windows Security").  A developer shared with me that he had previously been able to disable Defender Real-time Protection to build BC Docker Containers, but recently, that stopped working.  When he completely disabled 

3. Remove the imageName parameter from the BcContainerHelper script.  I saw this mentioned in a GitHub Issue, and it seems to have worked for Matthew Perren.

I don't know if the image creation process is triggering an error directly, or if skipping the named image process avoids a potential issue with Defender that is causing the problem.  But if disabling Defender Real-time protection alone doesn't work, try removing imageName.

4. Try adding the "-isolation hyperv" parameter to the BcContainerHelper script.  This used to be the go-to solution for a version mismatch between the host OS and the container OS, but it seems that with 21H1 and 21H2 the isolation mode does not always resolve build errors.

If you do encounter container build issues with Windows 10 version 21H1 or 21H2, please let me know if one of the steps above worked for you, or if you found a different solution to your build error.

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

You can also find him on Twitter and YouTube

Monday, November 8, 2021

Business Central Chat with Stefan Maroń: Designing Complex Pages in BC

 by Steve Endow

Stefan Maroń was kind enough to have an impromptu live stream with me today to help me understand how to design complex pages in Business Central.  We discussed how I might develop replicate the functionality of the .NET interface of my "Data Generator" application in a Business Central page.

In addition to designing the UI of a data generator, Stefan shares some very valuable information about how to process background tasks in Business Central, which I was unfamiliar with.

Here is a link to the "Coding4Performance" series on his blog:

Here is the recording of our discussion:

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.

You can also find him on Twitter and YouTube

Sunday, October 31, 2021

Learning Business Central Development - Season 2 - Episode 1

 by Steve Endow

I have needed good test data in Business Central for many months, but I just haven't gotten around to finding a good import tool or writing code.  There are several options, so I kept weighing the pros and cons of each, and sure enough, never chose one and still don't have a tool for creating reliable test data in Business Central.

In addition to not setting up a tool for test data, I also have not had the time to write any AL code.  I've been busy with other things while our developer focuses on writing AL code, so the little bit of AL knowledge I had has been fading for months.

That is finally about to change.  It's time for me to continue my AL code learning and create a "data generator" tool, so I'm going to combine both of those tasks into one project.

Introducing "Learning BC Development: Season 2"!  (following the Learning BC Development From Scratch video series)

In this "season", I'm going to learn how to develop a test data generator for Business Central.  I'm going to use the AL language to create custom windows and tables and codeunits as part of a custom Business Central application.

I don't really know what I'm doing or how I'm going to develop the application, but I'm going to give it a try and learn along the way.

Videos will be live streamed on Twitter, YouTube, and Twitch, but will be saved as recordings on YouTube.  

If you have any questions or comments, you can post a comment on this blog post, or a comment on the YouTube video.  Or you can send me a Tweet or DM on Twitter.

Here's the video:

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.

You can also find him on Twitter and YouTube

Tuesday, October 5, 2021

Docker Engine Enterprise and Mirantis Container Runtime for Business Central Developers

by Steve Endow

NOTE:  This post relates to Docker Engine Enterprise and Mirantis Container Runtime on Windows Server.  I don't believe this relates to or affects Docker Desktop.  But let me know if I missed anything regarding Docker Desktop, and let me know if I have any details wrong regarding Mirantis Container Runtime and its use with BcContainerHelper and Business Central Docker containers.

On September 27, 2021, Tobias Fenster shared a Microsoft announcement about the future of Docker Engine Enterprise, sometimes referred to as "Docker EE".

This Microsoft announcement comes almost 2 years after Docker was acquired by a company named Mirantis, in November 2019.

The Microsoft announcement indicates that technical support for the Mirantis Container Runtime will be transitioned to Mirantis.

Mirantis also published a post explaining the transition, and discussing options for receiving support for Mirantis Container Runtime (aka "MCR"), and free licenses for up to 9 "nodes" for Windows Server users.

So what does this mean for Business Central developers who use Windows Server?

When I first read the Mirantis blog post, I assumed I would need to contact Mirantis to take advantage of their "special offer" to receive a free license for MCR for my Business Central development on Windows Server.

This page on the Mirantis web site explains the "beta pricing" offer is available until December 31, 2021.

I thought that I would request licenses for a few nodes, which would cost me $0.  But, that would NOT include any support.  

I'm okay with the $0 price tag, and if I did need support for some reason, I would be open to paying a reasonable price for support.  I would even be open to paying $50 per node to receive limited support, but based on the information on the web page shown above, it is not included by default with the 1-9 or 10-50 note packages by default.  But it may be possible to purchase support separately for 50 nodes or less.

I'm assuming that after December 31, 2021, Mirantis will come up with new pricing, and MCR will not be free for Windows users.

What about "Moby"?

But today I re-read the Mirantis blog post and noticed this.

Wait, what?

I'm a Business Central "developer".  I am not using my containers in "production".

Does that mean that I can use the "default Moby engine"?  Whatever that means?

I posted my initial thoughts about this to the hive mind called Twitter:

I tagged our resident Business Central Docker expert, Tobias Fenster to see if I was missing anything.

I'm glad I tagged him, because he shared some important information about how Docker works and additional considerations about the Mirantis announcement that I believe are pretty important to Business Central developers.

Tobias replied, and mentioned that the free Moby engine has "No client component".

I didn't know what this meant, so he generously explained further.

So while we could run BC containers using Moby, technically we would not have a docker.exe "client" to manage or create the containers on Windows Server.

It seems we will need MCR

You can read the rest of the Twitter thread if you would like, but after a few more questions from me, and answers from Tobias, these are the points I think are important for BC developers who use Docker Engine Enterprise on Windows Server.

1. If you do NOT get a license for the Mirantis Container Runtime (MCR), and choose to use the "Moby engine", that is ONLY the "server" (daemon) portion of the solution.  Moby will allow you to host containers, but technically, that does not include the docker.exe "client" or any tools or utilities to help you manage your containers.  You could try to find other Docker tools, but Tobias suspects that limited interest in Docker on Windows means limited options for Docker tools on Windows.

2. As a Business Central developer, I regularly use the docker.exe "client" command in PowerShell to manage containers and images.  So I believe I will still need that, and therefore will need MCR.

3. The BcContainerHelper PowerShell module that BC developers use to create Business Central containers relies on the docker.exe "client" tool to create and maintain and interact with containers, so I assume that the full MCR solution will be required in order to continue using BcContainerHelper.  (there may be other ways to download a docker.exe client or equivalent, but I have no yet researched that)

Given this, I will want to request the MCR licenses from Mirantis so that I can have access to the full MCR product suite on my Windows Servers and continue to reliably use BcContainerHelper.

If you are aware of other solutions that provide a Docker client and related tools on Windows Server and work well with BcContainerHelper, please let me know.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.

You can also find him on Twitter and YouTube

Tuesday, September 28, 2021

Using $metadata and $expand with Business Central Web APIs

By Steve Endow

In June 2021, I discussed my circuitous journey to learn about and understand the $metadata and $expand OData "query options" and how to use them with the Business Central web API.

BC Web API OData Entity Model

Here is that video:

Today I had to research the $expand option for a BC API, but completely forgot how to use $metadata and find the related record "multiplicities" or "NavigationProperties" available for BC records exposed by APIs.

Since I had forgotten everything I discussed in my video from June, I figured it was time to write down a few notes in a blog post so that I can come back to this post in the future to refresh my memory.

The $metadata Option

When working with the Business Central web API, you can add $metadata to the end of the base API URL to get information about all of the available endpoints as well as related data elements that can be accessed using the $expand option.

For example, when working with my BC Docker Container named "dev18", I would use this URL:


When working with a BC SaaS environment:  (substitute your tenant and environment)$metadata

You can read all the gory details in the OData documentation here:

If you access the above Business Central API URL with the $metadata option, the request should return a massive pile of XML that looks something like this:

BC Web API OData Entity Model

Okay, so what are you supposed to do with those 4,000 lines of XML?

First, find the BC API object that you are working with.  For example, let's suppose we want to know which additional data elements can be expanded on the Customer object.

If you search in this XML for "customer", you'll find lots of entries.  Look for the line that says EntityType Name="customer".

This shows the customer object and the properties (fields) that are returned by the API.

If you scroll down past the Properties, you will start to see "NavigationProperty" items.

These are the "child records" or "related records" that you can include with the Customer records using the $expand option.

The $expand Option

Now that we know the NavigationProperty objects that can be retrieved for a Customer, we can use the $expand option in our API request.

You can read all the gory OData details about $expand here:

For example, if I want to retrieve a specific customer, and I want to also retrieve the Contacts associated with the customer, my request would look like this:

Sample Docker container URL:


Sample BC SaaS URL:$expand=contactsInformation

Using this $expand option includes contactsInformation in the customer data:

If I repeat this process for BC items, I go back to my $metadata XML and search for EntityType Name="item".  I can then see the NavigationProperty values that are available for BC items.

In the case of items, there are only 5 navigation properties available for use with $expand.  Which is a bummer, because my customer would like to retrieve item quantities by location from the BC API.  Based on this metadata, it looks like quantities by location are not available, and it will likely require a custom API.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.

You can also find him on Twitter and YouTube

Thursday, June 17, 2021

Automatically Create BC Docker Container - PowerShell Script version 3

 By Steve Endow

I created this PowerShell script to automatically re-create my Business Central Docker Containers every morning.

Version 3 of the script records BC Image version information to a log file and to the notification email.

(Thanks to Kishor Mistry for the improved method to get the BC image version!)

Email notification with full log

 #v3.0 - June 17, 2021  
 #Define the host, container, and image names  
 $server = $env:COMPUTERNAME  
 $containerName = "dev18"  
 $imageName = "i" + $containerName  
 $dnsIP = ""  
 #Define date values  
 $simpleDate = Get-Date -Format "M/d/yy"  
 $dateTime = Get-Date -Format "M/d/yy HH:mm"  
 $fileDateTime = Get-Date -Format "yyyy-MM-dd HHmm"  
 $currentDate = Get-Date -Format "yyyy-MM-dd"  
 $currentTime = Get-Date -Format "hh:mm:ss"  
 #Specify file location for activity transcript log file  
 $transcriptFile = "D:\BCPowerShell\Logs\" + $fileDateTime + " " + $containerName + " Container Build Log.txt"  
 #File location for BC version tracking  
 $versionFile = "D:\BCPowerShell\Logs\" + $containerName + " BC Version Log.txt"  
 #Start recording transcript  
 Start-Transcript -Path $transcriptFile  
 #Record the start time  
 $StartTime = $(get-date)  
 #Define email configuration  
 $emailFrom = ""  
 $emailTo = ""  
 $smtpServer = ""  
 $port = "587"  
 #Email password file created using: Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File -FilePath D:\BCPowerShell\Gmail.securestring  
 $passwordFile = "D:\BCPowerShell\Gmail_sendow.securestring"  
 $securePassword = ConvertTo-SecureString (Get-Content -Path $passwordFile)  
 $smtpCred = New-Object -TypeName PSCredential ($emailFrom, $securePassword)  
 $subject = $server + ": " + $simpleDate + " BC Build Log for Container: " + $containerName  
 $started = "Start time: " + $StartTime  
 $body = $started + "`n"  
 $errFlag = $false  
 $warnFlag = $false  
 #Output startup info  
 Write-Output $subject  
 Write-Output $started  
 Try {  
   #Get image version from current container  
   try {   
     $currentImageNum = (Get-BcContainerAppInfo -containerName $ContainerName | Where-Object -Property Publisher -EQ "Microsoft" | Where-Object -Property Name -EQ "Application").Version.ToString()   
     $currentImageNum = 'No Container'  
   Remove-Module BcContainerHelper -ErrorAction SilentlyContinue  
   Import-Module BcContainerHelper -ErrorAction SilentlyContinue  
   #Remove old artifacts and images  
   Flush-ContainerHelperCache -cache bcartifacts -keepDays 7  
   #Remove existing container if it exists  
   Remove-BCContainer $containerName  
   #Remove old images without deleting base OS image  
   docker images --format "{{.Repository}}\t{{.Tag}}\t{{.ID}}" |    
    Select-String "businesscentral" -notMatch |   
    ConvertFrom-CSV -Delimiter "`t" -Header ("Repository","Tag","ID") |   
    Sort-Object Tag | % ID | % { docker rmi $_ }   
   #Call New-BcContainer - Use New-BCContainerWizard to generate the script/params for your environment  
   $password = 'P@ssw0rd'  
   $securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force  
   $credential = New-Object pscredential 'admin', $securePassword  
   $auth = 'UserPassword'  
   $artifactUrl = Get-BcArtifactUrl -country us -select Latest -type Sandbox  
   New-BcContainer `  
     -accept_eula `  
     -containerName $containerName `  
     -credential $credential `  
     -auth $auth `  
     -artifactUrl $artifactUrl `  
     -dns $dnsIP `  
     -updateHosts `  
     -licenseFile "D:\BCPowerShell\BCLicense\BCv18.flf" `  
     -assignPremiumPlan `  
     -includeTestToolkit `  
     -includeAL `  
     -shortcuts None `  
     -imageName $imageName  
   Publish-BcContainerApp -appFile "D:\zDisks\BCCL\BCCL_1.15.0.125\installer\Extension\Hougaard.com_Business Central Command" -containerName dev18 -install -packageType Extension -sync -syncMode Add  
   Catch {  
     #If the script fails or has an error, output to error stream  
     Write-Error "Um, something didn't go well:"  
     Write-Error $_  
     #Capture error in email body   
     $body += "`nERROR:`n`n" + $_ + "`n`n"  
     $errFlag = $true  
   Finally {  
     #Get image version info from new container  
     try {   
       $newImageNum = (Get-BcContainerAppInfo -containerName $ContainerName | Where-Object -Property Publisher -EQ "Microsoft" | Where-Object -Property Name -EQ "Application").Version.ToString()   
       $newImageNum = 'No Container'  
     #Create line to write to BC Version log file  
     $versionEntry = $currentDate.ToString() + "`t" + $currentTime.ToString() + "`t" + $currentImageNum + "`t" + $newImageNum  
     #Write entry to BC Version log file  
     Add-Content -Path $versionFile -Value $versionEntry  
     #Add BC Versions to email body  
     $body += "`n" + "Prior BC version: " + $currentImageNum  
     $body += "`n" + "New BC version: " + $newImageNum + "`n"  
     #PROTOTYPE - This is fairly fragile and needs improvement  
     #Search for the Container OS Build number and compare it to the Host OS build number  
     [string]$containerOSVersion = Select-String -Pattern "Container OS Version: " -SimpleMatch -Path $transcriptFile  
     $trimBefore = $containerOSVersion.IndexOf("Version: ")  
     #This is pretty crude parsing, so there is presumably a more elegant method  
     $containerOSBuild = $containerOSVersion.Substring($trimBefore+9, ($containerOSVersion.Length-($trimBefore+9))).Split(" (")[0].Split(".")[3]  
     $osVersion = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion" -Name UBR).UBR  
     If ($containerOSBuild -ne $osVersion)  
       $warnFlag = $true  
       $buildWarning = "`nWARNING: Container OS Build " + $containerOSBuild + " does NOT match Host OS Build " + $osVersion  
       Write-Error $buildWarning  
       $body += "`n" + $buildWarning + "`n"  
       $output = "Container OS Build " + $containerOSBuild + " matches Host OS Build " + $osVersion  
       Write-Output $output  
       $body += "`n" + $output + "`n"  
     ipconfig /flushdns;  
     #Record end time  
     $EndTime = $(get-date)  
     $output = "End time: " + $EndTime  
     $body += "`n" + $output   
     Write-Output $output   
     #Calculate elapsed time  
     $elapsedTime = $EndTime - $StartTime  
     $totalTime = "{0:HH:mm:ss}" -f ([datetime]$elapsedTime.Ticks)  
     $output = "Elapsed time: " + $totalTime  
     $body += "`n" + $output   
     Write-Output $output  
     $body += "`n`nFull log file is attached"   
     If ($errFlag)  
       $subject = "ERROR: " + $subject  
       $greeting = "Greetings human.`n`nIt appears there was an error building your Business Central Docker Container: " + $containerName + "`n`n"  
     elseif ($warnFlag)  
       $subject = "WARNING: " + $subject  
       $greeting = "Greetings human.`n`nI prepared your Business Central Docker Container: " + $containerName + ", but detected potential issues.`n`n"  
       $greeting = "Greetings human.`n`nI have prepared your Business Central Docker Container: " + $containerName + "`n`n"  
     $body = $greeting + $body  
     #Finish the transcript recording  
     #The Transcript must be stopped before trying to send the email,  
     #otherwise the log file will be locked, causing the email to fail  
     #Send the email  
     Send-MailMessage -From $emailFrom -To $emailTo `  
       -Subject $subject `  
       -Body $body `  
       -Attachments $transcriptFile `  
       -SmtpServer $smtpServer `  
       -Credential $smtpCred `  
       -Port $port `  

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.

You can also find him on Twitter and YouTube

Business Central API - Filter Child Array Values Using $filter Query Option

 by Steve Endow I had a difficult time finding an example of this type of API filtering using the OData $filter query option, so I wanted to...