Skip navigation
All Places > Developer > Blog > 2017 > February
2017
This post originally appeared on the SynoLab blog hosted by Synolia, an Elite SugarCRM Partner. Yann Bergès describes how you can use a relate filter with the Filter API. He also explores how Sugar does it as well as identifying a drawback to be considered when using this feature.

 

We all know the moment when you are roaming through source code to find something for a particular purpose and you come across that feature you didn’t expect but you absolutely want to test. This is how I came into the related link filter feature. What do I mean by related link filter? It is a derivative way to filter related data on a One-to-Many relationship by specifying a link name and a target field:

 

I want all Contacts filtered on their related Account with « Industry » value « Electronics » (use « one » side of the relationship)

 

I want all Accounts filtered on their related Contacts with « Title » value « President » (use « many » side of the relationship)

This is an advanced use of Sugar 7 Filter API, if you never used it before, have a look at this documentation for detailed information about how filters work:

 

– SugarCRM Cookbook – The School of REST – Part 3

 

– Sugar 7.8 Developer Guide - Architecture - Filters

 

Examples and tests have been made with a Sugar instance PRO 7.8.0.0

 

How does it work?

 

First of all, we have to look at the source code that manages this capability to understand how we will build our filter.clients/base/api/FilterApi.php

 

 

 

<?php

 

...

 

protected static function verifyField(SugarQuery $q, $field)

 

{

 

    $ret = array();

 

    if (strpos($field, '.')) {

 

        // It looks like it's a related field that it's searching by

 

        list($linkName, $field) = explode('.', $field);

 

        $q->from->load_relationship($linkName);

 

        if(empty($q->from->$linkName)) {

 

            throw new SugarApiExceptionInvalidParameter("Invalid link $linkName for field $field");

 

        }

 

        if($q->from->$linkName->getType() == "many") {

 

            // FIXME TY-1192: we have a problem here: we should allow 'many' links for related to match against

 

            // parent object but allowing 'many' in  other links may lead to duplicates. So for now we allow 'many'

 

            // but we should figure out how to find if 'many' is permittable or not.

 

            // throw new SugarApiExceptionInvalidParameter("Cannot use condition against multi-link $linkName");

 

        }

 

        $join = $q->join($linkName, array('joinType' => 'LEFT'));

 

        $table = $join->joinName();

 

        $ret['field'] = "$table.$field";

 

        $bean = $q->getTableBean($table);

 

        if (empty($bean))

 

            $bean = $q->getTableBean($linkName);

 

        if (empty($bean) && $q->getFromBean() && $q->getFromBean()->$linkName)

 

            $bean = BeanFactory::getBean($q->getFromBean()->$linkName->getRelatedModuleName());

 

        if(empty($bean)) {

 

            throw new SugarApiExceptionInvalidParameter("Cannot use condition against $linkName - unknown module");

 

        }

 

    } else {

 

        $bean = $q->from;

 

    }

 

    ...

 

}

 

...

 

 

 

The method verifyField has a specific behavior for field names having a dot character inside. The field name passed is parsed has « link ». »field » to match an existing link on the current module requested. There are 2 things to remember here: using any related field of a link is allowed (so not only « name » or « id » as vardef definition have for « relate » type fields, e.g. « account_id » and « account_name ») and using « many » side of a link is tolerated.

 

We will come back later on the FIXME, let’s practice now how to fill these conditions for filtering results.

 

How to use relate filter with FilterApi ?

 

For my examples, I used the HTTP client provided by Postman Chrome Extension and the demo dataset for Sugar PRO 7.8.0.0.

 

My first need: I wanted all Contacts filtered on their related Account with « Industry » value « Electronics ». Let’s have a look on how to implement it with a relate filter.

 

The second test is with the « many » side of a relationship: I want all Accounts filtered on their related Contacts with « Title » value « President »

 

Do not forget that such filter can be combined with other ones with « $and » and « $or » constructions or you can use more complex logic with operators provided for targeted field types (« $in », « $starts », …).

 

So what happens when using a relate filter on the « many » side ?

 

Adding related data augments the query to join additional tables. As highlighted in the FIXME comment, the « many » side of a relationship can produce duplicate entries in the results. Actually, Sugar could remove these duplicates manually, but it will have a cost and it will not prevent huge transfer of result set data from the database. So before executing query, the generated SugarQuery is inspected and modified to include a DISTINCT option in order to reduce data result size to unique values:MySQL

 

 

 

SELECT DISTINCT accounts.name name, accounts.id id, accounts.date_modified date_modified, accounts.assigned_user_id assigned_user_id, accounts.created_by created_by

 

FROM accounts

 

INNER JOIN(

 

    SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id AND team_memberships.user_id = 'seed_will_id'

 

    AND team_memberships.deleted = 0 GROUP BY tst.team_set_id

 

) accounts_tf ON accounts_tf.team_set_id = accounts.team_set_id

 

