Adding Fields to a Relationship/Subpanel

bsoremsugar —  October 4, 2011 — 17 Comments

Editor’s Note: I’m sure all the regulars of this blog recognize the author of today’s post. For those new to the community, he is the infamous Jason Eggers ( known prolifically as eggsurplus in the forums ) who is a very active community member and SugarForge project owner coming from the Land of 10000 Lakes. He’s quite versed in developing on the Sugar platform, and will be bringing us regular posts on this blog.

Ever had the need to add a field to a relationship but couldn’t figure out how? It isn’t necessarily as straight forward
to do as a normal Studio field addition is, but thankfully we have an example that SugarCRM already
includes by default! That is the Opportunities->Contacts relationship. You can view it in action
by viewing an Opportunity and looking at the Contacts subpanel. The Role field exists in the relationship
itself (in the opportunities_contacts table). This is the functionality we are going to mimic.

For this exercise we want to be able to track when a Contact’s employment began and possibly ended for an Account.

  1. Lets create the two new date fields in the database that we ultimately
    want to be able to edit for a contact/account relationship.
    Open metadata/accounts_contactsMetaData.php and add date_start, date_end:

    .........
          , array('name' =>'account_id', 'type' =>'varchar', 'len'=>'36')
          , array ('name' => 'date_start','type' => 'date')
          , array ('name' => 'date_end','type' => 'date')
          , array ('name' => 'date_modified','type' => 'datetime')
    .........

    Note: This has to be done in the metadata version instead of custom/metadata since the file already exists as processing of metadata does not give precedence to the custom/metadata version

  2. Run Admin->Repair->Quick Repair and Rebuild
  3. You will be prompted to run the following SQL. Execute it and the fields will be added to the database:
    ALTER TABLE accounts_contacts   add column date_start date  NULL ,  add column date_end date  NULL ;
  4. Next we want to create the edit screen. Copy the following files in modulesContacts to custommodulesContacts and rename:
    • ContactOpportunityRelationship.php rename to ContactAccountRelationship.php
    • ContactOpportunityRelationshipEdit.html rename to ContactAccountRelationshipEdit.html
    • ContactOpportunityRelationshipEdit.php rename to ContactAccountRelationshipEdit.php
    • SaveContactOpportunityRelationship.php rename to SaveContactAccountRelationship.php
  5. Edit these 4 new files in custom/modules/Contacts and replace all occurrences of ContactOpportunity with ContactAccount
  6. Edit ContactOpportunityRelationshipEdit.php and change the path at the top to point to the custom directory:
    	require_once('custom/modules/Contacts/ContactAccountRelationship.php');

    And further below:

    	$xtpl=new XTemplate ('custom/modules/Contacts/ContactAccountRelationshipEdit.html');
  7. Now do the same with SaveContactOpportunityRelationship.php:
    	require_once('custom/modules/Contacts/ContactAccountRelationship.php');
  8. Edit ContactAccountRelationship.php to remove all mention of the opportunity and contact role fields and include our account and date start/end fields.Top section:
    	// Stored fields
    	var $id;
    	var $contact_id; var $date_start; var $date_end; var $account_id;
    
    	// Related fields
    	var $contact_name;
    	var $account_name;
    
    	var $table_name = "accounts_contacts";
    	var $object_name = "ContactAccountRelationship";
    
    	var $column_fields = Array("id"
    		,"contact_id"
     ,"account_id" ,"date_start" ,"date_end"
    		,'date_modified'
    		);

    Field Defs array:

    		var $field_defs = array (
           'id'=>array('name' =>'id', 'type' =>'char', 'len'=>'36', 'default'=>'')
          , 'contact_id'=>array('name' =>'contact_id', 'type' =>'char', 'len'=>'36', ) , 'account_id'=>array('name' =>'account_id', 'type' =>'char', 'len'=>'36',) , 'date_start'=>array('name' =>'date_start', 'type' =>'datetime') , 'date_end'=>array('name' =>'date_end', 'type' =>'datetime')
          , 'date_modified'=>array ('name' => 'date_modified','type' => 'datetime')
          , 'deleted'=>array('name' =>'deleted', 'type' =>'bool', 'len'=>'1', 'default'=>'0', 'required'=>true)
          );

    In the fill_in_additional_detail_fields() function replace the opportunity section with:

    		if(isset($this->account_id) && $this->account_id != "")
    		{
    			$query = "SELECT name from accounts where id='$this->account_id' AND deleted=0";
    			$result =$this->db->query($query,true," Error filling in additional detail fields: ");
    			// Get the id and the name.
    			$row = $this->db->fetchByAssoc($result);
    
    			if($row != null)
    			{
    				$this->account_name = $row['name'];
    			}
    		}
  9. Now we need to add the start/end dates to the subpanel and alter the edit button so that it goes
    to the edit relationship screen. Lets see how Opportunities does it by opening modulesOpportunitiesmetadatasubpaneldefs.php
  10. In subpaneldefs.php notice that for the Contacts subpanel it uses the ForOpportunities subpanel:
            'contacts' => array(
    			'order' => 30,
    			'module' => 'Contacts',
    			'sort_order' => 'asc',
    			'sort_by' => 'last_name, first_name', 'subpanel_name' => 'ForOpportunities',
    			'get_subpanel_data' => 'contacts',
    			'add_subpanel_data' => 'contact_id',
    			'title_key' => 'LBL_CONTACTS_SUBPANEL_TITLE',
    			'top_buttons' => array(
    				array('widget_class' => 'SubPanelTopCreateAccountNameButton'),
    				array('widget_class' => 'SubPanelTopSelectButton',
    					'popup_module' => 'Opportunities',
    					'mode' => 'MultiSelect',
    					'initial_filter_fields' => array('account_id' => 'account_id', 'account_name' => 'account_name'),
    				),
    			),
    		),
  11. Open modulesContactsmetadatasubpanelsForOpportunities.php and you’ll see the following:
    ......
    		'opportunity_role_fields'=>array(
    			'usage' => 'query_only',
    		),
    		'opportunity_role_id'=>array(
    			'usage' => 'query_only',
    		),
    		'opportunity_role'=>array(
    			'name'=>'opportunity_role',
    			'vname' => 'LBL_LIST_CONTACT_ROLE',
    			'width' => '10%',
    			'sortable'=>false,
    		),
    ......
    		//kbrill Bug#17483
    		'edit_button'=>array(
    			'vname' => 'LBL_EDIT_BUTTON',
    		    //'widget_class' => 'SubPanelEditButton',
    		    //C.L. Bug#18035, changed to use SubPanelEditRoleButton
    			'widget_class' => 'SubPanelEditRoleButton',
    			'role_id'=>'opportunity_role_id',
    		 	'module' => 'Contacts',
    			'width' => '5%',
    		),
    ......

    There are some query only fields that help get the relationship fields passed through. Below that is the edit button
    with a custom widget class that passes the relationship id through to the edit screen.
    We will need to create our own widget as well.

  12. The goal is to add the start/end dates to the Contacts subpanel of an Account record. We need to find
    out which subpanel layout to alter. Open modulesAccountsmetadatasubpaneldefs.php.
  13. Notice that for the Contacts subpanel which subpanel layout it uses:
    ......
    	'subpanel_name' => 'ForAccounts',
    .....
  14. Copy modulesContactsmetadatasubpanelsForAccounts.php to custommodulesContactsmetadatasubpanelsNote: Create the directory structure if it does not exist yet
  15. Change the edit_button definition to:
    		'edit_button'=>array(
    			'vname' => 'LBL_EDIT_BUTTON', 'widget_class' => 'SubPanelEditAccountContactButton', 'account_contact_id'=>'account_contact_id',
    		 	'module' => 'Contacts',
    			'width' => '5%',
    		),

    Notice our widget_class – we’ll get back to that

  16. Above the edit button add our eventual start/end dates along with some needed fields to pass the ID of the relationship to our edit screen:
    		'account_contact_fields'=>array(
    			'usage' => 'query_only',
    		),
    		'account_contact_id'=>array(
    			'usage' => 'query_only',
    		),
    		'account_contact_date_start'=>array(
    			'name'=>'account_contact_date_start',
    			'vname' => 'LBL_ACCOUNT_CONTACT_DATE_START',
    			'width' => '10%',
    			'sortable'=>false,
    		),
    		'account_contact_date_end'=>array(
    			'name'=>'account_contact_date_end',
    			'vname' => 'LBL_ACCOUNT_CONTACT_DATE_END',
    			'width' => '10%',
    			'sortable'=>false,
    		),

    Notice the account_contact_id. This tells Sugar to send over additional info to the widget.
    We need to setup the field to get the contact ID to pass over.

  17. Create a customExtensionmodulesContactsExtVardefsaccounts_contactsVardefs.phpNote: Create the directory structure if it does not exist yet
  18. Paste the following code in and run a Quick Repair & Rebuild:
    <?php
    $dictionary['Contact']['fields']['account_contact_fields'] = array (
    			'name' => 'account_contact_fields',
    			'rname' => 'id',
    			'relationship_fields'=>array('id' => 'account_contact_id', 'date_start' => 'account_contact_date_start', 'date_end' => 'account_contact_date_end'),
    			'vname' => 'LBL_ACCOUNT_CONTACT_FIELDS',
    			'type' => 'relate',
    			'link' => 'accounts',
    			'link_type' => 'relationship_info',
    			'join_link_name' => 'accounts_contacts',
    			'source' => 'non-db',
    			'importable' => false,
                'duplicate_merge'=> 'disabled',
    			'studio' => array('listview' => false),
    			'join_primary' => false, //this is key!!! See SugarBean.php and search for join_primary for more info
    );
    
    $dictionary['Contact']['fields']['account_contact_id'] = array (
    			'name' => 'account_contact_id',
    			'type' => 'varchar',
    			'source' => 'non-db',
    			'vname' => 'LBL_ACCOUNT_CONTACT_ID',
    			'studio' => array('listview' => false),
    );
    
    $dictionary['Contact']['fields']['account_contact_date_start'] = array (
    			'name' => 'account_contact_date_start',
    			'type' => 'date',
        		'source' => 'non-db',
    			'vname' => 'LBL_ACCOUNT_CONTACT_DATE_START',
    );
    
    $dictionary['Contact']['fields']['account_contact_date_end'] = array (
    			'name' => 'account_contact_date_end',
    			'type' => 'date',
        		'source' => 'non-db',
    			'vname' => 'LBL_ACCOUNT_CONTACT_DATE_END',
    );

    Note: relationship_fields helps map the query result to the ContactAccountRelationship bean

  19. Now we need to create our own widget class based on the SubPanelEditRoleButton class.
    Copy includegenericSugarWidgets to customincludegenericSugarWidgets
    and rename to SugarWidgetSubPanelEditAccountContactButton.php

    Note: Create the directory structure if it does not exist yet

  20. Edit SugarWidgetSubPanelEditAccountContactButton.php and change these 2 lines:
    	. '&action=' . 'ContactOpportunityRelationshipEdit'
    	. '&record=' . $layout_def['fields']['OPPORTUNITY_ROLE_ID']

    To:

    	. '&action=' . 'ContactAccountRelationshipEdit'
    	. '&record=' . $layout_def['fields']['ACCOUNT_CONTACT_ID']
  21. Open an Account record in SugarCRM and scroll down to the Contacts subpanel.
    Notice that we now have those pesky labels that need to be translated.
  22. Create and add the following code to customExtensionmodulesContactsExtLanguageen_us.Accounts_Contacts_StartEnd.phpNote: Create the directory structure if it does not exist yet. Prepend with the language code of your choice.
    <?php
    $mod_strings['LBL_CONTACT_ACCOUNT_FORM_TITLE'] = 'Contact-Account';
    $mod_strings['LBL_ACCOUNT_CONTACT_DATE_START'] = 'Start Date';
    $mod_strings['LBL_ACCOUNT_CONTACT_DATE_END'] = 'End Date';
  23. Run Repair & Rebuild and refresh the Account screen. The labels should now be translated correctly.
  24. For a Contact in the subpanel click on the edit button. We have an edit screen! But there is work to be done yet.Note: If the contact name doesn’t show on the edit screen go back and hover over the edit link.
    You should see a record= followed by a guid. If not, something in from a previous step was done incorrectly.
  25. To get the Account to show up correctly edit ContactAccountRelationshipEdit.html.
    Change {MOD.LBL_OPP_NAME} to {MOD.LBL_ACCOUNT_NAME} and remove the opportunity select and replace with {ACCOUNT.NAME}

    Before:

    	<td width="15%" scope="row"><slot>{MOD.LBL_OPP_NAME}</slot></td>
    	<td width="35%" ><slot>
    		<input type="text" class='sqsEnabled' id='opportunity_name' name='opportunity_name' rows="2" cols="30" value="{OPPORTUNITY.NAME}" />
    		<input id='opportunity_id' name='opportunity_id' type="hidden" value='{OPPORTUNITY.ID}'>
    		<input type="button" name="btn1" class="button"
    			title="{APP.LBL_SELECT_BUTTON_TITLE}"
    			accesskey="{APP.LBL_SELECT_BUTTON_KEY}"
    			value='{APP.LBL_SELECT_BUTTON_LABEL}'
    			onclick='open_popup("Opportunities",600,400,"",true,false,{"call_back_function":"set_return","form_name":"EditView","field_to_name_array":{"id":"opportunity_id","name":"opportunity_name"}});' />
    	</slot></td>

    After:

    	<td width="15%" scope="row"><slot>{MOD.LBL_ACCOUNT_NAME}</slot></td>
    	<td width="35%" ><slot>{ACCOUNT.NAME}</slot></td>

    Note: Yes, we are removing the ability to change the actual relationship. It will save trouble down the road
    if we do not allow the Contact or Account to be changed.

  26. Edit ContactAccountRelationshipEdit.php and change
    	safe_map('opportunity_name', $focus);
    	safe_map('opportunity_id', $focus);

    To:

    	safe_map('account_name', $focus);
    	safe_map('account_id', $focus);

    As well as:

    $xtpl->assign("OPPORTUNITY",$oppName = Array("NAME" => $focus->opportunity_name, "ID" => $focus->opportunity_id));

    To:

    $xtpl->assign("ACCOUNT",$accName = Array("NAME" => $focus->account_name);

    Refresh the edit screen and the Account name now shows.

  27. Edit ContactAccountRelationshipEdit.html, remove the contact select, and replace with {CONTACT.NAME}
    	<td width="15%" scope="row"><slot>{MOD.LBL_CONTACT_NAME}</slot></td>
    	<td width="35%" ><slot>{CONTACT.NAME}</slot></td>

    Refresh the edit screen and the Contact name now shows without the select box.

  28. Time to fix the title at the top of the edit page. Edit ContactAccountRelationshipEdit.php.
    Change $oppName['NAME'] to $accName['NAME']. Change $mod_strings['LBL_CONTACT_OPP_FORM_TITLE'].” ” to $mod_strings['LBL_CONTACT_ACCOUNT_FORM_TITLE'].”: “
    Refresh the edit screen and the title bar should now display correctly.
  29. Now add our start/end dates. Edit ContactAccountRelationshipEdit.html
    Change Role field from:

    	<tr>
    	<td scope="row"><slot>{MOD.LBL_CONTACT_ROLE}</slot></td>
    	<td ><slot><select name='contact_role'>{CONTACT_ROLE_OPTIONS}</select></slot></td>
    	<td scope="row"><slot> </slot></td>
    	<td ><slot> </slot></td>
    	</tr>

    To:

    	<tr>
    	<td scope="row"><slot>{MOD.LBL_ACCOUNT_CONTACT_DATE_START}:</slot></td>
    	<td ><slot><input name='date_start' id='jscal_start_date' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');" tabindex='8' size='11' maxlength='10' type="text" value="{DATE_START}"> <img src="index.php?entryPoint=getImage&themeName={THEME}&imageName=jscalendar.gif" alt="{CALENDAR_DATEFORMAT}"  id="start_date_jscal_trigger" align="absmiddle"> <span class="dateFormat">{USER_DATEFORMAT}</span></slot></td>
    	<td scope="row"><slot>{MOD.LBL_ACCOUNT_CONTACT_DATE_END}:</slot></td>
    	<td ><slot><input name='date_end' id='jscal_end_date' onblur="parseDate(this, '{CALENDAR_DATEFORMAT}');" tabindex='8' size='11' maxlength='10' type="text" value="{DATE_END}"> <img src="index.php?entryPoint=getImage&themeName={THEME}&imageName=jscalendar.gif" alt="{CALENDAR_DATEFORMAT}"  id="end_date_jscal_trigger" align="absmiddle"> <span class="dateFormat">{USER_DATEFORMAT}</span></slot></td>
    	</tr>
  30. In the same file right below the closing form tag add following script to get the calendar popup to work correctly:
    <script type="text/javascript">
    	Calendar.setup ({
    		inputField : "jscal_start_date", ifFormat : "{CALENDAR_DATEFORMAT}", showsTime : false, button : "start_date_jscal_trigger", singleClick : true, step : 1
    	});
    
    	Calendar.setup ({
    		inputField : "jscal_end_date", ifFormat : "{CALENDAR_DATEFORMAT}", showsTime : false, button : "end_date_jscal_trigger", singleClick : true, step : 1
    	});
    
    </script>
  31. Edit ContactAccountRelationshipEdit.php and remove the following code that references the contact role field:
    $xtpl->assign("CONTACT_ROLE_OPTIONS", get_select_options_with_id($app_list_strings['opportunity_relationship_type_dom'], $focus->contact_role));

    And replace with:

    global $timedate;
    $xtpl->assign('THEME', SugarThemeRegistry::current()->__toString());
    $xtpl->assign('USER_DATEFORMAT', '('.$timedate->get_user_date_format().')');
    $xtpl->assign("CALENDAR_DATEFORMAT", $timedate->get_cal_date_format());
    $xtpl->assign("DATE_START", $timedate->to_display_date($focus->date_start, false));
    $xtpl->assign("DATE_END", $timedate->to_display_date($focus->date_end, false));
  32. While in this file remove the following two blocks of code:
    require_once('include/QuickSearchDefaults.php');
    $qsd = new QuickSearchDefaults();
    $sqs_objects = array('opportunity_name' => $qsd->getQSParent());
    $sqs_objects['opportunity_name']['populate_list'] = array('opportunity_name', 'opportunity_id');
    $quicksearch_js = '<script type="text/javascript" language="javascript">sqs_objects = ' . $json->encode($sqs_objects) . '</script>';
    echo $quicksearch_js;

    And:

    $javascript = new javascript();
    $javascript->setFormName('EditView');
    $javascript->setSugarBean($focus);
    $javascript->addToValidateBinaryDependency('opportunity_name', 'alpha', $app_strings['ERR_SQS_NO_MATCH_FIELD'] . $mod_strings['LBL_OPP_NAME'], 'false', '', 'opportunity_id');
    echo $javascript->getScript();

And that is it! At this point you should be able to edit the start/end dates and save.

17 responses to Adding Fields to a Relationship/Subpanel

  1. 

    I am new to SugarCRM and I would appreciate your assistance:

    I am dealing with trying to do this on a relationship between Address Locations (a custom module) and Contacts so that when I am on the Contact module, I see the mailstop (the custom field in the relationship) in the Address Locations subtab.(So my Contacts is your Account and my Address Locations is your Contacts)
    I followed these instructions carefully, I see the field in the database, the label is in the subpanel but I do not see the contents of the field in the subpanel.I am dealing with an existing custom relationship, so the subpanel already exists, I just added the three fields necessary for the relationship field (mailstop) to display.

    I think my problem is in the vardef.
    There was already one as follows:
    $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_CONTACTS_TITLE’,);

    I replaced it with:
    $dictionary['addlo_Address_Locations']['fields']['contact_address_fields']= array(        ‘name’ => ‘contact_address_fields’,        ‘rname’ => ‘id’,        ‘relationship_fields’=>array(‘id’=> ‘contact_address_id’, ‘mailstop’ => ‘contact_address_mailstop’),        ‘vname’ => ‘LBL_CONTACT_ADDRESS_ID’,        ‘type’ => ‘relate’,        ‘link’ => ‘contacts’,        ‘link_type’ => ‘relationship_info’,        ‘join_link_name’ => ‘addlo_addreons_contact_c’, // should this be the name of the table containing the relationship????        ‘source’ => ‘non-db’,        ‘importable’ => false,        ‘duplicate_merge’ => ‘disabled’,        ‘studio’ => array(‘listview’=>false),        ‘join_primary’ => false, //this is key!!!See SugarBean.php and search for join_primary for more info);$dictionary['addlo_Address_Locations']['fields']['contact_address_id']= array (        ‘name’=>’contact_address_id’,        ‘type’=>’varchar’,        ‘source’ => ‘non-db’,        ‘vname’ => ‘LBL_CONTACT_ADDRESS_ID’,        ‘studio’ => array (‘listview’=>’false’),);$dictionary['addlo_Address_Locations']['fields']['contact_address_mailstop'] = array (        ‘name’ => ‘contact_address_mailstop’,        ‘type’ => ‘varchar’,        ‘source’ => ‘non-db’,        ‘vname’ => ‘LBL_CONTACT_ADDRESS_MAILSTOP’,);

  2. 

    Can these fields be updated with SOAP?

  3. 

    Hi to All,

    I have two modules with one-many relationship. On parent module detail view, there is a subpanel of child modules, both module are derived from base template. I want to show the last modified_user_name instead of modified_user_id in my subpanel. Please help.

    And I have done something regarding things but not sure.

    I have added the following code in child’s subpanel matadata file.

    ‘modified_user_name’=>array(
                ‘name’=>’modified_user_name’,
                 ‘module’ => ‘Users’,
                 ‘target_record_key’ => ‘modified_user_id’,
                 ‘target_module’ => ‘Users’,           
                 ‘vname’ => ‘LBL_LIST_MODIFIED_USER_NAME’,
                ‘width’ => ’22%’,
                ‘sortable’=>false,
    ),

    The above code work for me. But I am not sure whether, I have to add this non-db (i.e. modified_user_name) field to child vardef file or not.

    Please share your view point.

    Waiting for response.
    Hrishikesh Mishra

     

  4. 
    Youssef Sabbah July 15, 2012 at 10:13 am

    Hi to all,

    I successfully used the code above to customize the accounts_contacts relationship fields, show the new fields in a subpanel and to edit the additional fields in the relationship. I am using SugarCRM Pro 6.4.2.

    However, when i add a boolean field in the accounts_contacts relationship (for example ismanagement to tack if a contact is in the management team of the account), i have problems in showing the right value of the checkbox in the subpanel and the values for this field are always deleted after editing the relationship record.

    Any advice to properly add boolean fields to the relationship and be able to edit the value?

    Thank you in advance,

  5. 

    Hi Jason,

    This is exactly something I’ve been looking for for ages. But unfortunately I can’t seem to get it to work…

    My problem is that I need to add some vars to a relationship between Contacts and a module called Teams. Lhs is Teams, but it is a many 2 many relationship.

    Everthing seems to work but I can’t get the ID to load into the edit button. When I dump Layoutdef, the ID of the relationship isn’t loaded.

    What am I messing up here?

    Thanks!

    • 

      Perhaps you could detail your code changes and we can help dig into the problem?

      • 

        Hi John,

        I’m really breaking my brains about this one, so any help is really appreciated!

        Some more information.

        I have a custom module called gd_Teams.

        The relationship table to contacts is gd_teams_contacts_c. The fields (including the 2 custom fields) are:

        id, date_modified, deleted, gd_teams_contactsgd_teams_ida, gd_teams_contactscontacts_idb, gd_role and gd_perc

        gd_teams is the left-handed module, and therefore comparable to accounts in the example.
        gd_teams_contactsgd_teams_ida holds the gd_teams_id and gd_teams_contactscontacts_idb holds the contacts id.

        The problem is that the record in the relationship table doesn’t show up in the layout_def array on the gd_teams page.

        I think that the problem must be in the ContactGdteamRelationship.php, the gd_teams_contactsVardefs.php, the SugarWidgetSubPanelGdteamContactEditButton.php or the subpanel file default.php (offcourse in custom).

        ContactGdteamRelationship class:

        class ContactGdteamRelationship extends SugarBean {
            // Stored fields
            var $id;
            var $contact_id;
            var $gd_role;
            var $gd_team_id;
            var $gd_perc;
        
            // Related fields
            var $contact_name;
            var $gd_team_name;
        
            var $table_name = "gd_teams_contacts_c";
            var $object_name = "ContactGdteamRelationship";
            var $column_fields = Array("id"
                ,"contact_id"
                ,"gd_team_id"
                ,"gd_role"
                ,"gd_perc"
                ,'date_modified'
                );
        
            var $new_schema = true;
        
            var $additional_column_fields = Array();
                var $field_defs = array (
               'id'=&gt;array('name' =&gt;'id', 'type' =&gt;'char', 'len'=&gt;'36', 'default'=&gt;'')
              , 'contact_id'=&gt;array('name' =&gt;'contact_id', 'type' =&gt;'char', 'len'=&gt;'36', )
              , 'gd_team_id'=&gt;array('name' =&gt;'gd_team_id', 'type' =&gt;'char', 'len'=&gt;'36',)
              , 'gd_role'=&gt;array('name' =&gt;'gd_role', 'type' =&gt;'char', 'len'=&gt;'50')
              , 'gd_perc'=&gt;array('name' =&gt;'gd_perc', 'type' =&gt;'char', 'len'=&gt;'50')
              , 'date_modified'=&gt;array ('name' =&gt; 'date_modified','type' =&gt; 'datetime')
              , 'deleted'=&gt;array('name' =&gt;'deleted', 'type' =&gt;'bool', 'len'=&gt;'1', 'default'=&gt;'0', 'required'=&gt;true)
              );
            function ContactGdteamRelationship() {
                $this-&gt;db = DBManagerFactory::getInstance();
                $this-&gt;dbManager = DBManagerFactory::getInstance();
        
                $this-&gt;disable_row_level_security =true;
        
                }
        
            function fill_in_additional_detail_fields()
            {
                global $locale;
                if(isset($this-&gt;contact_id) &amp;&amp; $this-&gt;contact_id != "")
                {
                    $query = "SELECT first_name, last_name from contacts where id='$this-&gt;contact_id' AND deleted=0";
                    $result =$this-&gt;db-&gt;query($query,true," Error filling in additional detail fields: ");
                    // Get the id and the name.
                    $row = $this-&gt;db-&gt;fetchByAssoc($result);
        
                    if($row != null)
                    {
                        $this-&gt;contact_name = $locale-&gt;getLocaleFormattedName($row['first_name'], $row['last_name']);
                    }
                }
        
                if(isset($this-&gt;gd_team_id) &amp;&amp; $this-&gt;gd_team_id != "")
                {
                    $query = "SELECT name from gd_teams where id='$this-&gt;gd_team_id' AND deleted=0";
                    $result =$this-&gt;db-&gt;query($query,true," Error filling in additional detail fields: ");
                    // Get the id and the name.
                    $row = $this-&gt;db-&gt;fetchByAssoc($result);
        
                    if($row != null)
                    {
                        $this-&gt;gd_team_name = $row['name'];
                    }
                }
        
            }
        }
        

        —————————————————————————————————————————
        Vardefs:

        $dictionary['Contact']['fields']['gd_team_contact_fields'] = array (
        ‘name’ => ‘gd_team_contact_fields’,
        ‘rname’ => ‘id’,
        ‘relationship_fields’=>array(‘id’ => ‘gd_team_contact_id’, ‘gd_role’ => ‘gd_team_contact_gd_role’, ‘gd_perc’ => ‘gd_team_contact_gd_perc’),
        ‘vname’ => ‘LBL_GD_TEAM_CONTACT_FIELDS’,
        ‘type’ => ‘relate’,
        ‘link’ => ‘gd_teams’,
        ‘link_type’ => ‘relationship_info’,
        ‘join_link_name’ => ‘gd_teams_contacts_c’,
        ‘source’ => ‘non-db’,
        ‘importable’ => false,
        ‘duplicate_merge’=> ‘disabled’,
        ‘studio’ => array(‘listview’ => false),
        ‘join_primary’ => false, //this is key!!! See SugarBean.php and search for join_primary for more info
        );

        $dictionary['Contact']['fields']['gd_team_contact_id'] = array (
        ‘name’ => ‘gd_team_contact_id’,
        ‘type’ => ‘varchar’,
        ‘source’ => ‘non-db’,
        ‘vname’ => ‘LBL_GD_TEAM_CONTACT_ID’,
        ‘studio’ => array(‘listview’ => false),
        );

        $dictionary['Contact']['fields']['gd_team_contact_gd_role'] = array (
        ‘name’ => ‘gd_team_contact_gd_role’,
        ‘type’ => ‘varchar’,
        ‘source’ => ‘non-db’,
        ‘vname’ => ‘LBL_GD_TEAM_CONTACT_ROLE’,
        );

        $dictionary['Contact']['fields']['gd_team_contact_gd_perc'] = array (
        ‘name’ => ‘gd_team_contact_gd_perc’,
        ‘type’ => ‘varchar’,
        ‘source’ => ‘non-db’,
        ‘vname’ => ‘LBL_GD_TEAM_CONTACT_PERC’,
        );
        ————————————————————————-
        Edit button:

        class SugarWidgetSubPanelEditGdteamContactButton extends SugarWidgetField
        {
        function displayHeaderCell(&$layout_def)
        {
        return ‘ ‘;
        }

        function displayList(&$layout_def)
        {
        print””;
        print_r($layout_def);
        print””;
        global $app_strings;
        $href = ‘index.php?module=’ . $layout_def['module']
        . ‘&action=’ . ‘ContactGdteamRelationshipEdit’
        . ‘&record=’ . $layout_def['fields']['GD_TEAM_CONTACT_ID']
        . ‘&return_module=’ . $_REQUEST['module']
        . ‘&return_action=’ . ‘DetailView’
        . ‘&return_id=’ . $_REQUEST['record'];

        $edit_icon_html = SugarThemeRegistry::current()->getImage( ‘edit_inline’, ‘align=”absmiddle” border=”0″‘,null,null,’.gif’,$app_strings['LNK_EDIT']);
        //based on listview since that lets you select records
        if($layout_def['ListView']){
        return ‘‘ . $edit_icon_html . ‘ ‘ . $app_strings['LNK_EDIT'] .’ ‘;
        }else{
        return ”;
        }
        }
        }

        ——————————————————————————–
        Subpanel file:
        $subpanel_layout = array(
        ‘top_buttons’ => array(
        array(‘widget_class’ => ‘SubPanelTopCreateButton’),
        array(‘widget_class’ => ‘SubPanelTopSelectButton’, ‘popup_module’ => ‘Contacts’),
        ),

        ‘where’ => ”,

        ‘list_fields’ => array(
        ‘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’,
        ),
        ‘name’=>array(
        ‘name’=>’name’,
        ‘vname’ => ‘LBL_LIST_NAME’,
        ‘sort_by’ => ‘last_name’,
        ‘sort_order’ => ‘asc’,
        ‘widget_class’ => ‘SubPanelDetailViewLink’,
        ‘module’ => ‘Contacts’,
        ‘width’ => ’23%’,
        ),
        ‘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,
        ),
        ‘account_id’=>array(
        ‘usage’=>’query_only’,

        ),
        ‘email1′=>array(
        ‘name’=>’email1′,
        ‘vname’ => ‘LBL_LIST_EMAIL’,
        ‘widget_class’ => ‘SubPanelEmailLink’,
        ‘width’ => ’10%’,
        ‘sortable’=>false,
        ),
        ‘phone_work’=>array (
        ‘name’=>’phone_work’,
        ‘vname’ => ‘LBL_LIST_PHONE’,
        ‘width’ => ’15%’,
        ),
        ‘gd_team_contact_fields’=>array (
        ‘usage’=>’query_only’,
        ),
        ‘gd_team_contact_id’=>array (
        ‘usage’=>’query_only’,
        ),
        ‘gd_team_contact_gd_role’=> array (
        ‘name’=>’gd_team_contact_gd_role’,
        ‘vname’=>’LBL_GD_TEAM_CONTACT_GD_ROLE’,
        ‘width’=>’10%’,
        ‘sortable’=>’false’,
        ),
        ‘gd_team_contact_gd_perc’=> array (
        ‘name’=>’gd_team_contact_gd_perc’,
        ‘vname’=>’LBL_GD_TEAM_CONTACT_GD_PERC’,
        ‘width’=>’10%’,
        ‘sortable’=>’false’,
        ),
        ‘edit_button’=>array(
        ‘vname’ => ‘LBL_EDIT_BUTTON’,
        ‘widget_class’ => ‘SubPanelEditGdteamContactButton’,
        ‘gd_team_contact_id’=>’gd_team_contact_id’,
        ‘module’ => ‘Contacts’,
        ‘width’ => ’5%’,
        ),
        ‘remove_button’=>array(
        ‘vname’ => ‘LBL_REMOVE’,
        ‘widget_class’ => ‘SubPanelRemoveButton’,
        ‘module’ => ‘Contacts’,
        ‘width’ => ’5%’,
        ),
        ),
        );

        Any suggestions?

        Best regards

        Guus Derks

Trackbacks and Pingbacks:

  1. Attributes on a relationship - SugarCRM Forums - October 6, 2011

    [...] [...]

  2. Opportunity Roles and reporting. - SugarCRM Forums - October 17, 2011

    [...] [...]

  3. qualifying a relationship - SugarCRM Forums - October 26, 2011

    [...] [...]

  4. "Contacts" field which changes in each "Case" - June 29, 2012

    [...] [...]

  5. Recording Account/Contact/Lead role(s) - December 12, 2012

    [...] [...]

  6. create relate field of role module in users module popupdef - January 23, 2013

    [...] [...]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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