HOWTO: Add additional data to many-to-many relationship (only viewing)

[gunni]

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
Prefix
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.

 $module_name='gun_courses';
$relation="gun_courses_contacts"; // added only for easier handling and avoiding typos and errors
$relation_field="role";
$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(
        'name'=>array(
             'vname' => 'LBL_NAME',
            'widget_class' => 'SubPanelDetailViewLink',
             'width' => '45%',
        ),
        'date_modified'=>array(
             'vname' => 'LBL_DATE_MODIFIED',
             'width' => '45%',
        ),
                // from here all the fields are necessary
                "{$relation}_fields"=>array( //variable value from above
                    'usage'=>'query_only'
                ),
                "{$relation}_id"=>array( //variable value from above
                    'usage'=>'query_only'
                ),
                "{$relation}_{$relation_field}"=>array( //variable value from above
             'vname' => 'LBL_FOR_ADDITIONAL_DATA',
             'width' => '45%',
        ),
        // end of the fields for additional data field
        'edit_button'=>array(
            'widget_class' => 'SubPanelEditButton',
             'module' => $module_name,
             'width' => '4%',
        ),
        'remove_button'=>array(
            'widget_class' => 'SubPanelRemoveButton',
             'module' => $module_name,
            'width' => '5%',
        ),
    ),
); 
  

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

 <?php
$module_name='gun_courses'; // Only these two values need to be adjusted
$relation="gun_courses_contacts"; // Again only to avoid typos and mistakes
$relation_field="role";
// 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.
**Edit**: Set the field name variable to easier allow multiple fields in relation.

[Ramblin]

This was a fantastic help - thank you!

Did you ever figure out how to edit the additional fields by clicking on the Edit button in the Subpanel - like they do for contact-role in the opportunities-contacts relationship?

Ramblin

[gunni]

I only edit these values with own made sugarwidgets and ajax calls. I got two types one for dates and one for integers.

[Ramblin]

Thanks for the quick reply

I am creating an events package with modules for
event details
venue
faclitator
caterer

with many relationships and I got your display to work; now I just need to edit the custom relationship fields

Did you not use the Opportunities/Contacts method to edit because you found a better way or because the Opportunities/Contacts way was just to unwieldy?

If you found a better way and are willing to share, I'd be interested

Ramblin

[gunni]

Sorry for the late reply.
I just did not like the way it is handled in the original opportunities/contacts relation, as then you need additional files for each relation.
I use ajax calls and display the fields as sugar widgets, so the values are editable inline in the listview. That is not perfect, but does work for me.
I am at home now, so i cannot post the code, but i think i posted it somewhere here in the forum, i will have a quick look.
*Edit*
Thats where i wrote it down:
http://www.sugarcrm.com/forums/showp...4&postcount=24

I did some minor changes since then, but this should work. In the thread the post is in there is another solution, you can have a look if you like it more http://www.sugarcrm.com/forums/showthread.php?t=56953

[Ramblin]

Thank you sir

I did figure out how to get it to work using the Opportunities<=>Contacts as a template. Pain in the but but it works.

I did not want the users to have two different ways to edit relationship fields and I was not about to re-do the Opportunities<=>Contacts method, so perservered and got it going.

See
http://www.sugarcrm.com/forums/showthread.php?t=69667
for the solution.

[gunni]

Pain in the but but it works.

That was the reason for me to write my own solution

[anthony.watson]

Three problems (unless you can tell me otherwise):

1. The only way to access the relationship attribute value via the bean is by using "role" in the field name (see method "_get_link_table_role_field" in data/Link.php)

2. the _get_link_table_role_field() method only returns one field, so you can't have more than one attribute field per relationship and access via the Bean

3. The relate attribute is not reportable, so you can't run a report (in your example) for users and classes grouped by year.

Back to using custom modules for me, I guess. Let me know if I'm wrong about the above.

[Francescas]

