******************************************************************************* * * Enhanced Search for SugarCRM * * Plugin adding new search capabilities to SugarCRM Advanced Search. * * Developed by Patrizio Gelosi * * Version: 3.0.3 - Date : 10 Jan 2009 * ******************************************************************************* ****************************** INSTALLATION ****************************** Once logged as admin user, install the package using the "Administration -> Module Loader" tool. *********************** UNINSTALLATION / DISABLING *********************** Once logged as admin user, uninstall / disable the package using the "Administration -> Module Loader" tool. ************************* 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 3.0 has fully achieved the goal to enhance Search capabilities up to the SQL query ones by providing new tools that allow the manual editing of queries and the Subquery search through related modules. Enhanced Search is multilanguage, the following languages are currently supported: - English - French - German - Italian - Spanish - (NEW !!) Finnish ********************************** USAGE ********************************* Enhanced Search 3.0 provides simple tools to search records of a module and its related. I - (NEW !!) SUBQUERY SEARCH using Related Search fields. The Subquery Search tool consists of a simple modification of the Related search field. When a related search field Popup window is open from the Advanced Search page, a New "Send All" Button appears at the top of the Record List. The Records in the Popup window can be filtered using the standard SugarCRM search, then all the result records (even the ones not appearing in the page) can be sent, on a query format, to the Advanced Search page by clicking the "Send All" Button. The Relate Search field aspect in the Advanced Search page changes after receiving the query : it turns from a text field into the "Custom Query" label and an Edit Icon appears on the right of the search field. By clicking the Edit Icon THE USER CAN VIEW / EDIT THE SUBQUERY IN A POPUP WINDOW. The Subquery can be viewed / edited in a human-readable form through a dedicated tool with the aid of : - SQL syntax highlight - Query indentation - Search/Replace text capability - Undo/Redo history navigation .. and more After the Subquery modification, changes can be discarded by simply closing the Edit Popup Window or saved by clicking the "Save" Button on the top bar of the Popup Window. The Subquery is linked by default to the Relate Search Field by the "IN" Operator. In the Enhanced Search FULL version, a choice among different Subquery Operators is also available: 1- " IN " (default) By Choosing this operator, the query condition becomes that the Relate Search field must be found IN the Subquery result set. This means that the value of the result set must be one of the values sent from the Popup Window by clicking the "Send All" button, if the Subquery has not been manually changed. This is the only related search option in the Enhanced Search DEMO version and is selected by default. The search clause is something like : ... AND IN ( SELECT FROM ... ) ... 2- "EXISTS " With this option, a records is retrieved if at least one record is retrieved by the Subquery. 3- "" When this option is selected, the Subquery is put in the main query "as is". This can be useful for an advanced usage such as, for instance, counting records of any related module with a query like (SELECT COUNT(...) ... ) = Of course the standard SugarCRM single-record selection capability of the relate field is still supported. SUBQUERY SEARCH CAN BE SAVED/RETRIEVED LIKE ANY OTHER SUGARCRM SEARCH OPTION (FULL version only) II - (NEW !!) EDIT GLOBAL QUERY (FULL version only) Clicking the "Switch to Custom Query Mode" Button at the top of the Advanced Search page, the Advanced Search Screen changes from the Classic Mode to the Custom Query Mode. A tool to view/edit the main search query is available in this Mode, and the query viewing/editing is aided by - SQL syntax highlight - query indentation - search/replace text capability - undo/redo history navigation .. and more The value of the last query searched in Custom Query Mode is kept for further searches. A very useful option permits to replace the query of Custom Query Mode with the last query executed, for instance, in the Classic Mode. To replace the last query, just check the "Force to Replace Last Query" checkbox on the right of the "Switch to Custom Query Mode" Button, and then click the "Switch to Custom Query Mode" Button. CUSTOM QUERIES CAN BE SAVED/RETRIEVED LIKE ANY OTHER SUGARCRM SEARCH OPTION. III - Advanced Search Enhanced Options Other 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 and Datetimecombo 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, Html and Relate 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. e. (NEW !!) The 'BETWEEN' operator : the result textfields must be included (in alphabethical order) between two text field separated by non-text characters, such as blank space, comma or semicolon. For example, if the following search value ab az is searched with the 'BETWEEN' operator, all the fields starting with text from 'ab' to 'az' are retrieved. 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, Datetimecombo, Text, Html, Phone, Textarea, Integer, Decimal, Currency, Relate, Enum, Radioenum and Multienum searchfields. DEMO VERSION : Text, Integer, Decimal, Currency, Html, Relate, 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, Datetimecombo, Text, Html, Phone, Textarea, Integer, Decimal, Currency, Relate, Enum, Radioenum and Multienum searchfields. DEMO VERSION : Text, Integer, Decimal, Currency, Html, Relate, 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, Datetimecombo, 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