Business Central’s filters are extremely powerful. Where you are using them will affect what tools are available. We will start by illustrating how to find the filter pane and save views. We’ll then demonstrate filters in typical list views before eventually running through date filters. Due to the nature of filters, this blog will be more pictorial. I’ve attached images of filters I’ve used and descriptions to explain their functions. This hopefully will give you some ideas as to where you can employ them in your Business Central environment.
The Business Central filter pane
To open up Business Central’s filter pane, on a list page, click the funnel symbol on the right of the page. This is located next to the bookmark and the FactBox icon. When you click this, a ‘Views’ section should appear on the left side of the page.
Before I demonstrate examples, it’s important to note that you can save and rename filtered views using the floppy disk icon. So after filtering results in a particular way, you click that icon and name the view. This is especially powerful when it comes to date range filters which we will cover further on in this post. Any saved filter views will be shown here:
Business Central filter examples
As you can see, by clicking the ‘filter’ action in ‘Filter list by’, you can select a field you wish to filter by. On top of this, you can ‘Filter totals by’ part to narrow down your results. Users can apply multiple filters at any given time. Let me show various examples on the Customers page.
Incorporating the asterisk symbol into Business Central filters
When it comes to filtering, the asterisk allows users to filter specific parts of a field’s value. For example, anything that begins with the letter ‘A’. See below:
In this case, I am filtering by the Name field, and using the ‘*’ key to say I am looking for any value that begins with A, regardless of what characters follow. Contrarily, you could use ‘<>A*’ to only show records which don’t start with ‘A’.
Two asterisks and the ‘@’ symbol
Using two asterisks allows you to filter results that include a certain letter or sequence of characters within the field you are filtering. The difference between this filter and the previous one is that the value you are filtering for here doesn’t have to be at the start or the end of the field value. The ‘@’ makes the filter I am applying not case-sensitive.
The use of the ‘@’ character tells the search that the value isn’t case-sensitive. Using the asterisk before and after ‘fine art’ means the results must include that value at some point in the Name field.
If I did an asterisk just before my value which was ‘School’, it wouldn’t return any results as the singular asterisk before means our value has to end with ‘school’, whereas ours begins with it. If I did ‘school’ followed by an asterisk, it would return a result as the asterisk after the value means the Name field value begins with ‘school’. In my case, I would either have to be case-sensitive with my Business Central filter value (‘School’, not ‘school’) or use the ‘@’ symbol before ‘school’.
Filter out results that include a specific value
In Business Central, users can filter out results that include specific characters. To do so, use ‘less than’ and ‘greater than’ symbols in conjunction with the value you want to filter out. In the example below, I am filtering out records with a Location Code value that include the letter ‘M’. See below:
Another point to make is that I don’t have to input the entire value. As you can see, I am filtering by a single letter. I could change the filter to <>*MAIN* if I only want to filter out the records with MAIN in the Location Code, not just M. If I use <>*E*, it would filter out all the records with an ‘E’, including ‘Tiverton’ and ‘East’. Like I showed earlier, I could use a singular asterisk instead. By doing ‘<>E*’, I would only filter out results beginning with an ‘E’, meaning ‘Tiverton’ would show but ‘East’ wouldn’t. Essentially, I can make this as broad or narrow as I choose.
Filter out results that don’t include a value
This title may not be totally clear at first glance, but what this will allow you to do is filter results by anything that doesn’t have a blank value. This formula is similar to the previous one. One difference is that where I am filtering something out that’s blank, I use two apostrophes instead of an asterisk. It’s one of the most popular filters so it’s important to understand. The filter formula is: <>”. The value includes everything between the two apostrophes. In my case, there’s no space or value between them, meaning everything that isn’t blank should appear.
In the example below, I am filtering out any customers which don’t have a Location Code value set against them:
Pipe symbol filter
The pipe character acts as an ‘or’ function. See below:
In this case, I am searching for records with a No. consisting of one of those three values.
Create a range between values using dots
The ‘..’ reflects a range between two values. See below:
In this scenario, I am saying ‘bring me results ranging from numbers between, and including, 10000 and 50000’.
Just to show you, you can use these filters in conjunction with one another. Let’s take a look:
This includes all the values from the previous filter as well as all those which begin with ‘C’, so your results don’t have to match both filters’ criteria. So, this is displaying all records which No. value is between 10000 and 50000 or any records which have a No. value starting with ‘C’.
Question mark symbol
The question mark symbol in this case acts as an unknown character. See below:
This filter brings all the results which start with ‘C00’ and end in ‘0’ but can have any two characters in between them.
‘Less than’ and ‘greater than’ symbols
Users can employ ‘<‘ and ‘>’ symbols in Business central, as well as ampersands. See below:
This is essentially looking for records with a No. field value bigger than 30000 and less than 60000. These are the two conditions. The ampersand means both conditions have to be met, unlike with the pipe symbol, where only one must be satisfied.
‘Equal to or greater than’ filter
A similar filter you could employ would be ‘>=’ which is ‘equal to or greater than’. Additionally, you could filter results the other way around by doing ‘equal to or less than’. This would look like ‘<=’. Below is an example of ‘equal to or less than’ filter on the Sales Orders list page:
As you can see, only order numbers below 101330 show.
Filtering results by favourites established on the Role Center
Another character you can use is ‘%’. This filters by what’s deemed as your ‘favourite’ for a record type you choose. In my case, on the Sales Order Processor Role Centre, I have ‘My Items’ located towards the bottom of the page. To try this, you may need to personalise your Role Centre.
This filters by the items I have in the ‘My Items’ area of my Role Centre.
Date range filters in Business Central
If we are looking for records which have particular associated dates, we can use a different set of filters to narrow down the results. Below I will show a series of examples and illustrate how different character combinations can provide useful filtered views for your records. Just for reference, I am showing these examples between the 13th and 20th April 2022, so you can see the filters working as intended.
The combinations below may seem a little random, I am simply trying to demonstrate what’s possible with Business Central filters.
The filter here essentially says ‘provide a range of records which have a document date between the 11th Friday of 2022 and today. T is today.
The image here shows the same as the image above, but the presentation of the filter changes to this when I tab off.
Today, current day and work day filters
In Business Central, users can incorporate letters to act as formulaic characters when it comes to filtering. Some of these include: ‘T’, ‘CD’ and ‘WD’. CD is current day. T and CD are actually the same. However, WD (work date) can be different. Technically speaking, the work date should default to today’s date. However, whilst you can’t change today’s date, you can change the work date in My Settings. So CD is based on the actual date, not the day in the Work Date field in My Settings. Users, especially those in the finance department, may adjust the Work Date to have different posting dates to the current date when posting journals.
This filter looks for a range between today’s date and five weeks prior. When you use the ‘..’ function, it’s important to have a value come afterwards. Otherwise, the range will be from the value before the ‘..’ to anything in the future, even beyond today’s date if there were records that included that!
This is filtering results by all those with a document date between the 14th April 2021 and the 14th April 2022.
As mentioned earlier, the reason these filters are so powerful in this format is that they are dynamic. If I wrote a filter ’14/04/21..14/04/22′, the results will always be those with a document date between that range. However, if I incorporate formulaic characters in a filter, like in the image above, it will work between now and a year prior but also in the future. So if I wanted to do the same thing in 6 months time, I could use the exact same filter and get new results as there will now be six months worth of new records in there, and six months of old records that are no longer applicable. If you save this filter as a view, you can get back to it easily.
Current month, week and year filters
Speaking of finance, there are CW, CM and CY filters. When used, these relate to the last day of the respective timeframe; be that ‘week’, ‘month’ or ‘year’. For example, using CM in July would default to the 31st July. Using CY will always default to the December 31st in the respective year. One to thing note is Sunday is the last day of the week for CW. I know some areas of the world see Sunday as the first day of the week.
For either CW, CM or CY, if you put a ‘-‘ in front of them, they will instead reflect the first day in the timeframe instead of the last. To avoid confusion, the difference between -CY and -1Y is that -CY will always be 1st January in whatever year you’re in. -1Y will always be a year before the current day you are in.
Work date and week
In some cases, Business Central filters do have some overlap in what each character represents. W is ‘work date’ but also ‘week’. When using date formulas, it will generally use week. However, if you wanted to search for records filtered by work date, you could simply input ‘w’ into a date field filter and it will filter the records by the value that matches your current Work Date field in My Settings.
WD is ‘week day’. Presumably, in the majority of the time, W and WD will be the same if you work a typical Monday to Friday routine. However, it’s just worth keeping in mind if you don’t.
‘-wd1’ reflects the most recent first day of the week, which is Monday 11th April for me. If I used ‘wd3’ instead, it would bring results based on the upcoming Wednesday.
WD works on the basis of ‘have you had that day in the week so far’. If you have, it will default to next week. For me right now, if I use wd5, it will default to tomorrow, as that is the nearest Friday. However, if I use wd1, it defaults to next Monday (April 18th) as we have already had Monday this week. To prove this, you can tab off the filter:
A quick side note…
Any date filters that appear further on in this post will be as a result of using a formula and tabbing off, not simply entering the dates.
Creating payment terms using Business Central filters
It’s quite normal for users to set up lots of different payment terms on the system. Some might be 30, 45 or 90 days. On a Customer Card, there’s the Payment Terms Code field. To demonstrate a formula, I’ve created a new record, ’90 days from end of month’. Each date filter I make will be available as a saved, filtered view. This allows me to see which customers have which payment terms.
If I choose to create a sales invoice for this customer, you’ll see the Due Date value defaults to 90 days on from the end of the month of the invoice. In this instance, the current month is April.
When it comes to views, you could set payment terms filters on the Customers page.
I’ve selected the Payment Terms Code field to filter by, input the CM+90 value and as we can see, I have the one Customer I applied it to.
Quarters using Business Central filters
One of the final bits to consider in relation to date filters are quarters of the year. Users use these to filter records by a particular quarter. Q is the character that reflects quarter in Business Central, so Q1 means first quarter.
Retrospective quarters
On the Item Ledger Entries page, if you use the Posting Date field filter with a range value of ‘Q1..Q2’, it brings back results for the upcoming range. In my case, as we are now in Q2, it brought back a blank set of results as it was looking for records in 2023. If I change this to Q3, it looks for records in 2022 as this is the next third quarter we’ll experience.
To manage this, I used ‘CY-1Y+Q1..CY-1Y+Q2-1D’ to give me a range of records with a Posting Date value between the first day of the first period of the year and the last day of that period. To break this down, ‘CY’ brings us to the last day of the current year. ‘-1Y’ brings us to the final day of the last year. ‘+Q1’ brings us a quarter into the current year. That’s part one. As ‘..’ indicates a range, the ‘CY-1Y’ part in the second half is the same. The ‘Q2-1D’ takes us to the final day of the first quarter.
To clarify, the reason why the ‘-1D’ is important to the filter above is because it brings you to the last day for the quarter you are looking at, rather than the first day of the next period. This means the results aren’t skewed as they don’t contain extra records relating to an additional day outside of the normal quarter date range.
You can adjust this accordingly for ranges that aren’t Q1-Q2, as long as they are retrospective and not the current quarter. If you’re currently in Q4, you could use this for Q3 instead, for example. What this means is, you should always be wary of what quarter you are in. It also means five separate views should cover all bases here: one for the current quarter and the rest for Q1, Q2, Q3 or Q4 specifically. We will cover the current quarter filter further down.
As peculiar as this may look, if you save it as a filtered view, this will only have to be set up once! Again, you can always check whether the formula you have entered is accurate by tabbing off. Once you do so, you’ll get the date range the formula reflects. See below:
Current quarter
By using ‘CQ’, you can filter results by the current quarter. If you wish to filter by results in the current quarter, you should use a different filter. This filter will be ‘CQ+1D-1Q..CQ’. ‘CQ+1D’ brings us to the first day of the next quarter as remember anything current (‘CW’, ‘CY’ etc) brings you to the last day of the respective period of time. The ‘-1Q’ brings us to the first day of this quarter. The ‘..CQ’ is the second part of the formula, giving us a range between the first and last day of the current period.
Viewing records from the previous year’s quarters
If you’re in Q1 and wanted to look back at Q4 of the previous year, you’d have to slightly adjust the formula. That would have to be ‘CY-1Y+Q4..CY+Q1-1D’. We have removed the second ‘-1Y’ element that we had in the ‘retrospective quarters’ section. This is because we are now envisioning a scenario when we are currently in a later year than the fourth quarter we want to see the related records for.
Forecasting quarters in the future
When we’ve looked at date filters, we’ve focused on using it to identify records that have already been created or posted, but forecasting will require date formulas too. Fortunately, I’d say these are simpler. It might be that you want to couple these forward looking date formulas with a Salesperson Code filter to assess targets.
Whatever period you are in, to see the period range for the next quarter, use this: ‘CQ+1D..CQ+1Q’. Here, ‘CQ+1D’ takes us to the first day of next quarter. The second part, following the ‘..’ says ‘CQ+1Q’, bringing us to the last day of next quarter. In my case, as it’s currently Q2, if I tab off the filter, it looks like this:
This is the date range for Q3.
However, if you are looking to see a specific quarter that isn’t necessarily the upcoming one, you can use ‘Q1..Q2-1D’. This filters results to include only those in the upcoming first quarter. In my case, that’s in 2023:
If you wanted to search for a different upcoming quarter, switch out Q1 and Q2 for whichever you want to filter between.
Creating accounting periods using Business Central filters
Another common formulaic character users employ is ‘P’ for ‘accounting period’. You can configure what constitutes an accounting period on the Accounting Periods page. On a ledger entries page, you could then filter by period. You can do this using a formula. For example, P1 would be first period. By simply inputting P1 into a date filter, it will create a range from the start to the end of the period. You can amend the filter to spread across multiple periods using the same ‘..’ function we have used throughout.
It’s important that your accounting periods are up to date, otherwise the formula will refer to the latest periods that have been set up in the fiscal year. To illustrate this, in my CRONUS company, 2022 hadn’t been set up as an accounting period, meaning each time I entered P1 for example, it was filtering by 2021. Having since amended this, it now looks to the accounting period in the current fiscal year. In my case, as each new period starts on the first day of the month, using P1 provides this:
Closing remarks
It’s worth clarifying this isn’t an exhaustive list of Business Central filters but a good indication as to what you can do. I’ve attached a link to Microsoft’s documentation on the topic. It has more examples of the types of characters available when using filters.
Thanks very much for reading! Hopefully these Business Central filters can save you a lot of time when managing records in the system. Remember to save filtered views, especially for formulaic date filters so that you can keep using them in the future, without having to amend them.
To keep up to date with when we post blogs, follow us on LinkedIn. If you have any questions, please get in contact with us.