Composite Field ListView Searches and Exports

Today's guest post comes from Andrew Copley, an experienced developer who knows the ins-and-outs of SugarCRM.

A customization that I often get asked to implement is a composite field ListView search, by which I mean, to search by a field in the Search panel which is not a database field but rather a criterion defined in terms of two or more module database fields.

For example, one could imagine in Opportunities, the classification of an Opportunity as a Priority, where the Sales_Stage=’Negotiation/Revision’ and Next_Step is not defined or is empty. Thus it might be important to treat these cases with some urgency.

What we would like is a field ‘Priority’ that returns only those Opportunities thus defined, on the Search panel. Let's get started!

Add a field ‘Priority’ to Opportunity vardefs. This will allow us to place the field on the Search panel. We can add this field via the Extensions structure provided by SugarCRM in custom/Extension/modules/Opportunities/Ext/Vardefs/add_priority_field.php. If the file doesn’t exist, simply create it. The vardefs definition is relatively simple:

The important entry is ‘source’=>’non-db’, which ensures that on Repair and Rebuild, a new field is not created in the database. A Repair and Rebuild makes the ‘priority’ field available in Studio, from where it can be added to our SearchPanel metadefs.

At this point, you will also need to enter the language string at this point so that when the priority field is displayed throughout SugarCRM, it’ll display as “Priority” and not “LBL_PRIORITY”. To do this, create the file custom/Extension/modules/Opportunities/Ext/Language/en_us.add_priority_field.php and define the label like so:

Now that we’ve added the field and label, it’s time to Repair and Rebuild from the Admin area. This will install the field and label to the Opportunities module.

In Studio, add the new field to your Search Panels for that module, Basic and/or Advanced. The field will appear as a dropdown with 3 values, ‘-blank-‘, ‘yes’, ‘no’. The next step is to interpret or define the search that ‘priority’ represents. For this we need to intercept the standard ListView search mechanism of SugarCRM in a custom view.list.php for the Opportunities module.

If there is not already there, we create the following file custom/modules/Opportunities/views/view.list.php. We are going to extend the default ViewList class in SugarCRM and overwrite the method processSearchForm(). Copy the entire method from include/MVC/View/views/view.list.php and paste as below. We’ll do most of the work in this method. In SugarCRM CE 6.5.10, the method starts at line 224.

Examining the new code, we simply loop round the ‘where_clauses’ array searching for a clause with our priority field and replace the clause with the appropriate SQL clause or its negation,depending on the value supplied. This method can be used for multiple composite field searches, but the awake amongst you will realize that it breaks the default list view export function. In the following steps, we’ll get that working.

When we perform a ListView filter on a non-database field and then try and export those records via the “Select All’ and ‘Export’ list actions, we will receive an error. However if we use ‘Select Page’ or select individual records via their checkboxes and then we export, we are successful. This is because, in the first case, SugarCRM passes a query ‘where’ clause and in the second and third cases a list of Ids to the database. The ‘where’ clause contains our non-db field ‘priority’ and thus the query fails with a sql error because the field doesn’t exist. Unfortunately the code for exporting can’t make use of our definitions in view.list.php for ‘priority’. Thus we, again have to intercept, the where clause and repeat our code.

The sql for the export query is generated in SugarBean.php in the create_export_query() method. We will overwrite this method in our module bean by extending the core bean class ‘Opportunity’. How do we extend a core bean class?

Create the following file and custom/modules/Opportunities/SOCustomOpportunities.php and add the create_export_query() method.

Basically, we decompose the where clause into its constituent parts, only if it doesn’t include ‘opportunities.id’ i.e. if a list of ids are being passed, search for our non-db field and replace it with our definition. Finally we reconstitute the where clause and pass it to the parent method for inclusion in the final query.

Finally we have to tell SugarCRM about the new bean file for Opportunities. Create the following file custom/Extension/application/Ext/Include/Opportunities.php

The last line tells SugarCRM to use original object name so that all existing relationships are preserved between Opportunities and other modules. After doing a repair and rebuild, the extensions will be installed. At this point, the exporting should work completely.