A Guide to Using Filters and Views in Business Central

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 will add descriptions showing what each is doing. This hopefully will give you some ideas as to where you can employ them in your environment.

Filter pane

To open up the 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. This is especially powerful when it comes to date range filters which we will cover further on in this post. Any saved views will be shown here:

By selecting any of the saved views, my filters will adjust to those which I have set up previously. Alternatively, click the dropdown arrow at the top and choose from the resulting list.

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.

‘A*’. Below this image and all subsequent ones I am going to write the formula I use below, just in case the image isn’t clear.

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. Equally, you could use ‘<>A*’ to not include any records which start with ‘A’.
‘10000|20000|30000’.

The pipe character acts as an ‘or’ function. I am therefore searching for records with a No. consisting of one of those three options.
‘10000..50000’.

The ‘..’ reflects a range. So 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:

‘10000..50000|C*’.

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.
‘C00??0’.

The question mark symbol in this case acts as an unknown character. This filter brings all the results which start with ‘C00’ and end in ‘0’ but can have any two characters in between them.
‘>30000&<60000’.

This is essentially looking for records with a No. field value bigger than 30000 and less than 60000. A similar filter you could employ would be ‘>=’ which is ‘equal to or greater than’.

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.

‘%myitems’.

This filters by the items I have in the ‘My Items’ area of my Role Centre.
‘@*fine art*’.

The use of the ‘@’ character tells the search that the value isn’t case sensitive. Using the asterisk before and after the value I am searching for must include ‘fine art’ 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 wouldn’t return a result either as the asterisk after the value means the Name field value should start with the value you entered.

Date range filters

If we are looking for records which have particular associated dates, we can use a different set of filters to narrow down the results. So 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.

‘Fri11..t’.

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.
’18/03/22..13/04/22′.

The image here shows the same as the image above, but the presentation of the filter changes to this when I tab off.
‘t-5w..t’.

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!
‘cd-1y..t’.

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

Today, current day and work day filters

Another bit to clarify on date filters is the difference between ‘T’, ‘CD’ and ‘WD’. CD is current day. T and CD are actually the same. But 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 automatically have different posting dates to the current date when posting journals.

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

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. However, it’s just worth keeping in mind if you don’t.

‘-wd1..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:

‘wd5’.
’15/04/22′.
‘wd1’.
’18/04/22′.

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.

Payment terms 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’.

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 current month, April.

When it comes to views, you could set payment terms filters on the Customers page.

‘CM+90’.

I’ve selected the field to filter by as Payment Terms Code, inputted the value I created beforehand and as we can see, I have the one Customer I applied it to.

Quarters

One of the final bits to consider in relation to date filters are quarters of the year. Users will 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 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:

’01/01/22..31/03/22′.

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:

’01/07/22..30/09/22′.

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:

’01/01/23..31/03/23′.

If you wanted to search for a different upcoming quarter, switch out Q1 and Q2 for whichever you want to filter between.

Accounting period filters

Another common one 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:

’01/01/22..31/01/22′.

Closing remarks

It’s worth clarifying this isn’t an exhaustive list of filters but a good indication as to what you can do with Business Central. 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. Click here to give it a read.

Thanks very much for reading! Hopefully these keyboard shortcuts and record filters can save you a lot of time when managing records in the system. Remember to save filter 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 by clicking here. To get in contact with us, click here.

Leave a Comment Cancel Reply

%%footer%%