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.

https://www.kauffmann.nl/bc-apis-online-course/

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:

http://bcdev-default:7048/BC/api/v2.0/companies(684c3004-464e-ec11-bb7e-000d3a39265e)/

customers(461d8d2a-464e-ec11-bb7e-000d3a39265e)?$expand=contactsInformation

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

{

    "@odata.context": "http://bcdev-default:7048/BC/api/v2.0/$metadata#companies(684c3004-464e-ec11-bb7e-000d3a39265e)/customers/$entity",

    "@odata.etag": "W/\"JzQ0O3VXUnY4ZHNyb3dyMGVja2owMTh4QmJabEJqVmVndHdmSHV2ZE5PdDVMSTQ9MTswMDsn\"",

    "id": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

    "number": "10000",

    "displayName": "Adatum Corporation",

    "type": "Company",

    "addressLine1": "192 Market Square",

    "addressLine2": "",

    "city": "Atlanta",

    "state": "GA",

    "country": "US",

    "postalCode": "31772",

    "phoneNumber": "",

    "email": "robert.townes@contoso.com",

    "website": "",

    "salespersonCode": "JO",

    "balanceDue": 0,

    "creditLimit": 0,

    "taxLiable": true,

    "taxAreaId": "6701222f-464e-ec11-bb7e-000d3a39265e",

    "taxAreaDisplayName": "ATLANTA, GA",

    "taxRegistrationNumber": "",

    "currencyId": "00000000-0000-0000-0000-000000000000",

    "currencyCode": "USD",

    "paymentTermsId": "551c8d2a-464e-ec11-bb7e-000d3a39265e",

    "shipmentMethodId": "00000000-0000-0000-0000-000000000000",

    "paymentMethodId": "0e01222f-464e-ec11-bb7e-000d3a39265e",

    "blocked": "_x0020_",

    "lastModifiedDateTime": "2021-11-25T23:20:30.547Z",

    "contactsInformation": [

        {

            "@odata.etag": "W/\"JzQ0O0hYRTV0WnVQc0FIWUN5d0ZWY2QwOS94dVJvelBqVUdPTEVCUlZSbDNvQU09MTswMDsn\"",

            "contactId": "ee01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000001",

            "contactName": "Adatum Corporation",

            "contactType": "Company",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        },

        {

            "@odata.etag": "W/\"JzQ0O01JdGtxcUxla0d6MDJHWnl0VklOdnNlUy9uMjZGWVdDeFJ2VisySGxINTg9MTswMDsn\"",

            "contactId": "ef01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000002",

            "contactName": "Robert Townes",

            "contactType": "Person",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        }

    ]

}

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:

        "code": "BadRequest",

        "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:

        "code": "BadRequest",

        "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:

    "lastModifiedDateTime": "2021-11-25T23:20:30.547Z",

    "contactsInformation": [

        {

            "@odata.etag": "W/\"JzQ0O01JdGtxcUxla0d6MDJHWnl0VklOdnNlUy9uMjZGWVdDeFJ2VisySGxINTg9MTswMDsn\"",

            "contactId": "ef01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000002",

            "contactName": "Robert Townes",

            "contactType": "Person",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        }

    ]

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.

http://docs.oasis-open.org/odata/odata/v4.0/os/part2-url-conventions/odata-v4.0-os-part2-url-conventions.html#_Toc372793860

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

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:

    https://stefanmaron.com/?s=Coding4Performance

Here is the recording of our discussion:

https://www.youtube.com/watch?v=bcWSwIjZSG8

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

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".  

https://twitter.com/tobiasfenster/status/1442533006812631047?s=20

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:

https://twitter.com/steveendow/status/1445494238955720713

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

Creating a simple text file using AL in Business Central - Sample Code

 By Steve Endow

On January 23, 2021, I recorded a video showing how to create a simple text file using AL in Business Central.

Here is the video:

https://www.youtube.com/watch?v=_GUzlFKJFQk

Below is the sample code from that video.

