adding relationship field

Question

Hi , 
 
 I have two modules. admb_partner and contacts. Between them exists an one to many relationship. I would like to create a checkbox field on the relationship. I looked to the opportunity role functionality but I can't seem to get it to work properly for my usecase 
 I have the current files in place: 
 custom/Extension/modules/Contacts/Ext/Vardefs/sugarfield_admb_partner_contact_main_mandatory_c.php 
 $dictionary['Contact']['fields']['admb_partner_contact_main_mandatory_c'] = array(
 'name' => 'admb_partner_contact_main_mandatory_c',
 'type' => 'bool',
 'source'=> 'non-db',
 'vname' => 'LBL_ADMB_PARTNER_CONTACT_MAIN_MANDATORY_C',
 'link' => 'admb_partner_contacts',
 'rname_link' => 'admb_partner_contact_main_mandatory_c',
 'massupdate' => false
); 
 
 www/custom/metadata/admb_partner_contactsMetaData.php 
 <?php
// created: 2018-01-03 16:24:52
$dictionary["admb_partner_contacts"] = array (
 'true_relationship_type' => 'one-to-many',
 'relationships' => 
 array (
 'admb_partner_contacts' => 
 array (
 'lhs_module' => 'ADMB_Partner',
 'lhs_table' => 'admb_partner',
 'lhs_key' => 'id',
 'rhs_module' => 'Contacts',
 'rhs_table' => 'contacts',
 'rhs_key' => 'id',
 'relationship_type' => 'many-to-many',
 'join_table' => 'admb_partner_contacts_c',
 'join_key_lhs' => 'admb_partner_contactsadmb_partner_ida',
 'join_key_rhs' => 'admb_partner_contactscontacts_idb',
 'rel_fields' => array(
 'admb_partner_contact_main_mandatory_c' => array(
 'type' => 'bool',
 'default'=> 0,
 )
 )
 ),
 ),
 'table' => 'admb_partner_contacts_c',
 'fields' => 
 array (
 'id' => 
 array (
 'name' => 'id',
 'type' => 'id',
 ),
 'date_modified' => 
 array (
 'name' => 'date_modified',
 'type' => 'datetime',
 ),
 'deleted' => 
 array (
 'name' => 'deleted',
 'type' => 'bool',
 'default' => 0,
 ),
 'admb_partner_contactsadmb_partner_ida' => 
 array (
 'name' => 'admb_partner_contactsadmb_partner_ida',
 'type' => 'id',
 ),
 'admb_partner_contactscontacts_idb' => 
 array (
 'name' => 'admb_partner_contactscontacts_idb',
 'type' => 'id',
 ),
 'admb_partner_contact_main_mandatory_c'=>
 array (
 'name' => 'admb_partner_contact_main_mandatory_c',
 'type' => 'bool',
 'studio' => false,
 'source' => 'non-db',
 'vname' => 'LBL_ADMB_PARTNER_CONTACT_MAIN_MANDATORY_C',
 'rname_link' => 'admb_partner_contact_main_mandatory_c',
 'default' => 0,

 )
 ),
 'indices' => 
 array (
 0 => 
 array (
 'name' => 'idx_admb_partner_contacts_pk',
 'type' => 'primary',
 'fields' => 
 array (
 0 => 'id',
 ),
 ),
 1 => 
 array (
 'name' => 'idx_admb_partner_contacts_ida1_deleted',
 'type' => 'index',
 'fields' => 
 array (
 0 => 'admb_partner_contactsadmb_partner_ida',
 1 => 'deleted',
 ),
 ),
 2 => 
 array (
 'name' => 'idx_admb_partner_contacts_idb2_deleted',
 'type' => 'index',
 'fields' => 
 array (
 0 => 'admb_partner_contactscontacts_idb',
 1 => 'deleted',
 ),
 ),
 3 => 
 array (
 'name' => 'admb_partner_contacts_alt',
 'type' => 'alternate_key',
 'fields' => 
 array (
 0 => 'admb_partner_contactscontacts_idb',
 ),
 ),
 ),
);
 
 When I now repair and rebuild I would expect an update on the relationship tabel. This didn't happen.But when I open a partner I do get an mysql error that says that the field is not found on the relationshp table. So i added myself to the tabel using alter query. After I added the specified field to the override subpanel using the following entry in the fields list I can see the field in the subpanel but any changed made are not saved so it seems. But when I add the value in the database directly the value is read and displayed correctly. So the big question is.. What am I missing? 
 3 =>
 array (
 'name' => 'admb_partner_contact_main_mandatory_c',
 'label' => 'LBL_MAIN_MANDATORY',
 'enabled' => true,
 'default' => true,
 ), 
 
 Also another question is, is it possibel to overwrite/enhnce the accounts_contacts relationship with a custom field?

