Sort, search, and filter data in lists, reports, or XMLports

A few mechanisms are available to help you scan, find, and limit the records in a list, a report, or an XMLport. They include operations like sorting, searching, and filtering. You can apply some or all of these operations simultaneously to quickly find or analyze your data.

For reports and XMLports, as for lists, you can set filters to delimit the data that is included. However, you can't sort or search.

Sorting

Sorting makes it easy to get a quick overview of your data. For example, if you have many customers, you can sort them by Customer No., Currency Code, or Country Region Code value to get the overview that you need.

To sort a list, follow one of these steps:

• Select the text of a column heading to switch between ascending and descending order.
• Select the dropdown arrow in the column heading, and then select either the Ascending action or the Descending action.

Searching

At the top of each list page is a Search button that provides a quick and easy way to reduce the number of records in a list and show only those records that contain the data that you're interested in.

To search, select the Search button or the F3 key on your keyboard. Then enter the text that you're looking for in the search field. The search text that you enter can include letters, numbers, and other symbols.

In general, search tries to match text across all fields. It doesn't distinguish between uppercase and lowercase characters (in other words, it's case-insensitive). It also matches text that occurs anywhere in the field: at the beginning, in the middle, or at the end.

Choose between modern search and legacy search

[This section is prerelease documentation and is subject to change.]

On the most common pages, such as Customers, Contacts, and Items, you can select the dropdown arrow in the search field and then select between two search options on the menu: Use modern search and Use legacy search.

Legacy search is the older search method. It's the only method that is available in Business Central 2024 release wave 1 and earlier. Modern search is a newer, faster, and more flexible search method. The key difference is that legacy search finds only records that contain the exact words that you enter, in the exact order that you enter them in, whereas modern search finds records that contain matching words in any order.

For example, the CRONUS demo company has an item that is named LONDON Swivel Chair, blue. If you enter London chair as the search text, legacy search doesn't return any results, because that exact text doesn't match any part of the item name. However, modern search finds the item by matching the words "London" and "chair" separately.

If you want to find all blue chairs in the CRONUS demo company, you might enter either blue chair or chair blue as the search text. In this case, modern search retrieves not only LONDON Swivel Chair, blue but also TOKYO Guest Chair, blue. However, once again, legacy search returns no results. To get results by using legacy search, you must enter chair, blue as the search text.

If you want to mimic the behavior of legacy search when you use modern search, you can enclose your search text in quotation marks, just as you can do in popular search engines. In this way, you refine the results by excluding any that aren't exact matches. For example, if you enter "blue chair" as the search text, modern search returns no results, because that exact text doesn't occur in any item name in the demo data.

Fine-tune the search by using filter criteria (legacy search only)

You can make a more exact search by using filter operators, expressions, and filter tokens. Unlike filtering, these criteria are applied across all fields when they are used in the search field. Therefore, they are less efficient than filtering.

• To find only field values that exactly match the entire search text, and the case of it, enclose the text in single quotation marks (''). For example, enter 'man'.
• To find field values that start with specific text and match the case of it, add an asterisk (*) after the text. For example, enter man*.
• To find field values that end with specific text and match the case of it, add an asterisk (*) before the text. For example, enter *man.
• When you use single quotation marks ('') or asterisks (*), the search is case-sensitive. To make it case-insensitive, add an at sign (@) before the search text. For example, enter @man*.

The following table provides some examples to explain how you can use the search.

Filtering

Filtering provides a more advanced and versatile way to control which records are included in a list, a report, or an XMLport. There are two major differences between searching and filtering, as the following table shows.

By specifying filter criteria, you can show records for specific accounts or customers, dates, amounts, and other information. Only records that match the criteria are shown in the list or included in the report, batch job, or XMLport. If you specify criteria for multiple fields, only records that match all criteria are shown.

For lists, the filters are shown in a filter pane that appears to the left of the list when you activate it. For reports, batch jobs, and XMLports, the filters are visible directly on the request page.

Filter by using option fields

For "ordinary" fields that hold data, setup date, or business data, you can set filters both by selecting data and by entering filter values. You can also use symbols to define advanced filter criteria. Learn more in the Enter filter criteria section.

However, for fields of the Option type, you can set a filter only by selecting one or more available options in a dropdown list. An example of an option field is the Status field on the Sales Orders page.

Set filters on lists

For lists, you set filters by using the filter pane. To show the filter pane for a list, select the dropdown arrow next to the name of the page, and then select the Show filter pane action. Alternatively, select Shift+F3.

