For decades, we have always retrieved full records from the database. Now we got an option to only query the fields we need. Check out this video:
Queries are a compelling object type within Business Central, as they allow us to retrieve records from one or more tables and then combine them in rows and columns into a single dataset.
This object type also allows you to perform calculations such as limiting the number of rows to retrieve, sorting data, and finding the sum or average of all the values in a specific column.
When working with table data, there will be times when we need to retrieve a unique record value from the table (also called SELECT DISTINCT). Unfortunately, D365 Business Central does not provide this logic out of the box. We will need to find a way to do this ourselves.
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”.