Skip navigation
All Places > Developer > Blog > Authors David Ranney

Developer

5 Posts authored by: David Ranney Employee
In our last post we covered the basics of adding a custom chart layout. Today's post will build on that example to cover some of the more advanced configurations that are possible using charts in Sugar.  Like the previous post, this is targeted at a beginner to intermediate skilled Sugar Developer who is interested in building custom charts.

 

Multiple Charts On the Same View

Previously we explored how to display a single chart on a view. Displaying more than a single chart on the view is also very easy too.

 

In order to add a second chart, you may be tempted to create another chart object in the initialize() method but that is not necessarily how Sugar Charts works.  The "chart" property in the view controller is a Chart factory. Chart factories will not affect how your view is rendered unless you do something with them. You can use the same factory's call() method to construct the same style of chart multiple times.

 

Example

 

We will create a custom layout and view exactly the way we did as before in the single chart example.  We will call this new example the "multi-chart-view".

 

Set up the Handlebars template

 

To display multiple charts you need to provide multiple locations for the charts to exist. These locations are represented below by the div and the enclosed svg elements below. It is convenient to allow each chart div to have a unique id.

 

 

 

<div id="example-chart1">

 

    <svg></svg></div>

 

<div id="example-chart2">

 

    <svg></svg></div>

 

<div id="example-chart3">

 

    <svg></svg></div>

 

 

 

Populate data for multiple views

 

In our JavaScript code, we will obviously need to provide data for each chart. Here we create three separate properties on the view controller:  "bluered", "purpleteal", and "yellowgray".  You'll notice that each chart uses the same format as before.

 

 

({

 

...

 

    loadData: function() {

 

        this.bluered = {

 

            data: [

 

                {

 

                    key: "Blue Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 10

 

                        },

 

                        {

 

                            x: 2, y: 9

 

                        },

 

                        {

 

                            x: 3, y: 8

 

                        },

 

                        {

 

                            x: 4, y: 7

 

                        },

 

                        {

 

                            x: 5, y: 6

 

                        },

 

                    ],

 

                    color: "#0000ff"

 

                },

 

                {

 

                    key: "Red Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 1

 

                        },

 

                        {

 

                            x: 2, y: 2

 

                        },

 

                        {

 

                            x: 3, y: 3

 

                        },

 

                        {

 

                            x: 4, y: 4

 

                        },

 

                        {

 

                            x: 5, y: 5

 

                        },

 

                    ],

 

                    color: "#ff0000"

 

                },

 

            ],

 

            properties: {

 

                title: 'First Chart Data'

 

            }

 

        };

 

        this.purpleteal =             {

 

            data: [

 

                {

 

                    key: "Purple Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 10

 

                        },

 

                        {

 

                            x: 2, y: 9

 

                        },

 

                        {

 

                            x: 3, y: 8

 

                        },

 

                        {

 

                            x: 4, y: 7

 

                        },

 

                        {

 

                            x: 5, y: 6

 

                        },

 

                    ],

 

                    color: "#ff00ff"

 

                },

 

                {

 

                    key: "Teal Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 1

 

                        },

 

                        {

 

                            x: 2, y: 2

 

                        },

 

                        {

 

                            x: 3, y: 3

 

                        },

 

                        {

 

                            x: 4, y: 4

 

                        },

 

                        {

 

                            x: 5, y: 5

 

                        },

 

                    ],

 

                    color: "#00ffff"

 

                },

 

            ],

 

            properties: {

 

                title: 'Second Chart Data'

 

            }

 

        };

 

        this.yellowgray = {

 

            data: [

 

                {

 

                    key: "Yellow Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 10

 

                        },

 

                        {

 

                            x: 2, y: 9

 

                        },

 

                        {

 

                            x: 3, y: 8

 

                        },

 

                        {

 

                            x: 4, y: 7

 

                        },

 

                        {

 

                            x: 5, y: 6

 

                        },

 

                    ],

 

                    color: "#ffff00"

 

                },

 

                {

 

                    key: "Gray Stuff",

 

                    values: [

 

                        {

 

                            x: 1, y: 1

 

                        },

 

                        {

 

                            x: 2, y: 2

 

                        },

 

                        {

 

                            x: 3, y: 3

 

                        },

 

                        {

 

                            x: 4, y: 4

 

                        },

 

                        {

 

                            x: 5, y: 5

 

                        },

 

                    ],

 

                    color: "#888888"

 

                },

 

            ],

 

            properties: {

 

                title: 'Third Chart Data'

 

            }

 

        }

 

        this.total = 1;

 

    },

 

...

 

})

 

Call the Chart factory to display each chart

 

Now we make three separate calls to display each of the charts we want to display. You will notice that the unique ids we added to the div elements in the Handlebars template allow us to easily select each chart location. We also pass each a data object that we created above.

 

 

({

 

...

 

    renderChart: function () {

 

        if (!this.isChartReady()) {

 

            return;

 

        }

 

        d3.select(this.el).select('#example-chart1 svg')

 

            .datum(this.bluered)

 

            .transition().duration(500)

 

            .call(this.chart);

 

        d3.select(this.el).select('#example-chart2 svg')

 

            .datum(this.purpleteal)

 

            .transition().duration(500)

 

            .call(this.chart);

 

        d3.select(this.el).select('#example-chart3 svg')

 

            .datum(this.yellowgray)

 

            .transition().duration(500)

 

            .call(this.chart);

 

        this.chart_loaded = _.isFunction(this.chart.update);

 

    }

 

})

 

With this code in place, you can run a Quick Repair & Rebuild and navigate to the following URL.

http://{your sugar server}/#Accounts/layout/multi-chart-layout

 

You should then see each of your charts in the layout:

 

Any Number of Charts from an API Call

 

While we have shown how to get basic charts working, what we have done so far does not reflect the real world implementations very well.  You do not always know how many charts you need to display and you pretty much never are working with static data. Typically you will make a HTTP call to some REST API to provide data to display. Depending on the nature of that data you may want to display a variable number of charts.

 

In this example, we provide an example that makes a call to an API which returns a variable amount of data, therefore requiring a variable number of charts, so we can explore how to handle this.

 

Set up an API to call

 

For our example we will create a toy API for us to call.  It is simplistic in that it returns data in the exact format the LineChart expects.  In the real world you might have to transform the data in JavaScript a bit first. However, this will illustrate the important principles involved.

 

This API will be invoked using a simple HTTP GET to

http://{your sugar server}/rest/v10/Accounts/get_line_chart_data

 

which will return a randomly generated set of data for multiple charts with multiple lines. You can play with the numbers at the top of the method to give you different results.custom/modules/Accounts/clients/base/api/chartInfoAPI.php

 

 

 

<?php if (!defined('sugarEntry') || !sugarEntry) {

 

die('Not A Valid Entry Point'); }

 

require_once 'clients/base/api/ModuleApi.php';

 

class chartInfoAPI extends ModuleApi {

 

public function registerApiRest() {

 

return array(

 

'webhook' => array(

 

                'reqType' => 'GET',

 

                'path' => array('Accounts', 'get_line_chart_data'),

 

                'pathVars' => array('module', 'action'),

 

                'method' => 'getLineChartInfo',

 

                'shortHelp' => 'This endpoint retrieves line chart information in the proper format',

 

                'longHelp' => '',

 

            ),

 

        );

 

    }

 

    /**

 

     * This method generates data for a line chart example and returns it in the following LineChart format:

 

     * array(

 

     *     array(

 

     *         'data' => array(

 

     *             array(

 

     *                 'key' => "Data set the first", // Title of this data set

 

     *                 'values' => array(

 

     *                     array(

 

     *                         'x' => {some number},

 

     *                         'y' => {some other number},

 

     *                     ),

 

     *                     ...

 

     *                 ),

 

     *                 'color' => "#ff0000", // Any color you want, of course

 

     *             ),

 

     *             ...

 

     *         ),

 

     *         'properties' => array(

 

     *             'title' => "Title of this chart",

 

     *         ),

 

     *     ),

 

     *     ...

 

     * )

 

     */

 

