Advanced search

Ranges

You can search for ranges of data by using brackets as follows:

Ranges can use wildcards and combinations of '[' and '}' to find all transactions with a negative value.

Dates

These date macros are provided:

'x' is a relative number with '0' being the current year, month, week, and date.

For example, 'INDT:YEAR(-1)'.

The DATE macro can be combined with ranges to create your own range. For example, 'LMDT:[DATE(-14) TO DATE(0)] returns all transactions within the last two weeks.

When searching with NOT, this must be preceded by another query. For example, 'No:780 NOT INDT:YEAR(0)'.

Date macros are very useful with saved searches as they give a quick link to, for example, last month's orders. They can be saved to Favorites, Canvas, as a menu option, or in the toolbox area, so they can be easily accessed.

Wildcards

'?' - replaces a single character in a specific position and can be used together with other wildcards. For example, 'SUNO:?*'.

For numeric fields you can use the NOT statement. For example nb mkll?>e, 'NOT STQT:0'.

'~' – can be used in alphabetic characters, not numeric characters and is a fuzzy search. This means that the characters do not need to exactly match those in the query.

Sorting search results

Sorting search results allows you to free sort any field in the main table of the function, apart from fields containing multiple words such as a description. A search finding results in a related table, set up in 'Related Search. Open' (CMS022) are not sorted.

A search which finds results in both related tables and the main table presents hits for the main table first in the selected sorting method, followed by hits from the related tables which are not sorted.

This sort is done by using the keyword 'SortBy:x,a,!y' where '!' performs a descending order.

You can save sorting in the toolbox area which enables you to re-sort the list without having to use a separate index. For example, you could re-sort results for the current company number using 'CONO:xxx', where 'xxx' is replaced by the currently used M3 BE company number.

Searching multiple words

Searching multiple words across several columns can easily return false results as it returns all instances of the search in all fields, in all columns. For example, 'chees* 10'.

By using the keyword 'SearchFields:x;y;z', you can search in multiple columns but only in the specified fields. For example, 'SearchFields:ITDS,FUDS chees* 10'.

Key search

Searching with the help of key search simplifies complex queries so they are easier to use. It also allows searching in other tables without the need for matching keys, as required by the related search setup in (CMS022).

For example, you can define a query which, when specified, the preset part in the key search is automatically appended.

Available key searches are displayed by using Ctrl+F.

Note: A key search is performed on both the main table and related tables (defined in (CMS022) or related tables through 'Related Tables. Connect' (CMS011)).
Related topics