Business Central How-To : Effective Filtering

As a BC user, you probably already know that it’s possible to filter against list pages. However, there are a lot of tips & tricks that can be applied to filtering that can super-charge your effectiveness and efficiency when using this function.

Let’s take a look:

Sorting: Users can sort data to get a quick overview. For example, try sorting your customers by Customer No., Currency Code or Country Region Code – you can achieve this quickly by toggling between ascending and descending order using the column headings.

Searching: At the top of each list page, you’ll find a Search action. This is slightly different to true filtering, but it does work to reduce the data shown based on your input. Type text into the search box – once applied, the system will display only those that contain the data you’re interested in. The search is case insensitive and matches text placed anywhere in the field.

Filtering:  Applying a filter causes the page to show records as defined by the filter, which lowers the total number of records and facilitates finding a specific record. Do you view data as tiles rather than a list page? No problem – filtering works exactly the same way.

Filter Pane: Almost all lists, ledgers, and line-levels of transactions have a Filter Pane that can be revealed or hidden.

Operators and Filter Criteria: This is where you can really boost your effectiveness. Using filter expressions in filters can widen your scope of data. If you’ve used formulas in Excel before, some of these will be familiar for you.

Let’s dive into these and what they can do for us:

Equal To (=)

We can use the equal sign to filter records that match a specific value. For example:

=377 filters records with the number 377.

=BLUE filters records with the BLUE code (e.g., BLUE warehouse code).

Interval (…)

We can use this to specify a range using two dots (…). It’s a very flexible operator to use, and can be applied in various ways – for example:

1100..2100 includes numbers from 1100 through 2100.

..2500 covers all numbers up to and including 2500.

..12 31 00 includes dates up to and including December 31, 2000.

Either/Or (|)

Use the pipe symbol to filter records that match either of the specified values. For instance:

1200|1300 includes records with either 1200 or 1300.

Greater Than (>), Less Than (<)

Use this to filter records based on numerical comparisons. For example:

>1200 includes numbers greater than 1200.

<1200 includes numbers less than 1200.

Greater Than or Equal To (>=), Less Than or Equal To (<=)

This builds on the above, but here we add an extra criteria so that we can be sure to include our upper/lower limit. See examples as below:

>=1200 includes numbers greater than or equal to 1200.

<=1200 includes numbers less than or equal to 1200.

Wildcard (*)

We can use the asterisk to represent an indefinite number of unknown characters. This is helpful for data where we aren’t too sure on the spelling, or want to bring back a wider range of data. Take at look at the below for some examples:

Co* filters texts that begin with “Co”.

*Co filters texts that end with “Co”.

Hans?n matches texts like Hansen or Hanson.

Ignore Case (@)

We can use @ to make the search case-insensitive, like so:

@location matches texts like LOCATION, location, or Location.

Combining Expressions

Finally, the ability to combine different formats for more complex filters allows us to really get at the data range we’re looking for:

|8100..8490 includes records with 5999 or numbers from 8100 to 8490.

50&<100 filters numbers between 51 and 99.

If you’d like to discuss more about how effective filtering and Business Central can transform your business, reach out today to Brookland Solutions for a no obligation discussion.  Get in touch!