David Ranney

Creating Subpanels with Custom Results in Sugar 7.5

Blog Post created by David Ranney Employee on May 18, 2015
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.

Outcomes