To show the filter pane for a column in a list, select the dropdown arrow, and then select the Filter action. Alternatively, select Shift+F3. The filter pane appears, and the selected column is shown as a filter field in the Filter list by section.

The filter pane shows the current filters for a list. You can set your own custom filters on one or more fields by selecting the + Filter action.

The filter pane is divided into three sections: Views, Filter list by, and Filter totals by:

• Views – Some lists include the Views section. Views are variations of the list that are preconfigured with filters. You can define and save as many views as you want for each list. The views are available to you on any device that you sign in to. Learn more in Save and personalize list views.

• Filter list by – This section is where you add filters on specific fields to reduce the number of records that are shown. To add a filter, select the + Filter action. Then enter the name of the field that you want to filter the list by, or select a field in the dropdown list.

• Filter totals by – Some lists that show calculated fields, such as amounts and quantities, include the Filter totals by section. Here, you can adjust various dimensions that influence the calculations. To add a filter, select the + Filter action. Then enter the name of the field that you want to filter the list by, or select a field in the dropdown list.

  Note

  Filters in the Filter totals by section are controlled by FlowFilters in the page design. Learn about the technical aspects of FlowFilters in FlowFilters overview.

By using the filter pane, you can set a simple filter directly on a list to show only records that have the same value as a selected cell. Select a cell in the list, select the dropdown arrow, and then select the Filter to This Value action. Alternatively, select Alt+F3.

Set filters in reports, batch jobs, and XMLports

For reports and XMLports, the filters are visible directly on the request page. The request page shows the last-used filters according to your selection in the Use default values from field. Learn more in Use default values—predefined settings.

The main Filter section shows the default filter fields that you use to define which records should be included in the report or XMLport. To add a filter, select the + Filter action. Then enter the name of the field that you want to filter by, or select a field in the dropdown list.

In the Filter totals by section, you can adjust various dimensions that influence the calculations in the report or XMLport. To add a filter, select the + Filter action. Then enter the name of the field that you want to filter by, or select a field in the dropdown list.

Enter filter criteria

Both in the filter pane and on a request page, you enter your filter criteria in the box under the filter field.

The type of the filter field determines which criteria you can enter. For example, to filter a field that has fixed values, you can select only among those values. Learn more about special filter symbols in the Filter criteria and operators and Filter tokens sections.

Columns that already have filters are indicated by the symbol in the column heading. To remove a filter, select the dropdown arrow, and then select the Clear Filter action.

Filter criteria and operators

When you enter criteria, you can use all the numbers and letters that you normally use in the field. In addition, there is a set of special symbols that you can use as operators to further filter the results. The following sections describe these symbols and explain how to use them as operators in filters.

(..) Interval

(|) Either/or

(<>) Not equal to

(>) Greater than

(>=) Greater than or equal to

(<) Less than

(<=) Less than or equal to

(&) And

('') An exact character match

(@) Case-insensitive

(*) An indefinite number of unknown characters

(?) One unknown character

Combined format expressions

Filter on values that contain symbols

There might be cases where field values contain one of the following symbols:

• &
• (
• )
• =
• |

If you want to filter on any of these symbols, enclose the filter expression in single quotation marks (''). For example, to filter on records that start with the text J & V, the filter expression is 'J & V*'.

This requirement applies only to the five symbols in the preceding list.

Filter tokens

When you enter filter criteria, you can also enter filter tokens, which are words that have special meaning. After you enter a filter token, it's replaced by the value or values that it represents. Filter tokens make filtering easier, because you don't have to open other pages to look up values that you want to add to your filter. The following sections describe some of the tokens that you can enter as filter criteria.

(%me or %user) Records assigned to you

When you filter fields that contain a user ID, such as the Assigned to User ID field, use the %me or %user filter token to show all records that are assigned to you.

(%mycustomers) Customers in My Customers

In the customer number (No) field, use the %mycustomers filter token to show all records for customers that are included in the My Customers list on your Role Center.

(%myitems) Items in My Items

In the item number (No) field, use the %myitems filter token to show all records for items that are included in the My Items list on your Role Center.

(%myvendors) Vendors in My Vendors

In the vendor number (No) field, use the %myvendors filter token to show all records for vendors that are included in the My Vendors list on your Role Center.

Related information

Searching and filtering FAQ
Save and personalize list views
Work with Business Central
Search and filter lists

Find free e-learning modules for Business Central here