LEFT JOIN accounts_contacts jt1_accounts_contacts ON(accounts.id = jt1_accounts_contacts.account_id AND jt1_accounts_contacts.deleted = 0)

 

LEFT JOIN contacts jt0_contacts ON(jt0_contacts.id = jt1_accounts_contacts.contact_id AND jt0_contacts.deleted = 0 AND(jt0_contacts.team_set_id IN(

 

    SELECT tst.team_set_id FROM team_sets_teams tst INNER JOIN team_memberships team_membershipsjt0_contacts ON tst.team_id = team_membershipsjt0_contacts.team_id AND team_membershipsjt0_contacts.user_id = 'seed_will_id'

 

    AND team_membershipsjt0_contacts.deleted = 0)))

 

WHERE accounts.deleted = 0 AND jt0_contacts.title = 'President'

 

ORDER BY accounts.date_modified DESC, accounts.id DESC

 

LIMIT 0, 21

 

 

 

Let me highlight some source code that will illustrate what Sugar does:data/SugarBean.php

 

 

 

<?php

 

...

 

function fetchFromQuery(SugarQuery $query, array $fields = array(), array $options = array()) {

 

    ...

 

    if ($this->queryProducesDuplicates($query)) {

 

        $this->fixQuery($query);

 

    }

 

    ...

 

}

 

...

 

protected function queryProducesDuplicates(SugarQuery $query)

 

{

 

    foreach ($query->join as $join) {

 

        if ($join->linkName) {

 

            $seed = $query->from;

 

            $linkName = $join->linkName;

 

            if ($seed->load_relationship($linkName)) {

 

                /** @var Link2 $link */

 

                $link = $seed->$linkName;

 

                if ($link->getType() === REL_TYPE_MANY) {

 

                    $relationship = $link->getRelationshipObject();

 

                    if (empty($relationship->primaryOnly)) {

 

                        return true;

 

                    }

 

                }

 

            }

 

        }

 

    }

 

    return false;

 

}

 

...

 

protected function fixQuery(SugarQuery $query)

 

{

 

    foreach ($query->select->select as $field) {

 

        if ($field->table) {

 

            $bean = $query->getTableBean($field->table);

 

            if (!$bean) {

 

                $bean = $query->from;

 

            }

 

            $def = $bean->getFieldDefinition($field->field);

 

            $type = $this->db->getFieldType($def);

 

            if ($this->db->isTextType($type)) {

 

                $GLOBALS['log']->warn('Unable to fix the query containing text field');

 

                return;

 

            }

 

        }

 

    }

 

    $query->distinct(true);

 

}

 

...

 

 

 

Using DISTINCT is not always a good way to prevent duplicate results, especially when there is not enough memory allocated to the database server or too many rows to compute. If you run into this problem, then you will have to optimize your SugarQuery or simply find another way to get the results you need.

 

Conclusion

 

Keep in mind that using this feature can generate heavy load on your database server, especially if you have no idea of what is going on behind your query. Also, since it is not a public method, we can’t guess if SugarCRM is going to make some updates on the verifyField method to handle the « many » side differently (there is explicitly the commented line throw new SugarApiExceptionInvalidParameter("Cannot use condition against multi-link $linkName");).

 

Sugar 7 provides many tools to deal with problematic relationships. So keep into digging some of their great features for more efficient results or just to satisfy your own curiosity.

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.

Many customers want to configure Sugar for Single Sign On (SSO). Well Sugar supports Security Assertion Markup Language (SAML) so this must be easy, right? But the devil is always in the details.

 

Each SAML identity provider behaves a little differently. Each of these systems has different terminology and methods for configuration and may use different default settings. Some of these important configuration settings can make the difference between a successful SSO implementation and a tire fire. For example, are users provisioned Just-In-Time or will they be provisioned manually? Did you know that Sugar uses the e-mail address as the SAML application username format?

 

Below are instructions for configuring SAML SSO with a couple of common identity providers.

 

Okta

 

One of our Solution Architects, Enrico Simonetti, wrote a good summary of how to configure SAML authentication for Sugar using Okta as the identity provider. Okta is convenient for trying out SSO because they have a developer program you can join. Enrico also covers a few tips and details that can trip up any SAML implementation.

 

Please visit Enrico's post called SSO Authentication on SugarCRM with SAML for more details including screen shots and even code examples.

 

Active Directory Federation Service

 

The most common system that we get questions about is Microsoft's Active Directory Federation Service (ADFS). ADFS is pretty complicated so there are several steps that you need to follow to get it done right.

 

We recently publish a SugarCRM Knowledge Base article called Configuring SSO With Active Directory's ADFS. It was written by Lars Blockken, one of our Senior Technical Account Managers, and in it he walks you through each of these steps in detail along with screenshots. It will have you up and running on ADFS in no time!