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.

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

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:

    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

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