HOWTO: Add additional data to many-to-many relationship for viewing

sugarcrmdevelopers —  July 22, 2011 — Leave a comment

Editor’s Note: I pulled this post from a forums post by community member gunni, after one of the members of our internal team used the instructions for a project he was working on.

As i often stumbled upon this relationship type, and always i try to do a new one i make errors and dont get it to work. Now i invested 1 day to figure out minimal changes needed to add additional data to a relation.
Hope you find it helpful:

Whats this about
Lets say we have contacts and courses. Contacts is the standard sugarmodule, wheras courses is a new build module in modulebuilder.
We now have a relation like this:

Contact A <–> Course 1
Contact A <–> Course 2
Contact B <–> Course 2

Now if we want to have additional info, like the year a student passes the course, for that relation it gets a mess. You can insert a second module between the two, or try to build the data into the relationship. The second is what i am doing here by simply adding/changing only three files.
The relation will look like this after that.

Contact A <-2008-> Course 1
Contact A <-2009-> Course 2
Contact B <-2008-> Course 2

For the following i will use two variables to be more flexible, and simplyfy the changes need to be done for your modules:
{$relation} = name of the relation =”gun_courses_contacts” in this Howto
{$relation_field}= database field in the relation = “role” in this Howto
Replace all occurences in this howto by the values, or use a variable before, to set the value.

What to do?

1. Create a module courses in modulebuilder with the needed fields. In this howto i choose the prefix “gun”.
2. Create the many-to-many realtionship to contacts in modulebuilder.
3. Deploy the module in the actual instance or export it and load it to another instance of sugar.
4. Insert the field that holds the additional info into the metadata file that is located in custom/metadata/ and should be named something like gun_courses_contactsMetaData.php.
Insert the field after the last field:

5 =>
array (
'name' => '{$relation}_{$relation_field}', // Replace the values according to your relation
'type' => 'varchar',
'len' => 36,

5. Change the default subpanel that it looks like following. The file is located in modules/gun_courses/metadata/subpanels/default.php
$relation should be set to the name of the relation found in the relationship metadata file gun_courses_contactsMetaData.php.

$relation="gun_courses_contacts"; // added only for easier handling and avoiding typos and errors
$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name),
'where' => '',
//'fill_in_additional_fields' => true,
'list_fields' => array(
'vname' => 'LBL_NAME',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '45%',
'vname' => 'LBL_DATE_MODIFIED',
'width' => '45%',
// from here all the fields are necessary
"{$relation}_fields"=>array( //variable value from above
"{$relation}_id"=>array( //variable value from above
"{$relation}_{$relation_field}"=>array( //variable value from above
'width' => '45%',
// end of the fields for additional data field
'widget_class' => 'SubPanelEditButton',
'module' => $module_name,
'width' => '4%',
'widget_class' => 'SubPanelRemoveButton',
'module' => $module_name,
'width' => '5%',

6. Add following file: custom/Extension/modules/gun_courses/Ext/Vardefs/myadditionalrelationfield.php

$module_name='gun_courses'; // Only these two values need to be adjusted
$relation="gun_courses_contacts"; // Again only to avoid typos and mistakes
// gun_courses is the name of our module, prefix + modulename
$dictionary["$module_name"]["fields"]["{$relation}_fields"] =  array (
'name' => "{$relation}_fields",
'rname' => 'id',
'relationship_fields'=>array('id' => "{$relation}_id", "{$relation}_{$relation_field}"=>"{$relation}_{$relation_field}"),
'vname' => 'LBL_EVENT_NAME',
'type' => 'relate',
'link' => "$relation",
'link_type' => 'relationship_info',
'source' => 'non-db',
'Importable' => false,
'duplicate_merge'=> 'disabled',
'join_name' => "{$relation}",
$dictionary["$module_name"]["fields"]["{$relation}_id"] =  array(
'name' => "{$relation}_id",
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_COURSE_ROLE_ID',
$dictionary["$module_name"]["fields"]["{$relation}_{$relation_field}"] = array(
'name' => "{$relation}_{$relation_field}",
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_COURSE_ROLE',
'massupdate' => false,

7. Rebuild Database, clear vardef cache and rebuild relationships
8. Now you can enter values in the new column (only directly in database) and see them in the subpanel
9. TODO: Nice way for editing the values, whoever like can append an easy way here. I thougt about an AJAX alternative …

If you find things to improve feel free to leave a comment.

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s