Enhanced Search Options
From dispage wiki
The Enhanced Search Options discussed in this article are accessible by clicking the "More Detail" icon on the right of any search field of the SugarCRM™ Advanced Search Panel.
From SugarCRM 6, Enhanced Search Options are also available in the Basic Search Panel.
Contents |
FULL Version
Date / Datetime Fields
The following Date Input Mode are available thanks to the Extended Date Search feature:
Date Range
The classic Enhanced Search date range search, with a "From" and a "To" field :
a. If From Field is filled and To Field is blank, the result dates are after or equal to the midnight of that date.
b. If From Field is blank and To Field is filled, the result dates are before or equal to 23:59:59 hours of that date.
c. If both fields are filled, the result dates are between From Field and To Field (applying both the rules a. and b.)
Day Selection
The single day search that SugarCRM performs by default.
Month / Year
Results can be filtered by a month of the year and / or by a year.
Quarter
A quarter-based filter can be applied too.
Relative
A easy way to select the results by specifying a relative time window based on the current date and time.
Text operators
The following text operators are available:
Starting with (selected by default)
Returns the values starting with the search string
Ending with
Returns the values ending with the search string
Containing
Returns the values containing the search string
Note: Operators at point 1, 2 and 3 allow the use of query wildchars ('_' or '%') in the search string.
Equal to
Returns the values exactly equal to the search string
In
Allows to enter a list of values to search. List items can be added or removed with the + and - Buttons.
RegExp Like (only for MySQL database)
The result fields must match the pattern in the search field using regular expression MySQL syntax (see the RLIKE operator of the BASIC Version for some usage examples).
Between
Performs a search of the values included between two strings.
Numeric operators
Arithmetic operators
The "=", "<", "<=", ">", ">=" comparison operators are available.
Between
Performs a search of the values included between two numbers (see the corresponding Text Operator).
Search Options
OR operator
If the OR checkbox is checked, the OR boolean operator will be used, instead of the AND used by default. There are three possible cases, depending on the choices of OR options in the fields of the search page :
i) When there is no OR field option checked in the search page, the result query is the classical
<condition_AND_1> AND ... AND <condition_AND_n>
ii) When there is exactly one OR field option checked and there are one or more AND conditions, the result query is:
<condition_OR> OR ( <condition_AND_1> AND ... AND <condition_AND_n> )
iii) When there are more than one OR checked, all the OR fields are grouped together in the final query and are ANDed with the other conditions (if present) as the following:
( <condition_OR_1> OR ... OR <condition_OR_m> ) AND <condition_AND_1> AND ... AND <condition_AND_n>
NOT query operator
If the NOT checkbox is checked, the corresponding condition is denied.
Empty-field search
Searching for an empty field is not allowed in SugarCRM without Enhanced Search: if the search field is left empty it's simply ignored. To search for an empty field using Enhanced Search, the EMPTY checkbox must be checked.
Note: Checking the EMPTY checkbox has no effect if the search field is the "Only my items" checkbox filter
BASIC Version
Date / Datetime Fields
Default SugarCRM search field is replaced by a From field and a To field.
a. If From Field is filled and To Field is blank, the result dates are after or equal to the midnight of that date.
b. If From Field is blank and To Field is filled, the result dates are before or equal to 23:59:59 hours of that date.
c. If both fields are filled, the result dates are between From Field and To Field (applying both the rules a. and b.)
Text operators
The Following text operators are available:
LIKE (selected by default)
When selected two cases can be distinguished:
a. If no query wildchar ('_' or '%') is present in the searchstring, two '%' wildchars are automatically added at the top and the bottom of it. The effect is that the result fields must contain the text in the search field. b. If any query wildchar is present in the search string, the search string is used in the query as is, without adding any '%'.
=
The results must match exactly the search field.
RLIKE
The 'RLIKE' operator (only for MySQL databases) is used to filter result set: the result text must match the pattern in the search field using regular expression MySQL syntax.
Some RLIKE search examples:
a. Searching for empty / not empty text with trailing spaces trimmed: Check the NOT checkbox if search for not empty textfields and type in the search field the following:
^[ ]*$
b. Searching for all Streets and Avenues in the address field: Type in the search field the following:
st\.|av\.
N.B.: The regular expression special chars must be backslashed (\). Check the MySQL online documentation for a complete regular expression reference.
Numeric operators
Arithmetic operators
The "=", "<", "<=", ">", ">=" comparison operators are available.
Search Options
OR operator
If the OR checkbox is checked, the OR boolean operator will be used, instead of the AND used by default. There are three possible cases, depending on the choices of OR options in the fields of the search page :
i) When there is no OR field option checked in the search page, the result query is the classical
<condition_AND_1> AND ... AND <condition_AND_n>
ii) When there is exactly one OR field option checked and there are one or more AND conditions, the result query is:
<condition_OR> OR ( <condition_AND_1> AND ... AND <condition_AND_n> )
iii) When there are more than one OR checked, all the OR fields are grouped together in the final query and are ANDed with the other conditions (if present) as the following:
( <condition_OR_1> OR ... OR <condition_OR_m> ) AND <condition_AND_1> AND ... AND <condition_AND_n>
NOT query operator
If the NOT checkbox is checked, the corresponding condition is denied.
Empty-field search
Searching for an empty field is not allowed in SugarCRM without Enhanced Search: if the search field is left empty it's simply ignored. To search for an empty field using Enhanced Search, the EMPTY checkbox must be checked.
Note: Checking the EMPTY checkbox has no effect if the search field is the "Only my items" checkbox filter
