We all know that when a one-to-many relationship exists between two modules, Sugar will create a subpanel in the Parent Module and a relationship table in the database to store the related records ids to load that records in the subpanel. This is pretty powerful but in some situations, there are requirements to create two subpanels with only one relationship and categorize the related records into those two subpanels. This blog will explain how to create two subpanels with one relationship and categorize related records into those subpanels. 

An Example Scenario:

Let’s take an example where your customer has a requirement to show Opportunities related to Accounts in two subpanels in one subpanel with all the open Opportunities and the other with all closed Opportunities.

Now let us see how to build two subpanels for Opportunities in Accounts modules by using standard relationship in the studio.

Step 1: Create Metadata for Custom subpanel.

We know that there is already a one-to-many SugarCRM standard relationship between Accounts and Opportunities in SugarCRM and the relationship metadata is defined in modules/Accounts/vardefs.php

Now to create a new subpanel and to categorise Closed opportunities and Open Opportunities let us create a file in following path and add the below code.

custom/Extension/modules/Accounts/Ext/Vardefs/<your_file_name>.php

<?php
$dictionary['Account']['fields']['accounts_closed_opportunities'] = array(
    'name' => 'accounts_closed_opportunities',
    'type' => 'link',
    'relationship' => 'accounts_opportunities',
    'source' => 'non-db',
    'module' => 'Opportunities',
    'bean_name' => 'Opportunity',
    'vname' => 'LBL_ACCOUNTS_CLOSED_OPPORTUNITIES_TITLE',
    'link-type' => 'many',
    'side' => 'left',
);

The above code defines a custom relationship metadata with a name and tells the system to use the relationship which is used by the one-to-many relationship between accounts and opportunities. So that the related records are loaded into this custom subpanel form accounts_opportunities table.

Step 2: Custom Subpanel Title

As we want to differentiate Open Opportunities and Closed Opportunities, Let us rename the new subpanel as Closed Opportunities. To do this create a file in  following path and add the below code.

custom/Extension/modules/Accounts/Ext/Language/en_us.<your_file_name>.php

<?php
   $mod_strings['LBL_ACCOUNTS_CLOSED_OPPORTUNITIES_TITLE'] = 'Closed Opportunities';
?>

The above code will set the label to the custom subpanel.

Step 3: Create Viewdefs for Custom Subpanel

To render the custom subpanel in Accounts record view, create a new file in following path and add the below code.

custom\Extension\modules\Accounts\Ext\clients\base\layouts\subpanels\<your_file_name>.php

<?php
$viewdefs['Accounts']['base']['layout']['subpanels']['components'][] = array (
  'layout' => 'subpanel',
  'label' => 'LBL_ACCOUNTS_CLOSED_OPPORTUNITIES_TITLE',
  'context' => 
  array (
    'link' => 'accounts_closed_opportunities',
  ),
);
?>

This code will give metadata for the subpanel layout.

Step 4: Rename Default Subpanel

Now it’s time  to rename our default subpanel to the Open Opportunities in Accounts module. To do this open the Sugar Instance as an admin user and go to Admin> Studio> Accounts> Subpanels> Opportunities and rename the stock subpanel from Opportunities to Open Opportunities because we are going to load open Opportunities into it.

After this do Quick Repair and Rebuild now you will see a new custom subpanel in Accounts Record View.

Now you will see both the subpanels will load the same data, this is because we didn’t add the code to filter the Opportunities based on the sales stage.

To achieve this we need to add two more attributes namely link_class and link_file in the metadata files of both stock and custom subpanels. Here, link_class is the Class Name we are using for handling relationships and link_file is the path for the PHP file in which the class is defined.

Categorizing the related records for subpanels

Step 1: Adding attributes to relationship metadata

 As discussed in the above step, in this step we will add the two attributes for filtering data.

Open the relationship metadata file which was created before in following path and add these two attributes.

custom/Extension/modules/Accounts/Ext/Vardefs/<your_file_name>.php

The code will look like this,

<?php
$dictionary['Account']['fields']['accounts_closed_opportunities'] = array(
    'name' => 'accounts_closed_opportunities',
    'type' => 'link',
    'relationship' => 'accounts_opportunities',
    'source' => 'non-db',
    'module' => 'Opportunities',
    'bean_name' => 'Opportunity',
    'vname' => 'LBL_ACCOUNTS_CLOSED_OPPORTUNITIES_TITLE',
    'link-type' => 'many',
    'side' => 'left',
    'link_file' =>'custom/modules/Accounts/ClosedOpportunitiesForAccounts.php',
    'link_class' => 'ClosedOpportunitiesForAccounts', 
);

Do the same for stock relationship metadata. Create a new file in  the following path and add the below code.

custom\Extension\modules\Accounts\Ext\Vardefs\<file_name>.php 

<?php
$dictionary['Account']['fields']['opportunities']['link_file'] ='custom/modules/Accounts/OpenOpportunitiesForAccounts.php';
$dictionary['Account']['fields']['opportunities']['link_class'] = 'OpenOpportunitiesForAccounts';

In the above code, we are adding link_class and link_file attributes to stock relationship metadata.

Step 2: Filtering Related Records

Now create these classes in their defined paths to load the related records. Create a new file in  the following path and add the below code.

custom/modules/Accounts/ClosedOpportunitiesForAccounts.php

<?php
class ClosedOpportunitiesForAccounts extends Link2
{
    function __construct($linkName, $bean, $linkDef = false){
        parent::__construct($linkName, $bean, $linkDef = false);
    }
    function buildJoinSugarQuery($sugar_query, $options = array()){
        $closed_stages = array(
            'Closed Won',
            'Closed Lost',
        );
        $sugar_query->where()->in('sales_stage',$closed_stages);
      return $this->relationship->buildJoinSugarQuery($this, $sugar_query, $options);
    }
}

Sugar uses Link2 class which is defined in data/Link2.php for handling relationships, In the above code, we are extending Link2 class and overriding the buildJoinSugarQuery function to filter the records.

The buildJoinSugarQuery function has a SugarQuery object as an argument and we can make the query using that object like above.

Do the same for the stock relationship also by adding the below code in the following path.

custom/modules/Accounts/OpenOpportunitiesForAccounts.php

<?php
class OpenOpportunitiesForAccounts extends Link2
{
    function __construct($linkName, $bean, $linkDef = false){
        parent::__construct($linkName, $bean, $linkDef = false);
    }
    function buildJoinSugarQuery($sugar_query, $options = array()){
        $non_open_stages = array(
            'Closed Won',
            'Closed Lost',
        );
        $sugar_query->where()->notIn('sales_stage',$non_open_stages);
        return $this->relationship->buildJoinSugarQuery($this, $sugar_query, $options);
    }
}

Once all your files are in place do a quick repair and rebuild and you are done.

Now as shown in the above image we can see two Opportunities Subpanels, one with Open Opportunities Subpanel which filters only the Open Opportunities, and Closed Opportunities Subpanel which filters only Closed Opportunities. 

Conclusion

This customisation will be really useful where the customer wants to categorise the Opportunities based on their sales stages. Not only in Opportunities we can customise similarly for any modules in Sugar like for example where we need to categorise Meetings, Calls, Leads etc and this is completely Upgrade safe Customisation.