February 2013

This tutorial is for those who:
* want to create one-to-many relationship between two modules without an intermediate table (like ModuleBuilder does)
* has a relate field in a module and want to display a subpanel to the related module
* what to make this using code, in an upgrade safe manner

If you are using SugarCRM and you want to create one-to-many relationship between two modules via ModuleBuilder an aditional table will be created even if that isn’t necesary(no need for a table now for this type of relationship).
Eliminating additional table will improove the application performance and will make more easy to maintain.

Let’s consider the following scenario:
We have two modules: Invoices and Accounts. In custom Invoice module we already have a related field, billing_account_id that makes connection with Account module. We want to display in a subpanel from Accounts module all the invoices that are related.

If you have a related field in Invoices does’t mean that you have the relationship too. So we’ll have to create 4 files for that:
* /custom/Extension/modules/inv_Invoices/Ext/Vardefs/inv_invoices_accounts.php – where have to add the relationship itself and a link field between Invoices and Accounts
* /custom/Extension/modules/Accounts/Ext/Vardefs/inv_invoices_accounts.php – where have to add a link fild between the two modules
* /custom/Extension/modules/Accounts/Ext/Layoutdefs/inv_invoices_accounts.php – definition for invoices subpannel
* /custom/Extension/modules/inv_Invoices/Ext/Language/en_us.inv_invoices_accounts.php – a language variable with the name of the subpanel

Let’s see the code:

* /custom/Extension/modules/inv_Invoices/Ext/Vardefs/inv_invoices_accounts.php
$dictionary['inv_Invoices']['fields']['accounts'] = array(
'name' => 'accounts',
'type' => 'link',
'relationship' => 'inv_invoices_accounts',
'module' => 'Accounts',
'bean_name' => 'Account',
'source' => 'non-db',
'vname' => 'LBL_ACCOUNTS',

$dictionary['inv_Invoices']['relationships']['inv_invoices_accounts'] = array(
'lhs_module' => 'Accounts',
'lhs_table' => 'accounts',
'lhs_key' => 'id',
'rhs_module' => 'inv_Invoices',
'rhs_table' => 'inv_invoices',
'rhs_key' => 'billing_account_id',
'relationship_type' => 'one-to-many',

* /custom/Extension/modules/Accounts/Ext/Vardefs/inv_invoices_accounts.php
$dictionary['Account']['fields']['inv_invoices'] = array(
'name' => 'inv_invoices',
'type' => 'link',
'relationship' => 'inv_invoices_accounts',
'module' => 'inv_Invoices',
'bean_name' => 'inv_Invoices',
'source' => 'non-db',
'vname' => 'LBL_INVOICES',

* /custom/Extension/modules/inv_Invoices/Ext/Language/en_us.inv_invoices_accounts.php
$mod_strings['LBL_INVOICES_SUBPANEL_TITLE'] = 'Invoices';

Atention, we supose that we already have the definition for the related field in Invoice module vardefs.php :
$dictionary['inv_Invoices']['fields'] = array (
'importable' => 'required',

'billing_account_id' =>
'table' => 'accounts',
'isnull' => 'true',
'module' => 'Accounts',
'dbType' => 'id',
'reportable' => false,
'massupdate' => false,
'duplicate_merge' => 'disabled',

At the end, you’ll have to make a “quick repair and rebuid” and in Acounts modules you’ll have to see the Invoices subpanel(if the curent account was assingned to an invoice).

That’a all folks :)

In SugarCRM, a relate field can be added to EditView form using a popup from where you can select the desired entry.

In popup, you also have posibility to perform a search to filter the results from related module.
The nice part is that you can automatically send a filtering parameter using “initial_filter” from editviewdefs.php definition. So, for related field you have to add those lines:
'displayParams' => array(
'initial_filter' => "&account_name_advanced=John%20Doe",

That means that the popup it will open with “Account Name” prefilled with “John Doe” value. Be aware that target field is account_name but in “initial_value” the name is account_name_advanced.

This is great, but what if I want to send to pop-up a value of a field that has already been filled, not just a hardcoded value? Let suppose I have an invoice module and in EditView I have an “Acount Name” (related with Accounts) and “Payment responsible” (related with contacts). When I choose payment responsible I what to filter the results in popup by Account name. That makes sense.

To achieve this, in editviewdefs.php you must have:
array (
'name' => 'contact',
'displayParams' => array(
'initial_filter' => "&account_name_advanced=\" + document.getElementById(\"billing_account_name\").value + \"",

billing_account_name is actually the field name of “Account name”.
To understand how this works you can look at the HTML source and search the open_popup() function that is trigged by Contact button. Actually, the “initial_filter” value is a parameter in open_popup() javascript function.
So we can interfere in javascript and call a particular value from form(that we suppose is filled) using javascript function getElementById.

That’s all folks :)