Updating from NavContainerHelper to the new BcContainerHelper

 By Steve Endow

UPDATE: If you're interested in a convenient way to run the BcContainerHelper PowerShell commands, check out Krzysztof's Azure Data Studio Notebook with sample BcContainerHelper commands.  Jupyter Notebooks are a great way to include documentation and commands in a single convenient format.

On August 11, 2020, Freddy Kristiansen announced that he had released BcContainerHelper.

You can read his blog post here:

          https://freddysblog.com/2020/08/11/bccontainerhelper/

You will definitely want to read the entire blog post carefully, and perhaps read it a few times, as he shares quite a few important details about the new BcContainerHelper.

Same Great Container Helper, But Updated and Upgraded!

I made a video showing how I updated from NavContainerHelper to BcContainerHelper.  The process went flawlessly.

Here are the PowerShell Docker commands that I used to perform my update.

Learning Business Central Development from Scratch Video Series

By Steve Endow

If you are new to Dynamics 365 Business Central development, join me on a journey to learn how to develop Business Central Extensions using VS Code and the AL language.

I have been developing business software for many, many years, but I am completely new to Business Central, AL, and VS Code.  And I am also new to Docker and PowerShell.  

Basically, I'm having to learn everything from scratch.  

I'm writing blog posts and making videos to share the learning resources I'm using and share my learning journey.

Business Central Download Symbols Error: No such host is known

By Steve Endow

UPDATE:  After researching this type of Windows hosts file name resolution issue, it seems there are dozens of possible reasons why the problem can occur.  I suspect my case was one of the simpler versions, and fortunately flushdns worked for me.  Here is a forum thread with examples of several rather odd causes and solutions.

This morning I tried to create a new Business Central AL project in VS Code.  I used AL Go! to create the project, set the server name in the launch.json file, and then tried to Download Symbols.

Very simple, very easy, right?

Nope.

No soup for you!

What? I performed these exact same steps 2 days ago on this same machine, and I was able to download symbols without any issues.

Here is the full video of the troubleshooting process:

https://www.youtube.com/watch?v=zLb0tjwuThY

I closed VS Code, relaunched it, created a new project, and tried again.  Same error.

Looking at the error log in VS Code, I saw "Error: No such host is known".  Say what?

Automatically Update BcContainerHelper - Improved Script

By Steve Endow

UPDATE: I have updated the script to work with the newer BcContainerHelper rather than the older NavContainerHelper

UPDATE: If you're interested in a convenient way to run the BcContainerHelper PowerShell commands, check out Krzysztof's Azure Data Studio Notebook with sample BcContainerHelper commands.  Jupyter Notebooks are a great way to include documentation and commands in a single convenient format.

I previously wrote about creating a simple scheduled task to automatically update NavContainerHelper.

While that simple script does work, it didn't have any logging or notification. I never knew when NavContainerHelper / BcContainerHelper was updated.

I recently created a fancier PowerShell script to automatically build my Business Central Docker Containers every day, and while working on that script, I learned how to record elapsed time, log all activity, and send an email notification.

Based on that new learning, I wanted to upgrade the script that updates BcContainerHelper.

I wanted logging, elapsed time, and email notification if BcContainerHelper was updated to a new version.

Email notification when an update occurs

Here's what I came up with.  You'll need to adjust the file paths and email address, and generate your own email password file.

If you think of any other features the update could have, let me know!

 #v1.0 - March 27, 2021
#
$server = $env:COMPUTERNAME
#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"

#Define the module name
$moduleName = 'BcContainerHelper'

$body = ""
$message = ""
$updated = $false

#Specify file location for activity transcript log file
$transcriptFile = "D:\BCPowerShell\Logs\" + $fileDateTime + " " + $server + " " + $moduleName + " Update Log.txt"

#Start recording transcript
Start-Transcript -Path $transcriptFile

#Record the start time
$StartTime = $(get-date)