I followed your examples for my custom situation: custom Address Locations module related M-M to Contacts, (and M-M to Accounts - but Contacts is what I'm interested in here).
We are wanting to add a mailstop field in the Contact-Address relationship.

I can see the custom field in the back end in the relationship table,
I can see the field in the subpanel column headers, but I cannot see the value that I entered in the field in the subpanel. (data entry was done from the back end directly in the DB)

$module_name = 'addlo_Address_Locations';
$relation = 'addlo_addretions_contacts';
$relation_filed = "mailstop";

the rest is just like your code.

addlo_Address_Locations is our custom module
addlo_addretions_contacts is the relationship that was generated when the module was created
mailstop is the additional field I am adding to the relationship.

the files in question already existed, changes I made are in bold.

custom/Extension/modules/addlo_Address_Locations/Ext/Vardefs/addlo_address_locations_contacts_addlo_Address_Loc ations.php

<?php

$module_name = 'addlo_Address_Locations';
$relation = 'addlo_addretions_contacts';
$relation_filed = "mailstop";

$dictionary["$module_name"]["fields"]["{$relation}_fields"] = array (
'name' => "{$relation}_fields",
'rname' => 'id',
'relationship_fields'=>array('id' => "{$relation}_id", "{$relation}_{$relation_field}"=>"{$relation}_{$re lation_field}"),
'vname' => 'LBL_MAILSTOP_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_MAILSTOP_ID',
);
$dictionary["$module_name"]["fields"]["{$relation}_{$relation_field}"] = array(
'name' => "{$relation}_{$relation_field}",
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_MAILSTOP',
'massupdate' => false,
);

$dictionary["addlo_Address_Locations"]["fields"]["addlo_addretions_contacts"] = array (
'name' => 'addlo_addretions_contacts',
'type' => 'link',
'relationship' => 'addlo_address_locations_contacts',
'source' => 'non-db',
'vname' => 'LBL_ADDLO_ADDRESS_LOCATIONS_CONTACTS_FROM_CONTACT S_TITLE',
);
?>

custom/modules/addlo_Address_Locations/metadata/subpanels/Contact_subpanel_addlo_addretions_contacts.php

<?php
$module_name = 'addlo_Address_Locations';
$relation = 'addlo_addretions_contacts';
$relation_field = "mailstop";

$subpanel_layout['list_fields'] = array (
'name' =>
array (
'vname' => 'LBL_NAME',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '45%',
'default' => true,
),
'addlo_addre_accounts_name' =>
array (
'type' => 'relate',
'link' => 'addlo_addretions_accounts',
'vname' => 'LBL_ADDLO_ADDRESS_LOCATIONS_ACCOUNTS_FROM_ACCOUNT S_TITLE',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '10%',
'default' => true,
),
'tax_exempt_c' =>
array (
'type' => 'bool',
'default' => true,
'vname' => 'LBL_TAX_EXEMPT',
'width' => '10%',
),
'reseller_c' =>
array (
'type' => 'relate',
'default' => true,
'studio' => 'visible',
'vname' => 'LBL_RESELLER_C',
'width' => '10%',
),
// fields for mailstop
"{$relation}_fields"=>array( //variable value from above
'usage'=>'query_only'
),
"{$relation}_id"=>array( //variable value from above
'usage'=>'query_only'
),
"{$relation}_{$relation_field}"=>array( //variable value from above
'name' => "{$relation}_{$relation_field}",
'vname' => 'Mailstop',
'width' => '20%',
),
// end of the fields for mailstop
'edit_button' =>
array (
'widget_class' => 'SubPanelEditButton',
'module' => 'addlo_Address_Locations',
'width' => '4%',
'default' => true,
),
'remove_button' =>
array (
'widget_class' => 'SubPanelRemoveButton',
'module' => 'addlo_Address_Locations',
'width' => '5%',
'default' => true,
),
);
?>

custom/metadata/addlo_address_locations_contactsMetaData.php

<?php
// created: 2011-05-26 18:09:54
$dictionary["addlo_address_locations_contacts"] = array (
'true_relationship_type' => 'many-to-many',
'relationships' =>
array (
'addlo_address_locations_contacts' =>
array (
'lhs_module' => 'addlo_Address_Locations',
'lhs_table' => 'addlo_address_locations',
'lhs_key' => 'id',
'rhs_module' => 'Contacts',
'rhs_table' => 'contacts',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'addlo_addreons_contacts_c',
'join_key_lhs' => 'addlo_addr8a54cations_ida',
'join_key_rhs' => 'addlo_addrd2e2ontacts_idb',
),
),
'table' => 'addlo_addreons_contacts_c',
'fields' =>
array (
0 =>
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
1 =>
array (
'name' => 'date_modified',
'type' => 'datetime',
),
2 =>
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
3 =>
array (
'name' => 'addlo_addr8a54cations_ida',
'type' => 'varchar',
'len' => 36,
),
4 =>
array (
'name' => 'addlo_addrd2e2ontacts_idb',
'type' => 'varchar',
'len' => 36,
),
5 =>
array (
'name' => 'addlo_addretions_contacts_mailstop',
'type' => 'varchar',
'len' => 50,
),
),
'indices' =>
array (
0 =>
array (
'name' => 'addlo_addretions_contactsspk',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'addlo_addretions_contacts_alt',
'type' => 'alternate_key',
'fields' =>
array (
0 => 'addlo_addr8a54cations_ida',
1 => 'addlo_addrd2e2ontacts_idb',
),
),
),
);
?>

Any suggestions for troubleshooting?

Thanks!

[jskov]

Will anyone write a plugin for this?