Additional Search Fields
From dispage wiki
Additional Search Fields is a powerful tool to add conditions of every kind, from the simplest to any arbitrary complex or nested, to the SugarCRM™ advanced searches.
The "Additional Search Fields" panel, automatically hidden if no condition has been entered, looks like
Additional Search Fields - details
ASF panel has the aspect of a table, of which each row is a basic search condition, that is a condition on a single field of a single module (for example: search for Account's name starting with 'a' ). User can add / remove conditions using the buttons of the first column, change properties and even group conditions in parenthesis.
ASF conditions are appended to the default Advanced Search panel ones (if any)
Columns of the ASF table are described in details in this section.
First column (with no header caption) is simply an item button container as hinted above. User can add as many conditions as he wants and can delete all of them but the first.
Operators column is divided into two anonymous sub-columns:
1. The first contains the closed parenthesis dropdown (if any open parenthesis is selected). Its usage is described in Open Parenthesis Operator
2. The second holds the following dropdowns:
Main Boolean Operator
The first dropdown on the left is a boolean operator switcher to select a boolean operator between AND and OR that is applied to the entire condition.
Initially there is only one dropdown on the right of the Main Operator: it is set to blank by default.
A condition can have a number of secondary operators which may vary from 0 to virtually unlimited. ASF interface automatically helps users to add / remove secondary operators:
To add a secondary operator simply select a value different from blank: a new dropdown will be appended to the right of the sub-column
To remove a secondary operator, set it to blank and its dropdown will be removed from the sub-column.
The general rule is that ASF interface performs the operations below after any change of the secondary operator dropdowns:
- removes all the blank dropdowns
- appends a new one (with blank selected) to the right of the remainings
Besides the blank value, secondary operators can be set to
NOT Boolean Operator
NOT operator denies the subsequent condition that is:
- the condition in the row where it is placed, if there is no Open Parenthesis Operator on its right.
- if there is an Open Parenthesis Operator on its right, the condition enclosed by it.
These rules may seem tricky: the easiest way to use ASF interface is to read the table as a common query, i.e. from left to right and top to bottom, as reported in The easiest way to read ASF.
Open Parenthesis Operator
More conditions can be grouped by enclosing them in parenthesis: to do so, first select the Open Parenthesis operator in the row corresponding to the first condition to group.
The Operators turns into something like
where a closed parenthesis dropdown has been added after the row. To group more than one condition in parenthesis, set the first closed parenthesis to blank, and automatically the ASF interface will properly set the next closed parenthesis dropdown
The full size image of the ASF may help to understand the logic of the parenthesis
It can be read
Search <any_advanced_search_condition> AND (the Accounts whose name starts with 'a' OR whose related Contacts' firstname starts with 'b')
as explained in The easiest way to read ASF.
This method allows to add arbitrary nested conditions
Selection of Closed Parenthesis may lead to an inconsistent state, that is not permitted by the ASF interface. If so, the following exception is thrown and the parenthesis are restored to a consistent state
- Main Module: the Module whose records must be retrieved.
- Related modules: all the modules having a relationship of any kind with it.
ASF allows to perform searches across the Main Module and any of the Related modules by selecting a value from the dropdown in this column.
As a module is selected, its fields are automatically ajax-loaded in the following Field Name column.
Select the field to be filtered by.
As a field is selected, a list of operators specific for its type are ajax-loaded in the following Field Operator column.
According to the field's type, a list of supported operators to choose from is loaded in this column
As an operator is selected, its specific input field is ajax-loaded in the following Search Value column.
According to the operator selected, a specific input field is loaded in this column
The easiest way to read ASF
Rules in the previous chapter might appear hard to apply in a user friendly interface. However, all becomes easy in everyday usage if the ASF table is read as it were a text (from left to right and from top to bottom).
For example, the following complex query
may be read starting from the top-left corner:
Search <any_advanced_search_condition> AND NOT ( Type of Account is "Analyst" or "Competitor" OR Primary Address State of any related Contact is "Alabama" or "California" ) AND ( Amount of any related Opportunity is greater than 50000 AND NOT ( Account's Name is between 'a' and 'f' AND Account has a related Case created after "01/09/2010" ) )
Search for Deleted Records
A special search can be performed from ASF panel: the search for deleted records.
Since any relation with other modules' records is deleted at a record deletion, the following simple rule must be followed:
No related module must be included in ASF condition if the deleted field in anywhere selected
An example of a search for deleted records is
returning all the Accounts deleted after "09/09/2010"