    public function getLineChartInfo($api, $args) {

 

        $out_data_arr = array();

 

        $num_charts = 3; // Total number of charts to display

 

        $num_lines = 2; // Number of lines per chart to display

 

        $num_data_points = 5; // Number of data points per line to display

 

        $color_map = array(

 

            'silver',

 

            'gray',

 

            'black',

 

            'red',

 

            'maroon',

 

            'yellow',

 

            'olive',

 

            'lime',

 

            'green',

 

            'aqua',

 

            'teal',

 

            'blue',

 

            'navy',

 

            'fuchsia',

 

            'purple',

 

        );

 

        for ($i = 0; $i < $num_charts; $i++) {             $tmp_chart_arr = array(                 'data' => array(),

 

                'properties' => array(

 

                    'title' => "Chart #" . ($i + 1),

 

                ),

 

            );

 

            for ($j = 0; $j < $num_lines; $j++) {                 // Pick a color for the line                 $line_color = $color_map[rand(0, count($color_map) - 1)];                 $tmp_line_arr = array(                     'key' => ucfirst($line_color) . " Data",

 

                    'values' => array(),

 

                    'color' => $line_color,

 

                );

 

                for ($k = 1; $k <= $num_data_points; $k++) {                     $tmp_data_point = array(                         'x' => $k,

 

                        'y' => rand(0, 10),

 

                    );

 

                    $tmp_line_arr['values'][] = $tmp_data_point;

 

                }

 

                $tmp_chart_arr['data'][] = $tmp_line_arr;

 

            }

 

            $out_data_arr[] = $tmp_chart_arr;

 

        }

 

        return $out_data_arr;

 

    }

 

}

 

Implement the Handlebars template

 

Since we have no idea how many charts we are going to need to display we cannot just set up div and svg elements ahead of time like we did for the previous example. Instead we will just have a generic div into which we'll  add further div and svg elements dynamically within the Javascript below.

 

 

 

<div id="chart-section"></div>

 

 

 

Implement the loadData() method

 

Now we need to actually call our custom API. Here we call the API via Sugar's built in app.api.call() method which takes care of access token management automatically. We take the results from the API call and assign them to the chartDataArr array for use during render. Take note of the assignment to self.total. Here it matters where this happens because it should not be set until the data is available and charts can be rendered.

 

 

({

 

    ...

 

    loadData: function() {

 

        var self = this;

 

        var url = app.api.buildURL('Accounts/get_line_chart_data');

 

        app.api.call('read', url, null, {

 

            success: function (response) {

 

                _.each(response, function (data, key) {

 

                    self.chartDataArr.push(data);

 

                });

 

                if (self.chartDataArr.length > 0) {

 

                    self.total = 1;

 

                } else {

 

                    self.errorMsg = "There is no chart information available";

 

                }

 

                self.render();

 

            },

 

            error: _.bind(function () {

 

                this.errorMsg = "Error encountered retrieving chart information";

 

            }, this)

 

        });

 

    },

 

    ...

 

})

 

Implement the renderChart() method

 

The renderChart() method gets a little more complex here. We iterate through the set of arrays we stored in chartDataArr in the loadData() method, each of which represents a chart to display. First we add a new div and svg element, with a unique identifier, in which to display the chart. Then we make the call to the call() method to actually display the chart to the new elements.

 

 

