​Microsoft 365 Business Central: Filtering Data using AL

Jared Drueco | June 15, 2022

A table “record” is a complex data structure I’ve used often when scripting a report or an extension. Creating a record basically gives us an instance of a table within Business Central’s database; rows of the table are stored within this data type based on the current filter or range set on it.

Since AL is a client side and database driven language, understanding how to modify and access records is crucial to creating features that meet a client’s design requirements in Business Central. In this post I will show AL code utilizing the “Record” data type, as well as it’s effect on the table it is applied to.

This is the table we’ll be working with. As you can see the “Sales Header” table contains invoices for various customers. What if we only wanted to access data from one specific customer? We can use the SetRange() function which is a method that is already included with the record data type.

On the left of the above image is a code snippet that gets the filtered table we want, and on the right shows the effect the filter has. We can see that the SetRange() function takes multiple parameters which define the filter set on the table. Alternatively, we could also use the SetFilter() function, which can filter a table based on an expression rather than a range.

Filtering data allows use to perform CRUD (Create, Read, Update, Delete) operations on the specific records since Business Central as an ERP system is dominated by a CRUD design. You can refer to the AL documentation to find all the types of operations we can do on such data.

As an extra hint the lines after the SetRange() function is called provides an example of how one can iterate through the filter data to perform the operations on each record.