Custom Query
From dispage wiki
Custom Query is the ultimate tool for SugarCRM™ Search customization.
SQL queries can be view / changed thanks to a SQL-specific edit Area.
Any portion of the query can be parameterized and valued from a user interface.
Contents |
EditArea
EditArea is a SQL-specific tool to view / edit SugarCRM SQL queries.
Enhanced Search top panel 
Enhanced Search simplifies the users' experience by displaying the "Search" and the "Clear" button at the top of the Advanced Search page.
The two buttons can be hidden by checking the "Hide the Search Buttons in the top panel" option of the Enhanced Search configuration.
Switch to Custom Query
Custom Query Mode can be accessed from SugarCRM Advanced Search Panel by clicking the Switch to Custom Query Mode button of the top panel.
If the checkbox Force to Replace Last Query is checked, the query of the last performed search in the module is viewed from EditArea. Otherwise, it is dumped the SQL Query viewed the last time the Custom Query Mode has been accessed in that module.
Parameter Panel
(FULL Version Only) Custom Query is a powerful tool, which allows to perform any search since it gives SugarCRM Users the access to the SQL query executed by SugarCRM.
However, it does not lend itself to an easy usage for the ones who do not have a basic SQL skill. For this reason, the Parameter Templates has been introduced.
Parameter Templates
A parameter template is a new entity that allows to enter in any SQL query some placeholders where any value can be replaced. The parameter template syntax is simply
<{name_1 : value_1, name_2 : value_2, ..., name_N : value_N}>
If any portion of the query is replaced by a proper parameter template, the corresponding input field is added to the Parameter Panel interface. Then users can enter any values from that input field and finally perform a search with those values.
Parameter templates may be added through the Add-Parameter Panel or by typing them directly into the EditArea. The first method is faster but it can be used only to create a new parameter template, the second can be also used to modify an existing template or to add an advanced custom property
Add-Parameter Panel
A user-friendly interface comes in use to perform the above operations: the procedure is shown in the following example.
Let's suppose you have the query in the EditArea image above and you want to parameterize it so as to be able to search for any "first_name" with an exact match:
1. You have to highlight the value to replace ("a" in this case) from the EditArea
2. Then, fill "Parameter Name" with any Label
3. Finally, click the "Add Parameter to EditArea" button
The value selected in EditArea is replaced with the parameter template
<{id: 1, name: "Name", type: "text", order: 0}>
and the Parameter Panel looks like image below.
Examples of Parameter Queries
In few clicks, a complex Query can be parameterized and then hidden to the SugarCRM End-Users.
Opportunities Custom Mask
The following is an example in the Opportunities Module:
The parameterized query, generated from the Add-Parameter Panel, is:
SELECT
DISTINCT opportunities.id ,
opportunities.name ,
accounts.name account_name,
jtl0.account_id account_id,
opportunities.sales_stage ,
opportunities.amount_usdollar ,
opportunities.currency_id ,
opportunities.date_closed ,
jt1.user_name assigned_user_name ,
jt1.created_by assigned_user_name_owner ,
'Users' assigned_user_name_mod,
opportunities.amount ,
opportunities.assigned_user_id
FROM
opportunities
LEFT JOIN accounts_opportunities jtl0 ON opportunities.id=jtl0.opportunity_id
AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id
AND accounts.deleted=0
AND accounts.deleted=0
LEFT JOIN users jt1 ON jt1.id= opportunities.assigned_user_id
AND jt1.deleted=0
AND jt1.deleted=0
where
((opportunities.name like '<{id: 0, name: "Name", type: "text", required: true, order: 0}>%')
OR (COALESCE(opportunities.amount, 0) > <{id: 4, name: "Amount", type: "int", order: 0, min: 0, max: 100000}> )
AND ( COALESCE(opportunities.sales_stage, '') in ('<{id: 3, name: "Sales Stage", type: "enum", required: true, order: 0, list: "sales_stage_dom", multiple: true, custom:{size: 4} }>') )
AND ( ( opportunities.date_closed >= '<{id: 1, name: "Start Date", type: "date", required: true, order: 0}>'
AND opportunities.date_closed <= '<{id: 2, name: "End Date", type: "date", required: true, order: 0}> 23:59:59' )))
AND opportunities.deleted=0
GROUP BY
opportunities.id
ORDER BY
opportunities.name ASC
How to add a Multiple Dropdown Field
If the field to parameterize is a DropDown field and a multiple selection is required, the procedure is:
1- Select the values in the query excluding the single quotes:
2- Fill the "Parameter Name" field, select the Enum "Parameter Type" and assign the correct Option List
3- Click "Add Parameter to EditArea"
4- Now, to give the field the size of "4" options, manually add the property
custom:{size: 4}
to the field template in the EditArea, so as it becomes
<{id: 3, name: "Sales Stage", type: "enum", required: true, order: 0, list: "sales_stage_dom", multiple: true, custom:{size: 4} }>
5- Update the Parameter Panel clicking the "Update Parameter Panel" button. The result is the Sales Stage field in the image above
How to add an Integer Field
To add a User-defined Enum field, simply apply the procedure of the example above, with the following exceptions:
1- Select Integer instead of Enum as "Parameter Type"
2- Fill the "Min" and "Max" fields
The result is something like the Amount field in the image above
How to add a Custom Dropdown Field
To add a User-defined Enum field, the procedure of the example above can be applied again, with the following exceptions:
1- Select Custom Enum instead of Enum as "Parameter Type"
2- Fill the "Options" field with something like
'red', 'green', 'blue'
Clicking the "Add Parameter to EditArea", the result is something like
Advanced Custom Properties
Parameter Templates syntax includes advanced customization capabilities: it allows to add attributes or events to the field, and even to execute user-defined javascript code after the creation of the field. All the properties described in this section must be manually added the templates from the EditArea.
custom property
custom property allows to add any attribute to the input field as html element. The custom property is a javascript object whose property are assigned (overwriting the existent) to the input field. An example has been given in the above (#How to add a Multiple Dropdown Field) with the size property.
To add a custom property, the following code must be added to any template (after any other property):
, custom : {<property_1: value_1, property_2: value_2, ..., property_N: value_N, >}
For example if you want to color an input field with yellow and you want to bind an alert to the click event, you have to add this string to the template:
, custom:{style:"background-color:yellow;", onclick:"alert('click')"}
ready property
ready property is a custom javascript code that is executed after the input is created.
email check example
To add an email field with an email format check, the following property can be added:
, ready:"$('input[name=customQueryVars[1]]').change(function () {if ($(this).val().search(/^([a-z0-9_\\-]+\\.)*[a-z0-9_\\-]+@([a-z0-9_\\-]+\\.)+[a-z]+$/i) == -1) ESCQuery.customError = 'Error in email format'; else ESCQuery.customError = '';})"
Message in the figure below appears when a wrong email format is entered
