******************************************************************************* * * Enhanced Search for SugarCRM * * Plugin adding new search capabilities to SugarCRM Advanced Search. * * Developed by Patrizio Gelosi * * Version: 2.1.1 - Date : 12 Jun 2008 * ******************************************************************************* ****************************** INSTALLATION ****************************** Once logged as admin user, install the package using the "Administration -> Module Loader" tool. *********************** UNINSTALLATION / DISABLING *********************** 1- Once logged as admin user, uninstall / disable the package using the "Administration -> Module Loader" tool. 2- Delete the cache dirs '\cache\modules' and '\cache\smarty' to permanently erase the plugin. ************************* SUGARCRM COMPATIBILITY ************************* Check the most recent compatibility-.txt file to find out which Enhanced Search version is compatible with your SugarCRM version. **************************** SUGARCRM UPGRADE **************************** To upgrade SugarCRM with Enhanced Search installed : 1- Check if the new release of SugarCRM to upgrade to is compatible with your Enhanced Search version. 2- Uninstall Enhanced Search. 3- Install the SugarCRM upgrade. 4- Re-install Enhanced Search. ******************************* DESCRIPTION ****************************** The goal of the Enhanced Search plugin is to make SugarCRM search capabilities as close as possible to the sql query ones. To achieve it, Enhanced Search improves the Advanced Search Page with additional features, and provides the user with new search capabilities. In addition, all the Search forms (except the Basic search), such as the related field Selection windows, are also improved with the new features. For example, the Enhanced Search Options can be found on the Selection popup window of the 'Account Name' field on the Contacts Edit Page. Each searchfield of the same type on each search page has the same Enhanced Search Options. The plugin is fully integrated with SugarCRM, so it automatically saves the Enhanced Option data of the last search. Moreover, it saves the Enhanced Search Option data of the searches that the user saves manually. Enhanced Search is multilanguage, the following languages are currently supported: - English - French - German - Italian - Spanish ********************************** USAGE ********************************* Some Enhanced Search elements, such as the To-date searchfield, can be found directly on the search page. Many Enhanced Search Options elements appear on a Sugar-styled popup menu when a "More Detail" icon (placed on the right of the field) is clicked. To help the user to manage the Enhanced Search Options in all the popup menus of a page at the same time (when a popup becomes visible the others are automatically hidden), a star (*) automatically appears on the right of the "More Detail" icon as soon as an Enhanced Search Option is changed from the default value. Searchfield types and their new features are described below: 1- From-date and To-date. AFFECTED FIELDS (FULL & DEMO VERSION): Date/datetime searchfield. The Enhanced date searchfield consists of two input fields: From-date and To-date. a. If From-date is valued and To-date is blank, the result dates are after or equal to the midnight of that date. b. If From-date is blank and To-date is valued, the result dates are before or equal to 23:59:59 hours of that date. c. If both fields are valued, the result dates are between From-date and To-date (applying both the rules a. and b.) . To obtain the same results of the old-style date searches both the search fields must be filled with the same day. 2- Text search operator choice. AFFECTED FIELDS. FULL VERSION : Text, Phone, Html, Textarea and Relate searchfields. DEMO VERSION : Text, Phone and Html searchfield. Click on the "More Detail" icon on the right of the field to see the OPERATOR search option. Select the text operator in the OPERATOR dropdown menu. A choice among various search operators is available : a. The 'LIKE' operator (selected by default): when selected two cases are distinguished: - 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 textfields must contain the text in the searchfield. - If any query wildchar is present in the searchstring, the searchstring is used in the query as is, without adding anything. b. The '=' operator: the result textfields must match exactly the searchfield. c. The 'IN' operator: the result textfields must match exactly one of the element of the list passed as searchfield. The list elements must be separated by a comma or a semicolon. An element may be optionally surrounded by a double quote (") or a left single quote (`). Here are some examples of 'IN' valid searchfields : - John, Mary, David - "Green St."; "Red St."; "White Av." d. The 'RLIKE' operator (only for MySQL database): the result textfields must match the pattern in the searchfield using regular expression MySQL syntax. See the "TIPS 'N' TRICKS" section below for some usage examples. 3- Numeric search operator choice. AFFECTED FIELDS (FULL & DEMO VERSION): Integer, Decimal and Currency. Click on the "More Detail" icon on the right of the field to see the OPERATOR search option. Select the numeric operator in the OPERATOR dropdown menu. A choice among the most common (self-explaining) numeric operators is available : '=', '<', '<=', '>', '>=', BETWEEN. All numbers in a numeric search field (currency type included) may have a comma "," or a dot "." as decimal separator, and MUST NOT have any thousand separator. Negative values are also allowed. The BETWEEN operator returns all the values between a lower and a upper bound entered in the searchfield. The BETWEEN searchfield must have the following format : where : and are integer / decimal numbers (digits from 0 to 9 plus "," or "." as decimal separator and minus "-" are allowed) can be a space " " or a semicolon ";" for instance. It must be composed of 1 or more characters out of the above-mentioned bound set (0-9 , . -). Samples of search string for BETWEEN operator are : 1 10 -1.0 ; 10.1 12,44 / 100 If the search string is not formatted as described above or the lower bound is greater than the upper, the clause has no effect on the search query. Rows with non-assigned numeric values are taken equal to 0 in a numeric search (use the EMPTY option described below to separate them from zero values). See the "TIPS 'N' TRICKS" section below for a complete usage example. 4- OR query operator. AFFECTED FIELDS. FULL VERSION : Bool, Date/datetime, Text, Html, Phone, Textarea, Integer, Decimal, Currency, Relate, Enum, Radioenum and Multienum searchfields. DEMO VERSION : Text, Integer, Decimal, Currency, Html, Phone and Parent searchfield. Click on the "More Detail" icon on the right of the field to see the OR search option. If the OR checkbox is checked, the OR clause operator will be used, instead of the classical AND. If there is exactly one field involved in the search, checking the OR checkbox on it has no effect, so two or more fields involved are assumed. 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 AND ... AND ii) When there is exactly one OR field option checked and there are one or more AND conditions, the result query is: OR ( AND ... AND ) 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: ( OR ... OR ) AND AND ... AND 5- NOT query operator. AFFECTED FIELDS. FULL VERSION : Bool, Date/datetime, Text, Html, Phone, Textarea, Integer, Decimal, Currency, Relate, Enum, Radioenum and Multienum searchfields. DEMO VERSION : Text, Integer, Decimal, Currency, Html, Phone and Parent searchfield. If the NOT checkbox is checked, the corresponding condition is denied. 6- Empty-field search (FULL VERSION ONLY). AFFECTED FIELDS: Bool, Date/datetime, Text, Html, Phone, Textarea, Integer, Decimal, Currency, Relate, Enum, Radioenum and Multienum searchfields. Searching for an empty field is not allowed without Enhanced Search : if the searchfield is left empty it's simply ignored in the search query. To search for an empty field using Enhanced Search, simply check the EMPTY checkbox and leave the searchfield blank. Checking the EMPTY checkbox has no effect in two cases: a. The searchfield is not blank. b. The searchfield is the "Only my items" checkbox filter ***************************** TIPS 'N' TRICKS **************************** 1- Searching for not empty values of a field. For the specified field: a. Check the NOT checkbox b. Check the EMPTY checkbox 2- Searching for empty / not empty textfields with trailing spaces trimmed (MySQL DB only): a. Select the 'RLIKE' operator. b. Check the NOT checkbox if search for not empty textfields c. Type in the searchfield the following: ^[ ]*$ 3- Searching for fields containing an element in a list (MySQL DB only). Example : Searching for all Streets and Avenues in the address field. a. Select the 'RLIKE' operator. b. Type in the searchfield 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. 4- Searching for numeric values out of an interval (only for numeric fields). Example : Searching for all the values external to [-10; 100.1] . a. Select the 'BETWEEN' operator. b. Check the NOT checkbox. c. Fill the searchfield with: -10 100.1