#Define email configuration
$emailFrom = "myemail@gmail.com"
$emailTo = "myemail@gmail.com"
$smtpServer = "smtp.gmail.com"
$port = "587"
#Email password file created using:  Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File -FilePath D:\BCPowerShell\Gmail_password.securestring
$passwordFile = "D:\BCPowerShell\Gmail_password.securestring"
$securePassword = ConvertTo-SecureString (Get-Content -Path $passwordFile)
$smtpCred = New-Object -TypeName PSCredential ($emailFrom, $securePassword)

$subject = $server + ": " + $simpleDate + " " + $moduleName + " Update Log";

$started = "Start time: " + $StartTime
$body += $started + "`n`n"

Try
{
    #Get currently installed version
    $currentVersion = (Get-InstalledModule $moduleName -ErrorAction SilentlyContinue).Version.ToString()
}
Catch 
{
    $currentVersion = "(not installed)"
}

#Get the latest version available
$latestVersion = (Find-Module $moduleName).Version.ToString()



#If we don't have the latest version installed
If ($currentVersion -ne $latestVersion)
{

    $message = "Current version: " + $currentVersion + "`nLatest version: " + $latestVersion;
    $message += "`n`nUpdating to version " + $latestVersion;
    #Remove existing version(s)
    Uninstall-Module $moduleName -Force -AllVersions -ErrorAction SilentlyContinue

    #Install latest version
    Install-Module $moduleName -Force

    $updated = $true;

    #Verify version number
    $currentVersion = (Get-InstalledModule $moduleName).Version.ToString()
    $message += "`nUpdate complete";
    $message += "`nCurrent version is now " + $currentVersion;
        
    $body += "`n" + $message;

    Write-Output $message
}
Else
{
    #If we are on the latest version, log that info
    $message = "You are on the latest version of " + $moduleName + ": " + $currentVersion;
    Write-Output $message
}



#Record end time
$EndTime = $(get-date)
$output = "`nEnd 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" 

#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
Stop-Transcript
        