Jeroen Somhorst · Accepted Answer

Hi Jeroen,

I agree with Andre, as he said the relationship fields are more apt for M-M relationships not for 1-M relationship. Because if you have a 1-M relationship there is not point in using relationship fields. You can directly create a field in the main table of the module.

Anyhow if you want to create a relationship field you are missing one thing in your code.

Actually, the below code should be added to the relationship file created in the Vardefs folder. Generally, when a 1-M relationship is created it generates a file in module Vardefs. please check that and add this code to that file.

custom/Extension/modules/<moduleName>/Ext/Vardefs/<filename.php>

add this piece of code at the end of the file.

'rel_fields' => array(
'admb_partner_contact_main_mandatory_c' => array(
'type' => 'bool',
)
)

You have to define this field in the field indexes array in the custom/metadata/admb_partner_contactsMetaData.php, so your code in this file should look like below.

<?php
// created: 2018-01-03 16:24:52
$dictionary["admb_partner_contacts"] = array (
'true_relationship_type' => 'one-to-many',
'relationships' =>
array (
'admb_partner_contacts' =>
array (
'lhs_module' => 'ADMB_Partner',
'lhs_table' => 'admb_partner',
'lhs_key' => 'id',
'rhs_module' => 'Contacts',
'rhs_table' => 'contacts',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'admb_partner_contacts_c',
'join_key_lhs' => 'admb_partner_contactsadmb_partner_ida',
'join_key_rhs' => 'admb_partner_contactscontacts_idb',
),
),
'table' => 'admb_partner_contacts_c',
'fields' =>
array (
'id' =>
array (
'name' => 'id',
'type' => 'id',
),
'date_modified' =>
array (
'name' => 'date_modified',
'type' => 'datetime',
),
'deleted' =>
array (
'name' => 'deleted',
'type' => 'bool',
'default' => 0,
),
'admb_partner_contact_main_mandatory_c' =>
array (
'name' => 'admb_partner_contact_main_mandatory_c',
'type' => 'bool',
),
'admb_partner_contactsadmb_partner_ida' =>
array (
'name' => 'admb_partner_contactsadmb_partner_ida',
'type' => 'id',
),
'admb_partner_contactscontacts_idb' =>
array (
'name' => 'admb_partner_contactscontacts_idb',
'type' => 'id',
),
'admb_partner_contact_main_mandatory_c'=>
array (
'name' => 'admb_partner_contact_main_mandatory_c',
'type' => 'bool',
'studio' => false,
'source' => 'non-db',
'vname' => 'LBL_ADMB_PARTNER_CONTACT_MAIN_MANDATORY_C',
'rname_link' => 'admb_partner_contact_main_mandatory_c',
'default' => 0,

)
),
'indices' =>
array (
0 =>
array (
'name' => 'idx_admb_partner_contacts_pk',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'idx_admb_partner_contacts_ida1_deleted',
'type' => 'index',
'fields' =>
array (
0 => 'admb_partner_contactsadmb_partner_ida',
1 => 'deleted',
),
),
2 =>
array (
'name' => 'idx_admb_partner_contacts_idb2_deleted',
'type' => 'index',
'fields' =>
array (
0 => 'admb_partner_contactscontacts_idb',
1 => 'deleted',
),
),
3 =>
array (
'name' => 'admb_partner_contacts_alt',
'type' => 'alternate_key',
'fields' =>
array (
0 => 'admb_partner_contactscontacts_idb',
),
),
),
);

So to conclude, to create a relationship field, you need to modify three files.

1. The new file for creating the field in the Vardefs folder.

2. Modifying the relationship file in the Vardefs folder.

3. Modifying the file in the custom/metadata folder.

Hope this information helps

Thanks & Regards,

PK,

https://www.bhea.com

Jeroen Somhorst · Answer

Three considerations: 
 
 I'm not sure such a feature works for 1:M relationship, its context may require a M:M one 
 Take a look at field 'opportunity_role' at Contacts vardefs and 'contact_role' at Opportunities vardefs. I guess you missed some attributes. 
 Perhaps the suffix '_c' is not required.

Regards