Sorting, Searching, and Filtering

There are a few things that you can do that will help you scan, find, and limit records on a list or in a report or XMLport. These include sorting, searching, and filtering. You can apply some or all of these simultaneously to quickly find or analyze your data.

For reports and XMLports, as on lists, you can set filters to delimit which data to include in the report or XMLport, but you can't sort and search.

Sorting

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

To sort a list, you can either:

• Choose a column heading text to toggle between ascending and descending order, or
• Choose the drop-down arrow in the column heading, then choose the Ascending or Descending action.

Searching

At the top of each list page, there's a Search action that provides a quick and easy way to reduce the records in a list and display only those records that contain the data that you're interested in seeing.

To search, just choose the Search action, and then in the box, type the text that you're looking for. You can enter letters, numbers, and other symbols.

In general, search will attempt to match text across all fields. It doesn't distinguish between uppercase and lowercase characters (case insensitive) and will match text placed anywhere in the field, at the beginning, end, or in the middle.

Fine-tuning the Search with Filter criteria

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

• To find only field values that match the entire text and case exactly, place the search text between single quotes '' (for example, 'man').

• To find field values that start with a certain text and match the case, place * after the search text (for example man*).

• To find field values that end with a certain text and match the case, place * before the search text (for example *man).

• When using '' or *, the search is case-sensitive. If you want to make the search case insensitive, place @ before the search text (for example @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, report, or XMLport. There are two major differences between searching and filtering, as described in the table below.

Filtering enables you to display records for specific accounts or customers, dates, amounts, and other information by specifying filter criteria. Only records that match the criteria are displayed on the list or included in the report, batch job, or XMLport. If you specify criteria for multiple fields, then only records that match all criteria will be displayed.

For lists, the filters are displayed on 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.

Filtering with Option Fields

For "ordinary" fields that hold data, setup date, or business data, you can set filters both by selecting data and by typing filter values, and you can use symbols to define advanced filter criteria. For more information, see Entering Filter Criteria.

For fields of type Option, however, you can only set a filter by selecting one or more options from a drop-down list of the available options. An example of an option field is the Status field on the Sales Orders page.

Setting Filters on Lists

On lists, you set filters by using the filter pane. To display the filter pane for a list, choose the drop-down arrow next to the name of the page, and then choose the Show filter pane action. Alternatively, select Shift+F3.

To display the filter pane for a column on a list, choose the drop-down arrow, and then choose the Filter action. Alternatively, select Shift+F3. The filter pane opens with the selected column shown as a filter field in the Filter list by section.

The filter pane displays the current filters for a list, and enables you to set your own custom filters on one or more fields by choosing the + Filter action.

A filter pane is divided in three sections: Views, Filter list by, and Filter totals by:

• Views

  Some lists include the Views section. Views are variations of the list that have been preconfigured with filters. You can define and save as many views as you want per list. The views will be available to you on any device you sign into. For more information, see Save and Personalize List Views.

• Filter list by

  This section is where you add filters on specific fields to reduce the number of displayed records. To add a filter, choose the + Filter action. Then, type the name of the field that you want to filter the list by or pick a field from the drop-down list.

• Filter totals by

  Some lists that display calculated fields, such as amounts and quantities, will include the Filter totals by section where you can adjust various dimensions that influence calculations. To add a filter, choose the + Filter action. Then, type the name of the field that you want to filter the list by or pick a field from the drop-down list.

  Note

  Filters in the Filter totals by section are controlled by FlowFilters on the page design. For technical information, see FlowFilters.

You can set a simple filter directly on a list within using the filter pane, namely a filter that displays only records with the same value as in the selected cell. Select a cell on the list, choose the drop-down arrow, and then choose the Filter to This Value action. Alternatively, select Alt+F3.

Setting Filters in Reports, Batch Jobs, and XMLports

For reports and XMLports, the filters are visible directly on the request page. The request page displays the last used filters according to your selection in the Use default values from field. For more information, see Use Saved Settings.

The main Filter section shows the default filter fields that you use to delimit which records to include in the report or XMLport. To add a filter, choose the + Filter action. Then, type the name of the field that you want to filter by, or pick a field from the drop-down list.

In the Filter totals by section, you can adjust various dimensions that influence calculations in the report or XMLport. To add a filter, choose the + Filter action. Then, type the name of the field that you want to filter by, or pick a field from the drop-down list.

Entering 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, filtering a field that has fixed values will only let you choose from those values. For more information about special filter symbols, see Filter criteria and Filter tokens.

Columns that already have filters are indicated by the icon in the column heading. To remove a filter, choose the drop-down arrow, and then choose 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. But there's also a set of special symbols that you can use as operators to further filter the results. The following sections describe these symbols and 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

Filtering on values that contain symbols

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

• &
• (
• )
• =
• |

If you want to filter on any of these symbols, place the filter expression in single quotes ('<expression with symbol>'). For example, if you wanted to filter on records that start with the text J & V, the filter expression would be 'J & V*'.

This requirement isn't necessary for other symbols.

Filter Tokens

When entering filter criteria, you can also type words that have special meaning, called filter tokens. After entering the token word, the word is replaced by the value or values that it represents. Filter tokens make filtering easier by reducing the need to navigate to other pages to look up values you want to add to your filter. The tables below describe some of the tokens you can type as filter criteria.

(%me or %user) Records Assigned to You

Use %me or %user when filtering fields that contain the user ID, such as Assigned to User ID field, to display all records that are assigned to you.

(%mycustomers) Customers in My Customers

Use %mycustomers in the customer No field to display all records for customers that are included in the My Customers list on your Role Center.

(%myitems) Items in My Items

Use %myitems in the item No field to display all records for items that are included in the My Items list on your Role Center.

(%myvendors) Vendors in My Vendors

Use %myvendors in the vendor No field to display all records for vendors that are included in the My Vendors list on your Role Center.

See also

Searching and Filtering FAQ
Save and Personalize List Views
Work with Business Central

Find free e-learning modules for Business Central here