if ($updated)
{
#Send the email
Send-MailMessage -From $emailFrom -To $emailTo `
    -Subject $subject `
    -Body $body `
    -Attachments $transcriptFile `
    -SmtpServer $smtpServer `
    -Credential $smtpCred `
    -Port $port `
    -UseSsl
}  

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

You can also find him on Twitter and YouTube

https://www.precipioservices.com

Fully automate the Business Central Container build process

By Steve Endow

I don't like maintenance tasks.  I don't like having to remember to do something on a regular basis.  I don't like having to do low value tasks constantly.

While it has been valuable for me to manually run NavContainerHelper the last several months, I think I am finally at a point where I sufficiently understand how it works and what it does.  I'm now ready to automate my Business Central Container build process so that I don't have to do it manually.

Just tell me how it went...

This isn't anything new--I'm just finally getting around to doing it for me with the features that I want.  If there are other ways or better ways to do this, let me know.

I share the background, my thinking, and my journey creating the script in this video.

In short, I've just wrapped the call to New-BcContainer in some additional PowerShell script to enable full logging, some basic error handling, and email notification.

Basic Business Central Docker Commands That I Use

By Steve Endow

On July 1, 2020, I made a video showing the basic Docker commands that I use to manage Business Central Docker Containers.

PowerShell isn't too bad

Here is a complete list of the commands that I've collected to manage Business Central Containers, as of July 2020.  If you have any others that you recommend, please comment below and let me know!


Thanks to Daniel and Tanya and Džoka for some additions!

   
 Open Powershell / Powershell ISE on HOST machine using Run As Administrator  
   
 
 NOTE: When referring to a Container ID or Image ID, you can use the first 
       few characters of the ID if they are unique--you do not have to always
       use the full ID.


 Check NavContainerHelper Version  
   
      Get-InstalledModule NavContainerHelper  
   
   
 Fresh Install of NavContainerHelper  
   
      Install-module navcontainerhelper  
      Import-Module navcontainerhelper  
   
   
 Install Latest NavContainerHelper  
   
 If you KNOW you don't have multiple versions installed:  
   
      Update-Module NavContainerHelper -Force  
   
 If you MIGHT have multiple versions installed:  
   
      $MyModuleName = <modulename>  
      Uninstall-Module $MyModuleName -allversions   
      Install-Module $MyModuleName
   
      NOTE: I do not recommend using the -Force option with Install-Module. 
            It can bypass valuable warnings that you want to know about.


 Alternate method to Remove Old Versions of NavContainerHelper  
   
      $ModuleName = 'navcontainerhelper';  
      $Latest = Get-InstalledModule $ModuleName;   
      Get-InstalledModule $ModuleName -AllVersions | ? {$_.Version -ne $Latest.Version} | Uninstall-Module -WhatIf  
   
   
   
 Container Management Commands  
   
   	Compact Container listing:
    
      docker ps --format "table {{.Names}}\t{{.ID}}\t{{.CreatedAt}}\t{{.Status}}"
      
    Full listing:
    
      docker ps  
      docker ps -a  
        
      docker start <id>  
      docker stop <id>  
   
      
      docker container inspect 


 Remove Container  
   
    Recommended method:

      Remove-BCContainer <containername>


    Manual Docker method:

      docker stop <id> 
      docker rm <id> 
      Manually clean up hosts file
      Manually remove desktop icons
      Manually remove extensions from C:\ProgramData\NavContainerHelper\Extensions\
   

 Stop all running containers:  
        
      docker stop $(docker ps -aq)  
   
   
 Images  
   
       docker images  
       docker images -a  
   
 Remove Image  
   
      docker rmi <id>  
        
      docker rmi <repository>:<tag>  
   
   Remove all images:

      docker rmi $(docker images -aq)

   
 Clean up old images  
   
   Remove orphaned images with "< none >" identifiers:

      docker images -q -f dangling=true | % { docker rmi $_ }  


   Remove orphaned images:
   
   	  docker image prune -f
      
   Remove orphaned AND UNUSED images:
   
   WARNING: Be careful with this: It will remove generic OS images used by NavContainerHelper!

      docker image prune -a -f
   

   
 Get Artifact Info for an existing BC Container:  
   
      Get-BcContainerArtifactUrl containername  
        
      Get-NavContainerArtifactUrl containername  
   
   
 Diagnostics  
   
      Get-BcContainerEventLog  
   
   
   
 Create New BC Container  
   
      New-BcContainerWizard  
   
   
 https://blog.baudson.de/blog/stop-and-remove-all-docker-containers-and-images  
   

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

You can also find him on Twitter and YouTube

http://www.precipioservices.com

Learning Dynamics 365 Business Central Development From Scratch: Introduction

By Steve Endow

If you've never done any NAV or Business Central development, but would like to, join me on this journey to learn how to develop in Dynamics 365 Business Central.

I've never used Dynamics NAV and don't even know what C/AL looks like.  I don't know what a "code unit" is.  Until a few months ago, I had never used Docker.  And although I've used Visual Studio for years, I don't really know how to use VS Code.  I know that Business Central uses a language called "AL", but I can't write a single line of AL.

Based on my experience so far trying to learn the Business Central application and Business Central AL development, I expect this will be a long journey for me.  Because I am still working mostly with Dynamics GP customers and projects in my day job, I have to learn Business Central in my "free time", so it isn't something I'll be able to pick up in just a few months.  A year ago, I gave myself 2 years to be "minimally competent" with the Business Central application and development.  Well, work gets busy and life gets busy, so I think I'm currently well behind on that 2 year timeline, and need to catch up.

Even though I have over 15 years of experience working with Dynamics GP and .NET and SQL Server, Business Central is a completely different world to me.  My fundamental knowledge of ERP software and software development is somewhat helpful as background, but it seems all of the specifics are completely different.  I want to learn Business Central and AL development and have the same comfort level that I have with Dynamics GP and .NET.  That will take time, and will require some pretty intensive learning.

So I'm starting from scratch.  Learning from scratch.  This is the start of that journey.