Common Filter Criteria & Operators in Business Central
Hi Readers,
In todays post, we will look at the various filtering criteria and operators available in Business Central.
(..) Interval
The two dots are used to specify a range of data to filter. See some sample expressions below
| Sample Expression | Field Type | Effect on Records |
| 2000..2500 | Numbers/Decimal Fields | Records will be filtered from 2000 upto 2500. |
| ..2500 | Numbers/Decimal Fields | Records will be filtered up to and including 2500. |
| 2500.. | Numbers/Decimal Fields | Records will be filtered from 2500 going forward. |
| ..15 | Date Field | Records will be filtered from first date available upto 15th date - Current Month - Current Year |
| 16.. | Date Field | Records will be filtered from 15th date - Current Month - Current Year going forward |
| 0807.. | Date Field | Records will be filtered from 08th date - Eighth month - Current Year going forward |
| 080724.. | Date Field | Records will be filtered from 08th date - Eighth month - Year 24 - (08/08/24) going forward |
| 15..16 | Date Field | Records will be filtered betwwen 15th date - Current Month - Current Year and 16th date - Current Month - Current Year |
| ..15/07/24 | Date Field | Records will be filtered from first date available upto 15/07/24 |
| 15/07/24.. | Date Field | Records will be filtered from 15/07/24 going forward |
| P | Date Field |
The letter P will filter from the start of the month to the end of the month of the work date set. e.g If the work date is 02/08/2024, P will resolve to 01/08/2024..31/08/2024 |
| P1 | Date Field |
The letter P1 will filter from the start of the month to the end of the month of the first accounting period of the fiscal year the current work date is. e.g If the work date is 02/08/2024 and the fiscal year is Jan 2024 to Dec 2024, P1 will resolve to 01/01/2024..31/01/2024
To filter other accounting periods, the number can be changed as needed e.g P7 refers to 01/07/2024..31/07/2024 P13 refers to 01/01/2025..31/01/2025 |
( | ) Either/or
The pipe symbol ( | ) is used to indicate either/or of the criteria to filter.
| Sample Expression | Field Type | Effect on Records |
| 2000|2500 | Numbers/Decimal Fields | Records will be filtered with numbers with 2000 or 2500 |
| 01/08/24|02/08/24 | Date | Records will be filtered with dates 01/08/24 or 02/08/24 |
| P7|P4 | Date | Records will be filtered from between 01/07/24..31/07/24 or between 01/08/24..31/08/24 |
(<>) Not equal to
The symbol ( <> ) is used to indicate not equal to.
| Sample Expression | Field Type | Effect on Records |
| <>2500 | Numbers/Decimal Fields | Records will be filtered which are not equal to 2500 |
| <>01/08/24 | Date | Records will be filtered without including the date 01/08/24 |
| <>BCADMIN | User Id | Records will be filtered without user id BCADMIN |
(>) Greater than , (>=) Greater than or equal to, (<) Less than , (<=) Less than or equal to
| Sample Expression | Field Type | Effect on Records |
| >2500 | Numbers/Decimal Fields | Records will be filtered which are greater than 2500 and not including 2500 |
| >=10/07/24 | Date | Records will be filtered which are equal to a date 10/07/24 and greater than that date |
| <10/07/24 | Date | Records will be filtered which come before date 10/07/24 |
| <=2500 | Records will be filtered which are less than 2500 and including 2500 |
(&) And
The ampersand symbol (&) is used to combine various filter criteria.
| Sample Expression | Field Type | Effect on Records |
| >2500&<5000 | Numbers/Decimal Fields | Records will be filtered which are greater than 2500 and not including 2500 and less than 5000 and not including 5000 |
| >10/07/24&<01/08/24 | Date | Records will be filtered which are a date greater than 10/07/24 and less than 01/08/24 |
The difference between the pipe symbol ( | ) and ampersand symbol (&) is that for pipe either condition can be true but for ampersand both must be true.
Lets see the difference.
I will place the filter of <>01/02/2024|<>30/04/24 on the Posting Date filter below.
The expectation would be that both records would be excluded because I used the <> operator. However , it fails because each part of the filter negates the other causing the filter not to work.
Lets use the & operator now.
The records should now not be there because the & operator must have both conditions as true.
('') Blank
Two single quotes are used to filter blank records.
| Sample Expression | Field Type | Effect on Records |
| '' | Any | Records which are blank |
| <>'' | Any | Records which are not blank |
(@) Case insensitive
The @ operator is used in conjuction with star symbol (*) text fields to find text that matches a particular character and is case insensitive
| Sample Expression | Field Type | Effect on Records |
| @Invoice* | Most fields apart from Date | Records which starts with Invoice and is case insensitive. |
| @108* | Most fields apart from Date | Records which start with 108 e.g document number with 108008 will be filtered |
(*) An indefinite number of unknown characters - Case Sensitive
| Sample Expression | Field Type | Effect on Records |
| *Inv* | Most fields apart from Date | Records which contains Inv and is case sensitive. |
| *Inv | Most fields apart from Date | Records which end with Inv and is case sensitive. |
| Invoice* | Most fields apart from Date | Records which end with Inv and is case sensitive. |
| *108* | Numbers |
Records which contain number 108 e.g Document Number 901087 will be filtered. This does not work on decimal fields. |
Combined format expressions
You can combine all above operators to achieve your desired filters. A few examples below
| Needs | Filter |
| Filter all dates upto today but exclude month of May | Posting Date Field : 01/01/24..t&<>p5 |
| Filter 3 sales people code from the list (BC , SM, PT) | Salespeople Code Field : <>BC&<>SM&<>PT |
Date Expressions
Business Central provides various calendar expressions which can be used to accelerate filtering on date fields without typing out the full dates e.g P1 which we covered.
For the examples below , I will assume the current date is 02/08/2024 and fiscal year is Jan 2024 to Dec 2024
| Date Expressions | Resolves To |
| T / Today |
Set to today's date/ work date. Field will be Populated with - 02/08/2024 |
| P |
Sets the start and end of the month for the current accounting period that the current date belongs to. Field will be Populated with - 01/08/2024..31/08/2024 |
| P1 |
Sets the start and end of the month for the first accounting period the current date belongs to. Field will be Populated with - 01/01/2024..31/01/2024 |
| P(X) |
Sets the start and end of the month for the (X) accounting period the current date belongs to. P5 - Field will be Populated with - 01/05/2024..31/05/2024 P13 - Field will be Populated with - 01/01/2025..31/01/2025 |
| M/Mo |
Sets to Monday of the work date week. Field will be Populated with - 29/07/2024 |
| Tu |
Sets to Tuesday of the work date week. Field will be Populated with - 30/07/2024 |
| We |
Sets to Wednesday of the work date week. Field will be Populated with - 31/07/2024 |
| Th |
Sets to Thursday of the work date week. Field will be Populated with - 01/08/2024 |
| Fr |
Sets to Friday of the work date week. Field will be Populated with - 02/08/2024 |
| S/ Sa |
Sets to Saturday of the work date week. Field will be Populated with - 03/08/2024 |
| Su |
Sets to Sunday of the work date week Field will be Populated with - 04/08/2024 |
| / - |
e.g Th24 - Thursday of the 24th week of the year. - 13/06/24 Fr52 - Friday of the 52nd week of the year. - 27/12/24 Tu-1 : Tuesday of the 1st week of the year - 02/01/24 |
| CW |
Sets to end of the current week Field will be Populated with - 04/08/2024 |
| CM |
Sets to end of the current month Field will be Populated with - 31/08/2024 |
| CQ |
Sets to end of the current quarter Field will be Populated with - 30/09/2024 |
| CY |
Sets to end of the current year Field will be Populated with - 31/12/2024 |
| -CW |
Sets to start of the current week Field will be Populated with - 29/07/2024 |
| -CM |
Sets to start of the current month Field will be Populated with - 01/08/2024 |
| -CQ |
Sets to start of the current quarter Field will be Populated with - 01/07/2024 |
| -CY |
Sets to start of the current year Field will be Populated with - 01/01/2024 |
| -1Y |
Sets to a day one year ago Field will be Populated with - 02/08/23 |
| -1W |
Sets to a day one week ago Field will be Populated with - 26/07/24 |
| -1D |
Sets to the day before today Field will be Populated with - 01/08/24 |
You can combine several of these data expressions to create desired filters
| Needs | Filter |
| Filter start of the year to the end of the year | -CY..CY |
| Filter start of the month to end of the month | -CM..CM |
| Filter from last year same date to today | -1Y..t |
| Filter the last 2 previous weeks to today | -2W..t |
| Filter the previous start of the month to end of month | -CM-1M..CM-1M |
| Filter the previous start of the year to end of last year | -CY-1Y..CY-1Y |
| Filter start of the year to end of previous quarter | -CY..CQ-1Q |
| Filter last year quarter 2 | -CY-1Y+1Q..-CY-1Y+2Q-1D |
| 10 days after end of the month | CM+10D |
You can play around and build as needed.
These can be important if you want to save views.
For example, if you want to save a view of This months Posted Sales Invoices only
Click on the 3 dots to save a new view.
Add the name of the view.
You can then easily switch between the views as needed
ENDS.