({

 

...

 

      renderChart: function () {

 

        if (!this.isChartReady()) {

 

            return;

 

        }

 

        var self = this;

 

        var id_val = "";

 

        var selector_str = "";

 

        _.each(this.chartDataArr, function (data, key) {

 

            id_val = 'example_chart' + key;

 

            selector_str = '#' + id_val + ' svg';

 

            $("#chart-section").append('

 

<div id="' + id_val + '"><svg></svg></div>

 

');

 

            d3.select(self.el).select(selector_str)

 

                .datum(data)

 

                .transition().duration(500)

 

                .call(self.chart);

 

        });

 

        this.chart_loaded = _.isFunction(this.chart.update);

 

    }

 

})

 

Here's the JavaScript when you put it all together

 

 

({

 

    plugins: ['Chart'],

 

    className: 'api-chart-view',

 

    chartDataArr: [],

 

    total: 0,

 

    initialize: function (options) {

 

        this._super('initialize', [options]);

 

        this.chart = nv.models.lineChart()

 

            .x(function (d) {

 

                return d.x;

 

            })

 

            .y(function (d) {

 

                return d.y;

 

            })

 

            .showTitle(true)

 

            .tooltips(false);

 

    },

 

    loadData: function() {

 

        var self = this;

 

        var url = app.api.buildURL('Accounts/get_line_chart_data');

 

        app.api.call('read', url, null, {

 

            success: function (response) {

 

                _.each(response, function (data, key) {

 

                    self.chartDataArr.push(data);

 

                });

 

                if (self.chartDataArr.length > 0) {

 

                    self.total = 1;

 

                } else {

 

                    self.errorMsg = "There is no chart information available";

 

                }

 

                self.render();

 

            },

 

            error: _.bind(function () {

 

                this.errorMsg = "Error encountered retrieving chart information";

 

            }, this)

 

        });

 

    },

 

    renderChart: function () {

 

        if (!this.isChartReady()) {

 

            return;

 

        }

 

        var self = this;

 

        var id_val = "";

 

        var selector_str = "";

 

        _.each(this.chartDataArr, function (data, key) {

 

            id_val = 'example_chart' + key;

 

            selector_str = '#' + id_val + ' svg';

 

            $("#chart-section").append('

 

<div id="' + id_val + '"><svg></svg></div>

 

');

 

            d3.select(self.el).select(selector_str)

 

                .datum(data)

 

                .transition().duration(500)

 

                .call(self.chart);

 

        });

 

        this.chart_loaded = _.isFunction(this.chart.update);

 

    }

 

})

 

When everything is in place, do a Quick Repair & Rebuild and go to

http://{your sugar server}/#Accounts/layout/api-chart-layout

 

That should show you something like this

 

Final Thoughts

 

The examples here are very simple. There are many chart types to choose from and many options for each chart type. Hopefully using these tutorials, the resources mentioned above, and some trial and error, you will then be able to build custom charts in your Sugar customizations.

This blog will be the first in a two part series on building Charts components for Sugar 7. This post is targeted at beginner to intermediate Sugar Developers who want to learn how to build their first Chart component.

This post assumes some basic knowledge of Sugar 7 development, Sugar 7 administration, JavaScript, and PHP.  This information should be useful to anyone who has an interest in Sugar 7 development.

The examples in this post were created on an out-of-the-box installation of Sugar Professional 7.8.0.0.  But this technique should work on any on-premise Sugar 7 instance.

 

Introduction

You may have noticed that a number of out of the box dashlets and views contain various fancy charts and visualizations.  This is possible because Sugar has a charting component build into it.  You can make use of this to display charts within your own custom dashlets, views or layouts.

In this post, we will focus on the "LineChart" type. There are other chart types that use different data formats and chart options but the general techniques covered here will work for all chart types.  These examples were implemented in a basic custom view but they will also work within dashlets.

 

 

NVD3 and Sugar's Charts

 

To provide the ability to display charts easily, Sugar uses a highly customized version of NVD3. The basic structure of our version is the same as NVD3 but the details are different so take some time to explore the chart examples in the source code at /sugarcrm/styleguide/content/charts/. Sugar plans to fully migrate to Sucrose Charts which is SugarCRM's official fork of the NVD3 libraries in an upcoming release. It is worth taking some time to explore Sucrose Charts documentation as well. It includes a number of detailed examples of what charts are possible as well as the data models used to produce them.

 

Basic Requirements for a Sugar Chart

 

Within a Sugar view, all charts have the same general requirements. The examples here use the LineChart chart type but these steps apply to any chart you may want to create.

  1. Create the container for your Chart in your Handlebars template
    • Create a DIV element with a child SVG element inside your Handlebars template
  2. Implement the Chart plug-in within your JavaScript controller
    • Include the "Chart" Sidecar plug-in
    • Instantiate the chart model for your view in the initialize() method
    • Collect data in the right format for your chart type
    • Invoke the chart model via the chart call() method

 

The example below will make this clear.

 

A Basic Line Chart Example

 

We can create a custom Sidecar layout under the Accounts module called single-chart-layout which, in turn, includes a custom Sidecar view for Accounts module called single-chart-view. While we are using Accounts module here, this example could be adapted to run on any module.

 

First, we will create our custom layout.custom/modules/Accounts/clients/base/layouts/single-chart-layout/single-chart-layout.php

 

 

 

<?php

 

$viewdefs['Accounts']['base']['layout']['single-chart-layout'] =

 

array( 'type' => 'simple',

 

    'components' => array(

 

        array(

 

            'view' => 'single-chart-view',

 

        ),

 

    ),

 

);

 

 

 

As you can see from the layout, we reference "single-chart-view" so we should create this next.custom/modules/Accounts/clients/base/views/single-chart-view/single-chart-view.php

 

 

 

<?php

 

$viewdefs['Accounts']['base']['view']['single-chart-view'] =

 

array( 'title' => 'Chart example',

 

    'config' => "",

 

);

 

 

 

Now we can set up our chart within the single-chart-view component.

 

Set up your Handlebars template

 

Next, we create our Handlebars template.single-chart-view.hbs

 

 

 

<div class="single-chart-view">

 

<svg></svg></div>

 

 

 

Include the "Chart" plugin

 

When creating single-chart-view.js the first thing to do is to include the "Chart" plugin:

 

 

 

({

 

    plugins: ['Chart'],

 

    className: 'single-chart-view',

 

    chartData: {},

 

    total: 0,

 

    initialize: function (options) {

 

    ...

 

})

 

 

 

Instantiate the chart model for your View in initialize() method

 

The Chart plug-in will rely on the view's "chart" property.  We need to set up that property to point to a chart object.  In our example, we will tell it to point to a Line Chart.  To do that we can call the nv.models.lineChart() method which returns a line chart object.  For the options available for the Line Chart and others, you will need to consult NVD3 and Sucrose Charts documentation listed above.

 

For our first example, we will create a very simple line chart that displays a title.  You will notice that we attach "x" and "y" callbacks to the new Line Chart object.  These methods tell the chart object where to get the X and Y axis data for the line chart.  In our case the "x" callback retrieves from the "widget_points" property and the "y" callback retrieves from the "num_widgets" property. There is nothing magical about those names, of course. I made them deliberately arbitrary to illustrate that they can be anything you want.

 

 

 

({

 

    ...

 

    initialize: function (options) {

 

        this._super('initialize', [options]);

 

        this.chart = nv.models.lineChart()

 

            .x(function (d) {

 

                return d.widget_points;  // We get the X data points from 'widget_points'

 

            })

 

            .y(function (d) {

 

                return d.num_widgets;  // We get the Y data points from 'num_widgets'

 

            })

 

            .showTitle(true)

 

            .tooltips(false);

 

    },

 

    ...

 

})

 

 

 

Populate your data

 

If a loadData() method exists, then it will be executed when the View is rendered. This is when you retrieve data to be used in a chart. If you attach the data object to the View controller then it will be available whenever needed. Here we set it to a property called "chartData".

 

The format of the object is crucial. Use this format to working with Line Charts.

 

 

 

({

 

  ...

 

    loadData: function() {

 

        this.chartData = {

 

                data: [

 

                    {

 

                        key: "Blue Stuff",

 

                        values: [

 

                            {

 

                                widget_points: 1, num_widgets: 10

 

                            },

 

                            {

 

                                widget_points: 2, num_widgets: 9

 

                            },

 

                            {

 

                                widget_points: 3, num_widgets: 8

 

                            },

 

                            {

 

                                widget_points: 4, num_widgets: 7

 

                            },

 

                            {

 

                                widget_points: 5, num_widgets: 6

 

                            },

 

                        ],

 

                        color: "#0000ff"

 

                    },

 

                    {

 

                        key: "Red Stuff",

 

                        values: [

 

                            {

 

                                widget_points: 1, num_widgets: 1

 

                            },

 

                            {

 

                                widget_points: 2, num_widgets: 2

 

                            },

 

                            {

 

                                widget_points: 3, num_widgets: 3

 

                            },

 

                            {

 

                                widget_points: 4, num_widgets: 4

 

                            },

 

                            {

 

                                widget_points: 5, num_widgets: 5

 

                            },

 

                        ],

 

                        color: "#ff0000"

 

                    },

 

                ],

 

                properties: {

 

                    title: 'Example Chart Data'

 

                }

 

            };

 

        this.total = 1;

 

    }

 

...

 

})

 

 

 

You should also notice that at the end of the method is the line:

this.total = 1;

 

When rendering the view, this value is tested to see if data is available prior to rendering chart. With a hard coded example this does not matter much but when you are making an AJAX call for data then it matters since the response returns asynchronously. Other chart types use "total" differently but line charts simply evaluate if it is set to a non-zero value.

 

Invoke the chart model via the chart call() method

 

When rendering, the loadData() method is called and then later the renderChart() method is called, if it is defined. This method is used to draw the chart on the screen. We do this by using the built-in d3 call() method.

 

 

 

({

 

  ...

 

      renderChart: function () {

 

        if (!this.isChartReady()) {

 

            return;

 

        }

 

        d3.select(this.el).select('svg')

 

            .datum(this.chartData)

 

            .transition().duration(500)

 

            .call(this.chart);

 

        this.chart_loaded = _.isFunction(this.chart.update);

 

    }

 

  ...

 

})

 

 

 

This method first calls isChartReady() to see whether the chart is ready to display or not. Recall the this.total value we set in loadData()?  That's what this method is examining, among other things, to figure out whether it is appropriate to now draw the chart.

 

At the end of this method we set this.chart_loaded, indicating that the chart has indeed been drawn.

 

There are several parts to that long call() method chain that are important to understand. First, the string argument to select() is a CSS selector which points to the SVG element in our HTML where the chart will be drawn.

 

Second, the argument to the datum() method is the object we populated in the loadData() method. It is looking for the appropriately formatted data object.

 

Third, we attach whatever features of the d3 object we want. In this case, we set transitions to half a second (500 milliseconds). You can experiment with different values there to see what happens.

 

Finally, we pass the chart property of the view to the call() method. At that point the chart will be drawn.

 

To put it all together, here is the complete JavaScript controller:

 

 

 

({

 

    plugins: ['Chart'],

 

    className: 'single-chart-view',

 

    chartData: {},

 

    total: 0,

 

    initialize: function (options) {

 

        this._super('initialize', [options]);

 

        this.chart = nv.models.lineChart()

 

            .x(function (d) {

 

                return d.widget_points;

 

            })

 

            .y(function (d) {

 

                return d.num_widgets;

 

            })

 

            .showTitle(true)

 

            .tooltips(false);

 

    },

 

    loadData: function() {

 

        this.chartData = {

 

                data: [

 

                    {

 

                        key: "Blue Stuff",

 

                        values: [

 

                            {

 

                                widget_points: 1, num_widgets: 10

 

                            },

 

                            {

 

                                widget_points: 2, num_widgets: 9

 

                            },

 

                            {

 

                                widget_points: 3, num_widgets: 8

 

                            },

 

                            {

 

                                widget_points: 4, num_widgets: 7

 

                            },

 

                            {

 

                                widget_points: 5, num_widgets: 6

 

                            },

 

                        ],

 

                        color: "#0000ff"

 

                    },

 

                    {

 

                        key: "Red Stuff",

 

                        values: [

 

                            {

 

                                widget_points: 1, num_widgets: 1

 

                            },

 

                            {

 

                                widget_points: 2, num_widgets: 2

 

                            },

 

                            {

 

                                widget_points: 3, num_widgets: 3

 

                            },

 

                            {

 

                                widget_points: 4, num_widgets: 4

 

                            },

 

                            {

 

                                widget_points: 5, num_widgets: 5

 

                            },

 

                        ],

 

                        color: "#ff0000"

 

                    },

 

                ],

 

                properties: {

 

                    title: 'Example Chart Data'

 

                }

 

            };

 

        this.total = 1;

 

    },

 

    renderChart: function () {

 

        if (!this.isChartReady()) {

 

            return;

 

        }

 

        d3.select(this.el).select('svg')

 

            .datum(this.chartData)

 

            .transition().duration(500)

 

            .call(this.chart);

 

        this.chart_loaded = _.isFunction(this.chart.update);

 

    }

 

})

 

 

 

Run a Quick Repair & Rebuild when finished. If you put the view in the layout described above, then you can see it by navigating to the following URL.

http://{your sugar server}/#Accounts/layout/single-chart-layout

 

and something like the following will appear:

Look at that beautiful chart!

 

You, of course, are not limited in number of lines, colors that are used, or even to line charts alone. Users love charts, so I recommend spending some time to experiment with Sugar's chart engine to see what they can do for you.

This post is targeted at beginner to intermediate Sugar Developers who want to add a subpanel to a module which returns custom results.

This post assumes some basic knowledge of Sugar 7 development, Sugar 7 administration, PHP, and SQL.  This article will hopefully be useful to those who have done some Sugar 7 development but are not necessarily experts.

The example in this post was created on an out-of-the-box installation of Sugar Professional 7.5.2.1 with the generated demo data.  But this technique should work on any on-premise Sugar 7 instance.

 

An acknowledgement

 

I recently needed to add a subpanel to a module which returned rows matching some unusual criteria.  The requirement for this subpanel didn't match the standard one-to-many or many-to-many relationship with another module that a subpanel usually reflects.  I researched ways to do this and came across an excellent blog post on the subject by Shane Dowling.  Following what he had written I was able to accomplish my task.  This post is an attempt to make this technique accessible to those who are less familiar with Sugar 7 development.

 

Concerning Subpanels

 

When a one-to-many or many-to-many relationship exists between two modules this will typically be indicated by the presence of a subpanel in one or both of the modules.  For example, there is a default one to many relationship between Accounts and Contacts.  As a result, when you bring up an Account record you will see a "Contacts" subpanel which displays every contact associated with that account.  Likewise, when you create a custom one-to-many or many-to-many relationship between two modules in Studio - a similar subpanel will be created for you in the appropriate modules.  That subpanel will then display all the related rows.

 

That is pretty powerful alone but there are times you need something more specialized.  This post will show you a way to create a custom subpanel to display rows which match whatever criteria you need.

 

An Example Scenario

 

Let's say that at your company the technical support folks are also sales people and upper management has issued a mandate that the support engineer needs to make some positive mention of any open opportunity currently in the pipeline.  Yeah, I wouldn't want to work there either, but let's just say that's the case.  A support person could bring up a separate tab in his browser and search for all open opportunities but that's inconvenient.  Instead it would be nice if there were a subpanel on the current case that the support person could look at to see all currently open opportunities.

 

Let's create a custom subpanel in the Cases module which does exactly that.

 

Since you can't accomplish this with a regular custom relationship, it's probably time to use some custom code.

 

Step 1: Create a Custom Relationship

 

This technique involves overriding certain parts of a relationship's functionality.  To do that you first need a relationship to override.  You can create the various files and subpanels yourself, but I find it easier to start by creating the initial relationship through Studio.  Go into Studio > Cases > Relationships and create a custom one-to-many relationship between Cases and Opportunities.

 

The primary module should be the module in which you want the subpanel to appear and the related module should be the module whose rows you want to display.  For our example, Cases is the primary module because we want the subpanel to appear in the Cases record view and Opportunities is the related module because we want the subpanel to display opportunities.

 

Once you've saved and deployed the relationship a new subpanel will appear in the Cases module.  It probably has a weird title, and shows no rows, but hey, it's there which saves us some work.

 

Step 2: Customize the Relationship's Behavior

 

When you created that relationship, among all the other things that Studio did, it also created the file custom/Extension/modules/Cases/Ext/Vardefs/cases_opportunities_1_Cases.php.  This file contains the metadata for the relationship which tells the system how to handle it and probably looks something like this:

 

cases_opportunities_1_Cases.php

<?php

$dictionary["Case"]["fields"]["cases_opportunities_1"] = array (
'name' => 'cases_opportunities_1',
'type' => 'link',
'relationship' => 'cases_opportunities_1',
'source' => 'non-db',
'module' => 'Opportunities',
'bean_name' => 'Opportunity',
'vname' => 'LBL_CASES_OPPORTUNITIES_1_FROM_CASES_TITLE',
'id_name' => 'cases_opportunities_1cases_ida',
'link-type' => 'many',
'side' => 'left',
);

 

To get the behavior we want we need to update this file to point at some custom relationship code we're going to write later. To do this we'll make use of two fields which do not appear in generated code, namely "link_file" and "link_class". We are also going to change the "relationship" value to a blank:

 

cases_opportunities_1_Cases.php

<?php

$dictionary["Case"]["fields"]["cases_opportunities_1"] = array (
'name' => 'cases_opportunities_1',
'type' => 'link',
// Blank out relationship
'relationship' => '',
'source' => 'non-db',
'module' => 'Opportunities',
'bean_name' => 'Opportunity',
'vname' => 'LBL_CASES_OPPORTUNITIES_1_FROM_CASES_TITLE',
'id_name' => 'cases_opportunities_1cases_ida',
'link-type' => 'many',
'side' => 'left',
// New fields
'link_file' => "custom/modules/Cases/OpportunitiesForCaseLink.php",
'link_class' => "OpportunitiesForCaseLink",
);

 

These changes tell Sugar 7 to use the OpportunitiesForCaseLink class to handle the relationship you just created rather than the regular code it would normally use. Notice that the path in "link_file" is relative to the Sugar directory.

 

Step 3: Customize the Subpanel's Title

 

By default the subpanel is going to have a generic title like "Opportunities" which isn't specific enough.  So we need to change it. First, create the file custom/Extension/modules/Cases/Ext/Language/en_us.opportunities_for_cases_subpanel.php:

 

en_us.opportunities_for_cases_subpanel.php

<?php

$mod_strings['LBL_OPPORTUNITIES_FOR_CASE_ACCOUNT_SUBPANEL_TITLE'] = 'All Open Opportunities';

 

Then re-edit custom/Extension/modules/Cases/Ext/Vardefs/cases_opportunities_1_Cases.php and change the 'vname' parameter to have the same value as the index you just created in $mod_strings.

 

cases_opportunities_1_Cases.php

<?php

$dictionary["Case"]["fields"]["cases_opportunities_1"] = array (
'name' => 'cases_opportunities_1',
'type' => 'link',
'relationship' => '',
'source' => 'non-db',
'module' => 'Opportunities',
'bean_name' => 'Opportunity',
// Update the title
'vname' => 'LBL_OPPORTUNITIES_FOR_CASE_ACCOUNT_SUBPANEL_TITLE',
'id_name' => 'cases_opportunities_1cases_ida',
'link-type' => 'many',
'side' => 'left',
'link_file' => "custom/modules/Cases/OpportunitiesForCaseLink.php",
'link_class' => "OpportunitiesForCaseLink",
);

 

Now, to make it actually show up correctly, edit custom/Extension/modules/Cases/Ext/clients/base/layouts/subpanels/cases_opportunities_1_Cases.php which was created automatically by Studio previously

 

cases_opportunities_1_Cases.php

<?php

$viewdefs['Cases']['base']['layout']['subpanels']['components'][] = array (
'layout' => 'subpanel',
// Update label with same index that you created in $mod_strings previously
'label' => 'LBL_OPPORTUNITIES_FOR_CASE_ACCOUNT_SUBPANEL_TITLE',
'context' =>
array (
'link' => 'cases_opportunities_1',
    ),
);

 

Step 4: Override the Relationship Code

 

Now we have to actually write the custom code that will handle the relationship. Normally Sugar will use an internal class called Link2 to handle the custom relationship. We're going to extend that class and override certain methods so that it implements the custom behavior we want.

 

First, let's create a basic version of this file in the same location we specified in the "link_file" value in the relationship metadata above, namely custom/modules/Cases/OpportunitiesForCaseLink.php. Likewise, note that the class name in this file matches the "link_class" value as well:

 

OpportunitiesForCaseLink.php

<?php

class OpportunitiesForCaseLink extends Link2 {
protected $db;
public function __construct($linkName, $bean, $linkDef = false)
    {
$this->focus = $bean;
$this->name = $linkName;
$this->db = DBManagerFactory::getInstance();
if (empty($linkDef)) {
$this->def = $bean->field_defs[$linkName];
        } else {
$this->def = $linkDef;
        }
    }
/**
     * Returns false if no relationship was found for this link
     *
     * @return bool
*/
public function loadedSuccesfully()
    {
// this link always loads successfully
return true;
    }
/**
     * @see Link2::getRelatedModuleName()
*/
public function getRelatedModuleName()
    {
// Be sure the plural form of the related module is returned here
return 'Opportunities';
    }
/**
     * @see Link2::getRelatedModuleLinkName()
*/
public function getRelatedModuleLinkName()
    {
// this is one-side link, other side (Emails) won't have the link
return false;
    }
/**
     * @see Link2::getType()
*/
public function getType()
    {
return "many";
    }
/**
     * @see Link2::getSide()
*/
public function getSide()
    {
return REL_LHS;
    }
/**
     * @see Link2::is_self_relationship()
*/
public function is_self_relationship()
    {
return false;
    }
/**
     * @see Link2::isParentRelationship()
*/
public function isParentRelationship()
    {
return false;
    }
/**
     * If there are any relationship fields, we need to figure out the mapping
     * from the relationship fields to the
     * fields in the module vardefs
*/
public function getRelationshipFieldMapping(SugarBean $seed = null)
    {
return array();
    }
/**
     * use this function to create link between 2 objects
*/
public function add($rel_keys, $additional_values = array())
    {
// cannot add to this relationship as it is implicit
return false;
    }
/**
     * Marks the relationship deleted for this given record pair.
*/
public function delete($id, $related_id = '')
    {
// cannot delete from this relationship as it is implicit
return false;
    }
/**
     *
     * @see Link2::buildJoinSugarQuery()
*/
public function buildJoinSugarQuery($sugar_query, $options = array())
    {
$joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
$jta = 'cases_opportunities_1';
if (!empty($options['joinTableAlias'])) {
$jta = $joinParams['alias'] = $options['joinTableAlias'];
        }
$sugar_query->joinRaw($this->getCustomJoin($options), $joinParams);

return $sugar_query->join[$jta];
    }

/**
     * This is the method where you would put your custom query against the related
     * module.  All this method does is to create an SQL string that adds a JOIN to
     * a table subquery.  The idea is to use a regular old SQL SELECT to select all
     * the ids which fit the criteria you're interested in.  The main query then joins
     * to this subquery thus limiting the results to only what you want.
*/
protected function getCustomJoin($params = array())
    {
$sql = " JOIN (";
// Give me every opportunity
$sql .= <<<MYCUSTOMQUERY
SELECT
    opportunities.id AS my_custom_id
    FROM
        opportunities
MYCUSTOMQUERY;

$sql .= ") opportunities_to_show ON opportunities_to_show.my_custom_id = opportunities.id";
return $sql;
    }
}

 

Once you put that in place, if you run a Quick Repair & Rebuild and then bring up a single case you should see the new "All Open Opportunities" subpanel and it should show all opportunities in the system.  We'll get into the details in a bit but first let's break down this large class a bit to make it clear how it works:

 

OpportunitiesForCaseLink.php

class OpportunitiesForCaseLink extends Link2 {

 

Note that we're extending the Link2 class which is built into Sugar. As stated above that's the class that handles these sorts of custom relationships. The class name must match the "link_class" value you provide in the relationship metadata.

 

OpportunitiesForCaseLink.php

    /**
     * @see Link2::getRelatedModuleName()
     */
    public function getRelatedModuleName()
    {
        // Be sure the plural form of the related module is returned here
        return 'Opportunities';
    }

 

This overrides a method from Link2. It returns the plural form of the related module name. Since this extension is just for the use of our special relationship, we can simply hardcode the value.

 

OpportunitiesForCaseLink.php

    /**
     * use this function to create link between 2 objects
     */
    public function add($rel_keys, $additional_values = array())
    {
        // cannot add to this relationship as it is implicit
        return false;
    }

 

You don't have to change anything with this method. However it does show something important. The "+" button you see on the right side of the subpanel is used to create new related records. This functionality won't work once we've customized the Link2 class.

Generally it is a best practice to use custom subpanels in a read-only capacity.

 

Disabling the buttons on the subpanel can be accomplished by adding the 'disabled' CSS class to each button element.

 

OpportunitiesForCaseLink.php

    /**
     *
     * @see Link2::buildJoinSugarQuery()
     */
    public function buildJoinSugarQuery($sugar_query, $options = array())
    {
        $joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
        $jta = 'cases_opportunities_1';
        if (!empty($options['joinTableAlias'])) {
            $jta = $joinParams['alias'] = $options['joinTableAlias'];
        }
        $sugar_query->joinRaw($this->getCustomJoin($options), $joinParams);

        return $sugar_query->join[$jta];
    }

 

The above method gets handed a SugarQuery object which will ultimately get executed to return the rows which are displayed in our custom subpanel. If you're unfamiliar with SugarQuery then you should get familiar with it because it's awesome. There is a very helpful blog entry on SugarQuery which is a great place to start. The SugarQuery object coming into this method is a basic query against the related module (in our case opportunities) which returns the appropriate columns. We want to take that basic query and change it so that it only returns the rows we care about. We've implemented this behavior in joinRaw().

 

OpportunitiesForCaseLink.php

    /**
     * This is the method where you would put your custom query against the related
     * module.  All this method does is to create an SQL string that adds a JOIN to
     * a table subquery.  The idea is to use a regular old SQL SELECT to select all
     * the ids which fit the criteria you're interested in.  The main query then joins
     * to this subquery thus limiting the results to only what you want.
     */
    protected function getCustomJoin($params = array())
    {
        $sql = " JOIN (";

        // Give me every opportunity for the account associated with the current case which is not closed
        $sql .= <<<MYCUSTOMQUERY
SELECT
opportunities.id AS my_custom_id
FROM
opportunities
MYCUSTOMQUERY;

        $sql .= ") opportunities_to_show ON opportunities_to_show.my_custom_id = opportunities.id";
return $sql;
    }

 

Note that joinRaw() is passed the return value of this method.  This method generates a SQL string which is a JOIN to a table subquery. The SugarQuery object above will be joined to the results of this query, so only opportunities with an id matching the results of this query will show up in the subpanel.

 

Step 5: Modify the query as desired

 

Right now this will return every opportunity in the system so we need to change it to match our custom criteria.  The requirements given to us state that the subpanel should show every open opportunity in the system.  That means we need to adjust our query.

 

As seen earlier we are handed a SugarQuery object which will later be executed to give us our results.  That being the case, let's simply alter the SugarQuery object to fit the requirements.

 

To restrict the results to only open opportunities we use the where() method of the SugarQuery object along with the notIn() method.

 

OpportunitiesForCaseLink.php

    /**
     *
     * @see Link2::buildJoinSugarQuery()
     */
    public function buildJoinSugarQuery($sugar_query, $options = array())
    {
        $joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
        $jta = 'cases_opportunities_1';
        if (!empty($options['joinTableAlias'])) {
            $jta = $joinParams['alias'] = $options['joinTableAlias'];
        }
        $sugar_query->joinRaw($this->getCustomJoin($options), $joinParams);

        $closed_stages_arr = array(
            "Closed Won",
            "Closed Lost",
        );
        $sugar_query->where()
            ->notIn("sales_stage", $closed_stages_arr);

        return $sugar_query->join[$jta];
    }

 

You can use other parts of SugarQuery to affect the results as well.  Review time is coming up, so let's exceed expectations by ordering by the expected closed date showing the most recent first:

 

OpportunitiesForCaseLink.php

    /**
     *
     * @see Link2::buildJoinSugarQuery()
     */
    public function buildJoinSugarQuery($sugar_query, $options = array())
    {
        $joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
        $jta = 'cases_opportunities_1';
        if (!empty($options['joinTableAlias'])) {
            $jta = $joinParams['alias'] = $options['joinTableAlias'];
        }
        $sugar_query->joinRaw($this->getCustomJoin($options), $joinParams);

        $closed_stages_arr = array(
            "Closed Won",
            "Closed Lost",
        );
        $sugar_query->where()
            ->notIn("sales_stage", $closed_stages_arr);

        $sugar_query->orderBy("date_closed", "DESC");

        return $sugar_query->join[$jta];
    }

 

See what I did there?  I'm telling you SugarQuery is your friend.  Learn about it.  You won't regret it.

 

Step 5 (Part 2): Using Raw SQL

 

Let's say that after a few weeks, and after enough complaints from the support organization, management has finally realized that having support people comment on random open opportunities is silly.  They have decided that support folks should only comment on any open opportunities for the account associated with the current case.

 

While this is a boon to support, it kind of makes your life more difficult.  The account associated with a case is a simple field in the cases table (account_id).  However, the association between an account and an opportunity is stored in a separate table (accounts_opportunities) so it's not obvious how you'd handle that in SugarQuery.  There's likely a way, but the deadline is pressing and you're not that good at SugarQuery yet.

 

The good news is that you're not limited to using SugarQuery.  As you may have guessed the purpose of the getCustomJoin() method is to provide raw SQL we can inject into our SugarQuery object.  That means you can use whatever crazy SQL you need to fine tune the results of your query.

You should use SugarQuery abstraction whenever possible and descend into raw SQL only when absolutely necessary.
SugarQuery takes care of all sorts of background elements of a query such as team membership, whether or not rows are deleted, etc.  It exists to make your life easier, so you should use it if you can.  But when you cannot, the raw SQL option is available to you.

 

In this case we want to modify the results to only show us open opportunities for the account associated with the current case.  So let's change the method to the following:

 

OpportunitiesForCaseLink.php

    /**
     * This is the method where you would put your custom query against the related
     * module.  All this method does is to create an SQL string that adds a JOIN to
     * a table subquery.  The idea is to use a regular old SQL SELECT to select all
     * the ids which fit the criteria you're interested in.  The main query then joins
     * to this subquery thus limiting the results to only what you want.
     */
    protected function getCustomJoin($params = array())
    {
        $sql = " JOIN (";
        // Give me every opportunity for the account associated with the current case which is not closed
        $sql .= <<<MYCUSTOMQUERY
SELECT
opportunities.id AS my_custom_id
FROM
opportunities
JOIN accounts_opportunities
ON opportunities.id = accounts_opportunities.opportunity_id
WHERE
accounts_opportunities.deleted = 0
AND accounts_opportunities.account_id = '{$this->focus->account_id}'
MYCUSTOMQUERY;

        $sql .= ") opportunities_to_show ON opportunities_to_show.my_custom_id = opportunities.id";
return $sql;
    }

 

There are a few things to take note of in this code:

 

First, notice how we now use a combination of SugarQuery modifications and raw SQL.  This means you can do as much as possible through SugarQuery and relegate only the really weird stuff to raw SQL.  Do you see that "accounts_opportunities.deleted = 0" bit?  That's the sort of annoying detail SugarQuery takes care of for you.

 

Second, it uses $this->focus->account_id. $this->focus is a SugarBean object for the current Case. The Link2 class makes this available to you so you can use field values from current Case in building your query.

 

Third, notice the SQL we surround our query with. It joins the results of our custom query back to the root query provided in SugarQuery object. A good way to visualize what's going on here is to think about it this way:

 

Here's the plain vanilla query that comes into buildJoinSugarQuery():

 

gistfile1.sql

SELECT
opportunities.id,
-- Other columns appropriate to subpanel
FROM
        opportunities
-- Other incidental joins that Sugar needs that you don't care about
WHERE
-- Various conditions including, for example, deleted = 0

 

You then add to that query via getCustomJoin():

 

gistfile1.sql

SELECT
opportunities.id,
-- Other columns appropriate to subpanel
FROM
        opportunities
-- Other incidental joins that Sugar needs that you don't care about

JOIN (
-- YOUR CUSTOM QUERY THAT PRODUCES A LIST OF OPPORTUNITY IDs
-- WITH THE COLUMN ALIAS OF "my_custom_id"
        ) opportunities_to_show ON opportunities_to_show.my_custom_id = opportunities.id

WHERE
-- Various conditions including, for example, deleted = 0
ORDER BY
-- That ordering we imposed earlier via SugarQuery::orderBy()

 

When you apply this technique to your particular situation be careful with the SQL that follows the close parenthesis as it must match the context of the root query.  In our example I deliberately aliased opportunities.id to my_custom_id in the custom SQL to illustrate this need.

 

The custom SELECT statement you provide can get as long and goofy as you want, but keep in mind database performance impact and potential SQL dialect differences when writing raw SQL. The SELECT I wrote for the custom subpanel in my real world project involved four different SELECTs UNIONed together (don't ask). Just remember that this query must return only the set of record ids (opportunity ids in this case) that match rows you want to populate the subpanel.

 



 

To get it all to work you need to do another Quick Repair and Rebuild. Once that's done, bringing up a specific case will reveal the subpanel and it should now only show opportunities which are open and under the same account as the current case.

 

I also renamed the title of the subpanel to better match what it shows, which is always a good practice.  To do this just change the text as we did in Step 3 above.

 

You're Done!

 

At this point you now have a working subpanel which shows only those rows which match certain criteria you need.  Also, since we can use raw SQL you have a lot of power at your disposal to customize that result set.

 

How To Do Debug Your SQL

 

There's a great deal of flexibility available in this technique.  You can add whatever clauses you want to the table subquery.  You can also use SugarQuery functionality to alter the behavior of the SugarQuery object returned by buildJoinSugarQuery().  And you can combine the two as you wish.  As a result, when you get unexpected results it can be hard to make out exactly what went wrong.  While I was working on my particular subpanel I found it helpful to use SugarQuery's compileSql() (Note: deprecated in Sugar 7.7.1 and removed in 7.9.0. Use getSQL() and getParameters() instead) method to print out the actual SQL that was being generated.  That would let me know what was going on.  For example:

 

OpportunitiesForCaseLink.php

    /**
     *
     * @see Link2::buildJoinSugarQuery()
     */
    public function buildJoinSugarQuery($sugar_query, $options = array())
    {
        $joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
        $jta = 'cases_opportunities_1';
        if (!empty($options['joinTableAlias'])) {
            $jta = $joinParams['alias'] = $options['joinTableAlias'];
        }
        $sugar_query->joinRaw($this->getCustomJoin($options), $joinParams);
        $sugar_query->join[$jta];

$GLOBALS['log']->debug($sugar_query->compileSql());

        return $sugar_query;
    }

 

That will dump the (sometimes very long) SQL string into sugarcrm.log (or whatever location you previously set for your Sugar log) if your log level is Debug.  Once you format it and sort through it, it should reveal why it's doing what it's doing.  If nothing shows up there it's always helpful to look at your Apache server log to see if there are additional errors in there.

This article is aimed at beginning to intermediate SugarCRM developers who want to customize views in SugarCRM version 7.

This does not go into detail about all the ins and out of creating custom views, changing metadata and handlebars, etc.  This article merely points out a single technique for extending the JavaScript for an out of the box view to a custom view.  This technique also applies to layouts, but this article will concentrate on views.

This article assumes some knowledge of JavaScript and PHP.

 

Creating a Custom View From an Out of the Box View

When you create a custom view in SugarCRM 7 you create a subdirectory under

{SugarCRM base dir}/custom/clients/base/views/{new view name}

or

{SugarCRM base dir}/custom/modules/{module name}/clients/base/views/{new view name}

with the various files within it named for the view name.  An out of the box view is one you can find under

{SugarCRM base dir}/clients/base/views/

If you wanted to override one of these out of the box views you would simply create a custom view with the same name.  You can tweak the functionality of an out of the box view by simply copying its source files to your custom view and then customizing them from there.  Let's say, for example, that you wanted to alter the behavior of the "record" view for a given module.  You can simply copy the out of the box record view locally

cd {SugarCRM base dir}

 

cp -r clients/base/views/record custom/modules/{module name}/clients/base/views/record

You'll then need to alter the metadata file (in this case record.php) to reflect the new location

<?php

 

....

 

$module_name = "my_ModuleName";

 

$viewdefs[$module_name]['base']['view']['record'] = array(

 

    'buttons' => array(

 

    array(

 

        'type' => 'button',

 

        'name' => 'cancel_button',

 

        'label' => 'LBL_CANCEL_BUTTON_LABEL',

 

        'css_class' => 'btn-invisible btn-link',

 

    ),

 

    ...

If your customizations involve JavaScript changes you'll want to use the un-minified version of the JavaScript file to work against.  You can copy it from the jssource directory.  In this case you'd run the following command:

cp jssource/src_files/clients/base/views/record/record.js custom/modules/{module name}/clients/base/views/record

At this point you can change the various local files to your heart's content, and anytime the "record" view is used within your module, your custom view will be used instead.

Like all changes to views and layouts, you'll need to do a Repair and Rebuild before you see your changes take effect.  When you change the JavaScript file you'll also need to clear your browser cache.

 

The Problem With Overriding An Out of the Box View

That's wonderful until you upgrade.  The problem is that when you upgrade, the out of the box record view might receive changes but your local version of the "record" view won't.  Over time your custom view will become more and more out of step with the out of the box view.  It could even result in your view breaking eventually.  This isn't much of a problem for the metadata file since that's not likely to change much from version to version.  The JavaScript file, however, can change a lot.  It would be nice if there were a way to get the benefit of the improved JavaScript while still keeping a customized version of the view.

Happily this is possible by extending the JavaScript file rather than completely overriding it.

To extend the JavaScript file you simply use JavaScript's "extendsFrom" functionality.  In our example, to extend the record view the record.js file would look something like this:

({

 

extendsFrom: 'RecordView',

 

initialize: function(options) {

 

    console.log("We are using my customized record view, Yay!");

 

    this._super('initialize', [options]);

 

}

 

})

 

This JavaScript file will work exactly like the original except that it will now write a message to the JavaScript console whenever the view is used.

 

The tricky part of this is figuring out what to put in the "extendsFrom" part.  The name follows the form {Module}{Layout|View}.  If you're extending the JavaScript for a view the {Layout|View} part will, of course, be "View".  For the {Module} part you take the name of the view and capitalize it.  If the name has dashes in it, remove the dashes and uppercase the following word.  For example, if you were extending the JavaScript for the "filter-module-dropdown" view, the module part would turn into "FilterModuleDropdown" and the entire class to extend would become "FilterModuleDropdownView".

 

If you're ever unsure about what the class may be for the view or layout you're extending, you can run one of the following commands in the JavaScript console and look at the results.  They should contain the names of all the view and layout classes recognized by the application:

SUGAR.App.view.views;

 

SUGAR.App.view.layouts;

 

Calling the "_super" method within an overridden method is important to get the functionality of the parent method.  You can leave it out, but then your local method must do everything the parent method did.

 

This technique also works if you create a custom view of a different name based on an out of the box view.  There's no rule saying that what follows "extendsFrom" must match the name of the local view.  It's merely the JavaScript class you're inheriting from.

 

Since you inherit from the parent JavaScript class rather than completely overriding it, and use the _super() method within overridden methods, when you update you'll automatically get the improved functionality of the base class.

 

A Quick Caveat

 

When you extend the JavaScript for a view in this manner there's always the risk that the changes made in a given update will clash with the customizations you've made in your local view.  It depends on the nature of the customizations you choose to make.  There's no good way to completely guard against that other than never touching the Javascript at all.  Extending a view this way does minimize this risk because you override only what you must rather than the entire JavaScript file.  You should, however, remain aware of the customizations you make and test them thoroughly when you update before going live.

This article is aimed at beginning to intermediate SugarCRM developers who want to create custom filters in SugarCRM version 7.

This does not cover the custom filters that users can create for themselves.  Those are stored on a per-user basis and are not available to all users.  Instead, this document will cover creating filters that all users of the system will have access to.

This article assumes some knowledge of SQL, PHP, Unix, and general web stuff.

 

About Filters

 

In Sugar 7 parlance a filter is used to control what rows of data are displayed to the user in a given view.  Sugar has a number of core filters which come with Sugar out of the box.  When you go to a page with a layout that makes use of the filter view and click on the “Filter” pulldown menu you’ll see them displayed as options:

When you choose one of those filters you’ll notice that the rows that show up in the list view change.  The filter supplies certain characteristics the data must meet before the row is displayed in the view.

Filters can be extremely useful when you want to limit what the user sees and not just present every row in the table to the user.

 

How to create a very basic filter

The core filters are very convenient since you don’t have to do anything to make them show up. However, it’s not unusual to have a need for a more customized filter that sets criteria specific to your individual needs.  No core filter will be able to supply that.  Fortunately, SugarCRM 7 provides a way to  create such a filter without having to descend into the core functionality of the product and hack something into place.

So let’s dive in and create a very simple filter.

For the purposes of this document I have gone into Studio and created a module called “Random Custom Stuff” with a module name of “flt_RandomCustom”.  I know that’s a weird name, but it’s how we do things where I work, so I went with it.  I also added a custom field to the module to hold a random number.  This created both the “flt_randomcustom” and “flt_randomcustom_cstm” tables (among others) in the database.  That will be more interesting later.

 

When I go to

http://{my instance}/#flt_RandomCustom

 

I see the following:

You see all the rows for this module displayed because the default filter is “All Random Custom Stuff” which simply shows everything.  We’ll change that by creating a new filter.

 

Let’s create a filter to only show rows which have a name of “Blorg” and call it the “Blorg Only” filter.  Let’s say that you installed SugarCRM in

/var/www/sugarcrm

 

The first thing to do would be to create a directory for your new filter.  Like other components such as layouts and views, you create a custom filter by adding a directory and files under the

 

"custom/" directory in the SugarCRM instance.  We want this filter to be available only to this module so we’ll create the directory:

/var/www/sugarcrm/custom/modules/flt_RandomCustom/clients/base/filters/blorgonly

At first glance that looks like a crazy path, but if you’ve done any other customization of SugarCRM 7 you’ll recognize that this fits with layouts, views, and other customizations.

 

In that directory create a file "blorgonly.php" which contains the following text:

<?php

 

$module_name = "flt_RandomCustom";

 

$viewdefs[$module_name]['base']['filter']['blorgonly'] = array(

 

    'create'               => false,

 

    'filters'              => array(

 

        array(

 

            'id'                => 'blorgonly',

 

            'name'              => 'LBL_BLORG_ONLY_FILTER',

 

            'filter_definition' => array(

 

                array(

 

                    'name' => 'Blorg',

 

                ),

 

            ),

 

            'editable'          => false,        ),    ),

 

);

It’s very important that the name of the PHP file match the name of the directory which needs to match the name of the filter.  You’ll notice in the code above that we specify the name of the filter in two places, once as the element of the “filter” viewdefs array, and again as the “id” of the filter.  Both are important.  For the “name” we’ve put in a placeholder for a label string.  Don’t worry, we’ll get to that in a minute.

The real action takes place in the “filter_definition” array.  You can see that we have a single element that says that the “name” has to be “Blorg”.  The “name” field of the array under “filter_definition” is referring to the name of the actual column in the underlying table (flt_randomcustom in this case).  And, just to make things confusing, the name of that column is “name”.

To make it actually work you have to go to the Admin menu, click on Repair, and then click on “Quick Repair and Rebuild”.

 

When you reload http://{my instance}/#flt_RandomCustom and click on the filter pulldown you should see something like this:

When you click on the new filter you should see only the row with “Blorg” as the name appear.  My friend, you’ve just created your first custom filter in SugarCRM 7.  Savor the moment.

Now, if you don’t want a reputation for shoddy work, you’re going to want to make that label look like something real.  To do that we have to set up a string for it.  To do that we create another PHP file:

/var/www/sugarcrm/custom/Extension/modules/flt_RandomCustom/Ext/Language/en_us.RCFilt.php

This filename has to start with “en_us.” and end with ".php" but it can be named anything within that.  The contents of that file should be something like:

<?php

 

$mod_strings['LBL_BLORG_ONLY_FILTER'] = 'Only Blorg Rows';

 

Once you have that file in place, do the “Quick Repair and Rebuild” again.  Reloading the page and clicking on the filter pulldown should now look like this:

And there you have it, a simple custom filter users can employ within a custom module.

 

Making more complex filters

Filters which restrict rows to those matching a single value are all fine and good, you say, but not much use in the real world.  Reality demands much more complex criteria for filters.  I completely agree.  Let’s see if we can construct a less trivial filter.

 

Constructing a complex filter is exactly the same as the simple filter above except that the "filter_definition" array contents are more complicated.

 

Here’s where knowledge of SQL comes in handy.  The way that the filter_definition array works is not unlike the WHERE clause of a SELECT statement.  Let’s consider this example:

'filter_definition' => array(

 

    array(

 

        'random_number_c' => '17',

 

        'date_entered' => '2014-01-22 13:21:01',

 

    ),

 

),

By default, things that are grouped together within arrays have an implied AND relationship.  In this case the filter will only show rows with a random number of 17 and the given date_entered value.  By the way, did you notice that the random number field ends with “_c”?  That’s right, you can specify custom fields in these filters as well.  They just get treated like the columns of the main table.

Okay, that’s slightly cooler, but how would you do an OR instead of an AND?

The filter mechanism has these command directives which start with $ that you can use.  In this case we want to make use of the “$or” directive:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array('random_number_c' => '17'),

 

            array('random_number_c' => '827'),

 

        ),

 

    ),

 

),

This array groups together two conditions with the $or directive which means that rows with either value for that column should be shown.  You’ve probably noticed that the two conditions need to be wrapped in arrays of their own within the $or array.  That’s important.  Bad stuff happens if you forget to do that.  Also, don’t make the mistake of using double-quotes or PHP will try to interpret the $or as a variable and fail.

So now let’s try to construct a really complex filter.

Let’s say you need to create a filter that’s the equivalent of this WHERE clause:

WHERE

 

(

 

    date_entered = "2014-03-01 19:54:47"

 

    OR (

 

        random_number_c > 0

 

        AND random_number_c < 40

 

    )

 

)

 

AND (

 

    name LIKE “A%"

 

    OR name LIKE "B%"

 

    OR name LIKE "R%"

 

)

Wow.  Okay, so it’s kind of tough to just rattle off the PHP necessary to do this, so let’s break it down logically.  First, we have two big conditions AND’ed together which implies:

'filter_definition' => array(

 

    array(

 

        //The thing with ‘date_entered’ and other stuff

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

 

The second condition is a little more straightforward, so let’s handle that one first.  To reproduce the behavior with the LIKE’s above, SugarCRM filters have a $starts directive which tests for whether the value of a string starts with the given value.  So we would use it as follows:

'filter_definition' => array(

 

    array(

 

        //The thing with the ‘date_entered’ and other stuff

 

    ),

 

    array(

 

        '$or' => array(

 

            array(

 

                'name' => array(

 

                    '$starts' => 'A',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'B',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'R',

 

                ),

 

            ),

 

        ),

 

    ),

 

),

While that may look intimidating, actually it makes sense.  It’s just three $starts conditions OR’ed together.

Now let’s attack the first bit.  What if all we had to do was do the two tests for the random number?  It would look like this:

'filter_definition' => array(

 

    array(

 

        array(

 

            'random_number_c' => array(

 

                '$lt' => '40',

 

            ),

 

        ),

 

        array(

 

            'random_number_c' => array(

 

                '$gt' => '0',

 

            ),

 

        ),

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

That’s not too bad, right?  Now let’s OR in the date_entered test:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array(

 

                '$and' => array(

 

                    array(

 

                        'random_number_c' => array(

 

                            '$lt' => '40'

 

                        ),

 

                    ),

 

                    array(

 

                        'random_number_c' => array(

 

                            '$gt' => '0'

 

                        ),

 

                    ),

 

                ),

 

            ),

 

            array(

 

                'date_entered' => '2014-03-01 19:54:47'

 

            ),

 

        ),

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

Okay, I admit it, that’s a little weird.  You have to explicitly use the $and directive because in this case it gets confused if you leave the AND implied as before.  So let’s put the whole filter together in all its glory:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array(

 

                '$and' => array(

 

                    array(

 

                        'random_number_c' => array(

 

                            '$lt' => '40'

 

                        ),

 

                    ),

 

                    array(

 

                        'random_number_c' => array(

 

                            '$gt' => '0'

 

                        ),

 

                    ),

 

                ),

 

            ),

 

            array(

 

                'date_entered' => '2014-03-01 19:54:47'

 

            ),

 

        ),

 

    ),

 

    array(

 

        '$or' => array(

 

            array(

 

                'name' => array(

 

                    '$starts' => 'A',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'B',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'R',

 

                ),

 

            ),

 

        ),

 

    ),

 

),

Sadly, the filter mechanism lacks some of the features of an SQL WHERE clause such as NOT, a true LIKE, and a few other things.  But you can handle most conditions with filters.

For a complete list of directives and additional documentation you can go to:

http://{your instance}/rest/v10/help

Search for "/<module>/filter" next to "GET" and click on it.

 

Making your filter the default filter

Normally with a layout that includes the filter pulldown the filter that shows all rows is the default.  This is usually fine, but sometimes it’s not ideal.  It’s pretty easy to set a custom filter as the default filter for a module.

To do this you need to create a "default/" directory in the same "filters/" directory as your other custom filters.  In this directory you create a "default.php" file.  But instead of the filter code you saw above, the file would instead contain something like this:

<?php

 

$module_name = 'flt_RandomCustom';

 

$viewdefs[$module_name]['base']['filter']['default'] = array(

 

    'default_filter' => 'blorgonly',

 

);

As I imagine you figured out, this will set the default filter to the blorgonly filter we created earlier.  To see this work you sometimes have to clear your browser cache completely after doing the repair and rebuild, but before reloading the page.

 

Debugging techniques

Remember the horrible complex filter we saw above?  It was as ugly to write as it is to read.  Developing it took a lot of iterations.  There aren’t a lot of tools which will help you figure things out, but there are a few.

 

Examining the API Call

When SugarCRM 7 tries to apply a filter it makes a GET call to the appropriate filter API.  If you open the JavaScript console on your browser (or Firebug, or whatever your tool of choice is) you can see the API call go by along with any errors.  It can sometimes be helpful to pull out the URL it calls and examine it to see what it might be trying to do.

Here’s the URL for the API call for the big ugly filter (with the various entities replaced with their original characters, of course):

http://{whatever+your+intance+is}/rest/v10/flt_RandomCustom/filter?fields=name,random_number_c,date_entered,my_favorite&max_num=20&order_by=date_modified:desc&filter[0][$or][0][$and][0][random_number_c][$lt]=40&filter[0][$or][0][$and][1][random_number_c][$gt]=0&filter[0][$or][1][date_entered]=2014-03-01+19:54:47&filter[1][$or][0][name][$starts]=A&filter[1][$or][1][name][$starts]=B&filter[1][$or][2][name][$starts]=R

Yeah, yikes.  But, you can ignore everything up to the first &filter parameter which leaves:

&filter[0][$or][0][$and][0][random_number_c][$lt]=40&filter[0][$or][0][$and][1][random_number_c][$gt]=0&filter[0][$or][1][date_entered]=2014-03-01+19:54:47&filter[1][$or][0][name][$starts]=A&filter[1][$or][1][name][$starts]=B&filter[1][$or][2][name][$starts]=R

That’s still not wonderful, but if you split it out by parameter, you get:

&filter[0][$or][0][$and][0][random_number_c][$lt]=40

 

&filter[0][$or][0][$and][1][random_number_c][$gt]=0

 

&filter[0][$or][1][date_entered]=2014-03-01+19:54:47

 

&filter[1][$or][0][name][$starts]=A

 

&filter[1][$or][1][name][$starts]=B

 

&filter[1][$or][2][name][$starts]=R

Which actually starts to make some sense, sort of.  Note, for example, how there are two main arrays, just like the arrays we have in our filter_definition array.

Watching how these parameters change as you change your filter code can shed light on how you need to arrange your arrays and directives.  It’s not a slam dunk by any means, but it can help when you’re stuck.  You’ll often see things grouped together in ways you didn’t expect which can point to a problem.

 

Actually calling the API

Sometimes it’s helpful to actually make the API call that SugarCRM makes so you can see everything in detail including any errors, warnings, and any returned rows.  You can also mess with the parameters directly to see what effect that has on the results.  Fortunately it’s not that hard to do.  Here’s what you need to do:

Bring up your favorite utility for making API calls.  For example, POSTman in Chrome, or RESTClient in Firefox are good choices.  For the purposes of this explanation we’ll use POSTman in Chrome, but the basic ideas are the same regardless of the tool you choose.

Bring up the JavaScript console in Chrome, select the “Network” tab, and reload your page with your filter selected.  You should see a bunch of calls go by.  Once the page is loaded, from the bottom up, hover over each call until you find a one resembling:

http://{your instance}/rest/v10/{your module name}/filter?...

That’s the API call that Sugar makes to apply the filter to the rows.

 

Click on that entry and make sure the “Headers” tab is selected.  This should show you, among many other things, the OAuth-Token.  Save that string.

Copy the “Request URL” into the url line for POSTman, set the method to GET, and add OAuth-Token to the header with a value of the string you just saved.

When you hit “Send” you should get a result with a return status of 200 and a “records” array appropriate for your filter.

 

This too isn’t necessarily a slam dunk in terms of debugging, but sometimes you’ll see errors or other things which may point to the problem.  Also you can manually change the parameters to experiment with different things to try to solve the problem.