Enhanced Search Options

From dispage wiki

Jump to:navigation, search

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

Date input mode.png


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.

Relative input mode.png


Text operators

Text operators.png


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.

In operator.png


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.

Between operator.png



Numeric operators

Numeric operators.png

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

Navigation
dispage support
Toolbox