Searching, Filtering, and Sorting Data
There are a few things that you can do that will help you find, pinpoint, and scan records in a list. These include sorting, searching and filtering.
When you want to search for data, such as customer names, addresses, or product groups, you enter criteria. In search criteria you can use all the numbers and letters that you normally use in the specific field. In addition, you can use special symbols to further filter the results. There are two ways to search: using the Quick Filter or column filters.
Sorting
Sorting makes it easy for you to get a quick overview of your data. If you have many customers, for example, you can choose to sort them by Customer No., Customer Posting Group, Currency Code, Country Region Code, or Sales Tax Registration No. 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 small downs arrow in the column heading, and then choose Ascending or Descending.
Note
Sorting is not supported images, BLOB fields, FlowFilters, and fields that do not belong to a table.
Searching by using the Quick Filter
You can add filters to all pages by using the Quick Filter. The Quick Filter is enabled by choosing the magnifier icon in the top right corner of a page. This filtering type is used for a fast entry of criteria.
Important
The Quick Filter provides an easy access to filter data by entering plain text, but does also provide a lot of search criteria options. Depending on whether you enter plain text or text including symbols, the Quick Filter behaves differently.
- If you enter plain text in the search criteria, the search criteria is interpreted as a case insensitive search that contains certain text.
- If you enter text including symbols in the search criteria, the search criteria is interpreted exactly as you entered it, and the search is case sensitive.
Quick filter criteria
| Search Criteria | Interpreted as... | Returns... |
|---|---|---|
| man | @*man* | All records that contain the text man and case insensitive. |
| se | @*se* | All records that contain the text se and case insensitive. |
| Man* | Starts with Man and case sensitive. | All records that start with the text Man. |
| 'man' | An exact text and case sensitive. | All records that match man exactly. |
| @man* | Starts with and case insensitive. | All records that start with man. |
| @*man | Ends with and case insensitive. | All records that end with man. |
Note
You cannot use a wildcard when filtering on enumeration fields, such as the Status field on sales orders. To enter a filter for this type of field, you can enter the numeric value as a filtering parameter. For example, in the Status field on a sales order that has the values Open, Released, Pending Approval, and Pending Prepayment, use the values 0, 1, 2, and 3 to filter for these options.
Searching by using column Filters
You can add a filter on one or more columns in a list. Filtering on columns is more flexible and enhanced than the Quick Filter.
To add a filter on a column
- Before you add a filter, choose
icon to change to the list view. - Choose the downwards arrow in the column heading, and then choose Filter.
- Do one of the following:
- Choose ... next to the box to select a value from a list.
- Enter filter criteria in the box. See the next section for details.
- Choose the OK button.
Filter criteria and symbols
When you enter criteria, you can use all the numbers and letters that you can normally use in the field. In addition, you can use special symbols to further filter the results. The following tables show the symbols which can be used in filters.
Important
There may be instances where field values contain these symbols and you want to filter on them. To do this, you must include the filter expression that contains the symbol in quotation marks (''). For example, if you want to filter on records that start with the text S&R, the filter expression is 'S&R*'.
(..) Interval
| Sample Expression | Records Displayed |
|---|---|
| 1100..2100 | Numbers 1100 through 2100 |
| ..2500 | Up to and including 2500 |
| ..12 31 00 | Dates up to and including 12 31 00 |
| P8.. | Information for accounting period 8 and thereafter |
| ..23 | From the beginning date until 23-current month-current year 23:59:59 |
| 23.. | From 23-current month-current year 0:00:00 until the end of time |
| 22..23 | From 22-current month-current year 0:00:00 until 23-current month-current year 23:59:59 |
(|) Either/or
| Sample Expression | Records Displayed |
|---|---|
| 1200|1300 | Numbers with 1200 or 1300 |
(<>) Not equal to
| Sample Expression | Records Displayed |
|---|---|
| <>0 | All numbers except 0 The SQL Server Option allows you to combine this symbol with a wild card expression. For example, <>A* meaning not equal to any text that starts with A. |
(>) Greater than
| Sample Expression | Records Displayed |
|---|---|
| >1200 | Numbers greater than 1200 |
(>=) Greater than or equal to
| Sample Expression | Records Displayed |
|---|---|
| >=1200 | Numbers greater than or equal to 1200 |
(<) Less than
| Sample Expression | Records Displayed |
|---|---|
| <1200 | Numbers less than 1200 |
(<=) Less than or equal to
| Sample Expression | Records Displayed |
|---|---|
| <=1200 | Numbers less than or equal to 1200 |
(&) And
| Sample Expression | Records Displayed |
|---|---|
| >200&<1200 | Numbers greater than 200 and less than 1200 |
('') An exact character match
| Sample Expression | Records Displayed |
|---|---|
| 'man' | Text that matches man exactly and is case sensitive. |
(@) Case insensitive
| Sample Expression | Records Displayed |
|---|---|
| @man* | Text that starts with man and is case insensitive. |
(*) An indefinite number of unknown characters
| Sample Expression | Records Displayed |
|---|---|
| Co | Text that contains "Co" and is case sensitive. |
| *Co | Text that ends with "Co" and is case sensitive. |
| Co* | Text that begins with "Co" and is case sensitive. |
(?) One unknown character
| Sample Expression | Records Displayed |
|---|---|
| Hans?n | Text such as Hansen or Hanson |
Combined format expressions
| Sample Expression | Records Displayed |
|---|---|
| 5999|8100..8490 | Include any records with the number 5999 or a number from the interval 8100 through 8490. |
| ..1299|1400.. | Include records with a number less than or equal to 1299 or a number equal to 1400 or greater (all numbers except 1300 through 1399). |
| >50&<100 | Include records with numbers that are greater than 50 and less than 100 (numbers 51 through 99). |