How to add custom fields to a relationship table and display them in a Subpanel – SugarCRM | SuiteCRM

There may come a time in the development of a custom SuiteCRM or SugarCRM module when it would be handy to store additional data in the relationship table between two modules. This is not possible in studio or module builder and is not that straight forward even for an experienced coder unless you have a deep understanding of sugar’s underlying architecture.

So Ill save you the headache I had when first trying to implement this type of modification.

Step 1

The first thing you need to do is define your new fields in the metadata for the relationship. I will be adding the field in the relationship between a custom module FP_events and Contacts. The relationship fp_events_contacts is a many to many and the subpanel ill be displaying the field in is the contacts subpanel on the FP_events module.

This file can be found at:

custom/metadata/fp_events_contactsMetaData.php

In the code below notice that I added a field called ‘date_cancelled’ with type ‘date’ to the fields array.


$dictionary["fp_events_contacts"] = array (
 'true_relationship_type' => 'many-to-many',
 'relationships' =>
 array (
 'fp_events_contacts' =>
 array (
 'lhs_module' => 'FP_events',
 'lhs_table' => 'fp_events',
 'lhs_key' => 'id',
 'rhs_module' => 'Contacts',
 'rhs_table' => 'contacts',
 'rhs_key' => 'id',
 'relationship_type' => 'many-to-many',
 'join_table' => 'fp_events_contacts_c',
 'join_key_lhs' => 'fp_events_contactsfp_events_ida',
 'join_key_rhs' => 'fp_events_contactscontacts_idb',
 ),
 ),
 'table' => 'fp_events_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' => 'fp_events_contactsfp_events_ida',
 'type' => 'varchar',
 'len' => 36,
 ),
 4 =>
 array (
 'name' => 'fp_events_contactscontacts_idb',
 'type' => 'varchar',
 'len' => 36,
 ),
 5 =>
 array (
 'name' => 'invite_status',
 'type' => 'varchar',
 'len'=>'25',
 'default'=>'Not Invited',
 ),
 6 =>
 array (
 'name' => 'accept_status',
 'type' => 'varchar',
 'len'=>'25',
 'default'=>'No Response',
 ),
 7 =>
 array (
 'name' => 'email_responded',
 'type' => 'int',
 'len' => '2',
 'default' => '0',
 ),
 8 =>
          array (
              'name' => 'date_cancelled',
              'type' => 'date',
          ),
 ),
 'indices' =>
 array (
 0 =>
 array (
 'name' => 'fp_events_contactsspk',
 'type' => 'primary',
 'fields' =>
 array (
 0 => 'id',
 ),
 ),
 1 =>
 array (
 'name' => 'fp_events_contacts_alt',
 'type' => 'alternate_key',
 'fields' =>
 array (
 0 => 'fp_events_contactsfp_events_ida',
 1 => 'fp_events_contactscontacts_idb',
 ),
 ),
 ),
);

Once you have added your desired fields to the fields arrays do a quick repair and rebuild from the admin panel of Suite or Sugar and then execute the suggested sql queries this will add the fields to the database table for the relationship. (I double checked that the fields were added by going into phpmyadmin and looking at the fp_events_contacts_c table.)

Step 2

Your fields are now defined and in the actual database table but your only half way their if you want your fields to actually be displayed in the subpanel. The next thing you want to do is define your new fields in the vardefs for the relationship. This is done by adding a file in the custom/Extensions folder like so:

custom/Extension/modules/Contacts/Ext/Vardefs/CAN_BE_ANY_NAME.php

In this file add the following three definitions for each field your adding. Pay careful attention that all the field names and ids match up between definitions as minor typos here will prevent the fields from showing in the subpanel and can be major pain to spot:

$dictionary['Contact']['fields']['e_date_cancelled'] =
 array (
 'name' => 'e_date_cancelled',
 'rname' => 'id',
 'relationship_fields'=>array('id' => 'cancelled_id', 'date_cancelled' => 'event_cancelled'),
 'vname' => 'LBL_CONT_ACCEPT_CANCELLED',
 'type' => 'relate',
 'link' => 'fp_events_contacts',
 'link_type' => 'relationship_info',
 'join_link_name' => 'fp_events_contacts',
 'source' => 'non-db',
 'importable' => 'false',
 'duplicate_merge'=> 'disabled',
 'studio' => false,
 );

$dictionary['Contact']['fields']['event_cancelled'] =
 array(
 'massupdate' => false,
 'name' => 'event_cancelled',
 'type' => 'date',
 'studio' => 'false',
 'source' => 'non-db',
 'vname' => 'LBL_LIST_ACCEPT_CANCELLED',
 'importable' => 'false',
 );
$dictionary['Contact']['fields']['cancelled_id'] =
 array(
 'name' => 'cancelled_id',
 'type' => 'varchar',
 'source' => 'non-db',
 'vname' => 'LBL_LIST_ACCEPT_CANCELLED',
 'studio' => array('listview' => false),
 );

Step 3

The final thing you need to do is define the fields in the actual layout defs of the subpanel. In this case that file is located:

custom/modules/Contacts/metadata/subpanels/FP_events_subpanel_fp_events_contacts.php

In the code below notice that I add my field event_cancelled (as defined in the step 2 vardefs) to list_fields array and further down in the array I also add ‘e_date_cancelled’ and ‘cancelled_id’ and mark their usage as query_only.

