Custom Query

From dispage wiki

Jump to:navigation, search

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

Custom query editarea.png


EditArea is a SQL-specific tool to view / edit SugarCRM SQL queries.


Enhanced Search top panel New-inline.png

Switch custom query2.png


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

Parameter select field.png


2. Then, fill "Parameter Name" with any Label

Add parameter to editarea.png


3. Finally, click the "Add Parameter to EditArea" button


Parameter panel updated.png


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.


Parameter panel helloworld2.png


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:

Parameter panel search.png


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:

Parameter select multifield.png


2- Fill the "Parameter Name" field, select the Enum "Parameter Type" and assign the correct Option List

Add multitemplate to editarea.png


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

Add integer to editarea.png

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'

Add custom enum to editarea.png

Clicking the "Add Parameter to EditArea", the result is something like

Parameter panel custom enum.png



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')"}


Template custom property.png


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

Custom check email example.png

Navigation
dispage support
Toolbox