$subpanel_layout['list_fields'] = array (
 'name' =>
 array (
 'name' => 'name',
 'vname' => 'LBL_LIST_NAME',
 'sort_by' => 'last_name',
 'sort_order' => 'asc',
 'widget_class' => 'SubPanelDetailViewLink',
 'module' => 'Contacts',
 'width' => '23%',
 'default' => true,
 ),
 'account_name' =>
 array (
 'name' => 'account_name',
 'module' => 'Accounts',
 'target_record_key' => 'account_id',
 'target_module' => 'Accounts',
 'widget_class' => 'SubPanelDetailViewLink',
 'vname' => 'LBL_LIST_ACCOUNT_NAME',
 'width' => '22%',
 'sortable' => false,
 'default' => true,
 ),
 'phone_work' =>
 array (
 'name' => 'phone_work',
 'vname' => 'LBL_LIST_PHONE',
 'width' => '15%',
 'default' => true,
 ),
 'email1' =>
 array (
 'name' => 'email1',
 'vname' => 'LBL_LIST_EMAIL',
 'widget_class' => 'SubPanelEmailLink',
 'width' => '20%',
 'sortable' => false,
 'default' => true,
 ),
 'event_status_name' =>
 array (
 'vname' => 'LBL_STATUS',
 'width' => '10%',
 'sortable' => false,
 'default' => true,
 ),
 'event_accept_status' =>
 array (
 'width' => '10%',
 'sortable' => false,
 'default' => true,
 'vname' => 'LBL_ACCEPT_STATUS',
 ),
 'event_cancelled' =>
 array (
 'width' => '10%',
 'sortable' => false,
 'default' => true,
 'vname' => 'LBL_ACCEPT_CANCELLED',
 ),
 'edit_button' =>
 array (
 'vname' => 'LBL_EDIT_BUTTON',
 'widget_class' => 'SubPanelEditButton',
 'module' => 'Contacts',
 'width' => '5%',
 'default' => true,
 ),
 'remove_button' =>
 array (
 'vname' => 'LBL_REMOVE',
 'widget_class' => 'SubPanelRemoveButton',
 'module' => 'Contacts',
 'width' => '5%',
 'default' => true,
 ),
 'e_accept_status_fields' =>
 array (
 'usage' => 'query_only',
 ),
 'event_status_id' =>
 array (
 'usage' => 'query_only',
 ),
 'e_invite_status_fields' =>
 array (
 'usage' => 'query_only',
 ),
 'event_invite_id' =>
 array (
 'usage' => 'query_only',
 ),
 'e_date_cancelled' =>
 array (
 'usage' => 'query_only',
 ),
 'cancelled_id' =>
 array (
 'usage' => 'query_only',
 ),
 'first_name' =>
 array (
 'name' => 'first_name',
 'usage' => 'query_only',
 ),
 'last_name' =>
 array (
 'name' => 'last_name',
 'usage' => 'query_only',
 ),
 'salutation' =>
 array (
 'name' => 'salutation',
 'usage' => 'query_only',
 ),
 'account_id' =>
 array (
 'usage' => 'query_only',
 ),
);

Also remember to add the label in this case LBL_ACCEPT_CANCELLED to the custom language strings.

I added it to:

custom/Extension/application/Ext/Language/en_us.Advanced OpenEvents.php

$app_strings['LBL_ACCEPT_CANCELLED'] = 'Date Cancelled';

But it may work if added to the mod strings.

Now do another quick repair and rebuild from the admin panel and your custom relationship fields should now be showing up on the subpanel. You will now be able to add data into these fields in your module controller either via queries or through sugar’s bean framework.

Note you might have to manually go into the database and add some dummy data into those fields to confirm they are showing (Assuming you have not yet added any data to your new fields).

Andy
About

Software Developer from Falkirk, Central Scotland.

Posted in SugarCRM development and customisation, SugarCRM development tutorials
7 comments on “How to add custom fields to a relationship table and display them in a Subpanel – SugarCRM | SuiteCRM
  1. avc says:

    Hello Sir,
    I need an informarion of how manually create a text field in suitecrm and added to the database.

    Thank You Sir.

  2. Andy Andy says:

    Add a definition for the text file to the custom/extension/modules/Yourmodule/vardefs and then do a quick repair and rebuild.

  3. Rafal says:

    Thanks for the good tutorial. You’ve added the custom field into a stock module (Contacts) subpanel. Do you know how to add a custom field into a custom module?
    For example you have FP_events and FP_locations with many-to-many relationship. Then you want to add a custom field into the subpanel for FP_events into FP_locations. What file should I put the new field into the $subpanel_layout['list_fields'] to have it displayed in the subpanel?

  4. Karina says:

    Hi! After doing a lot of research I can’t find the answer. I did all the steps you show and it works, thank you! But.. I need that one of the field in the relationship to be part of the alternate key of the relationship. For instance, in “documents_contacts” relationship, I added the field “order” that may have different values for the same pair of (document_id,contact_id), becoming (document_id, contact_id, order) an alternate key. Subpanel retrieves only unique (document_id, contact_id) pairs, but the total shows the right number of records retrieved. Is it possible to achieve this?
    Thanks in advance, and thank you very much for your great life-saving tutorial =)

  5. Bao says:

    I want to add field Role in Contact subpanel of Cases same with Role in Contact subpanel of Opportunity. Please help me

    Thanks

  6. Pranav says:

    Can we edit the fields we created above?

  7. Mark says:

    Thanks for the tutorial! This has been really tricky for me.

Leave a Reply to Pranav Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>