Cannot add relate field [relationship field] on the custom module record save/update

Question

Hello,

Please forgive for my language and terminologies used if are incorrect

below is what I am struggling with

I have a custom module name COF

I wanted to have a relationship with opportunities 1 : M [1 Opportunity can contain many COFs]

I wanted to have a relationship with Accounts1 : M [1 Account can contain many COFs] Billing Account

I wanted to have a relationship with Accounts1 : M [1 Account can contain many COFs] Shipping Account

I wanted to have a relationship with Contacts1 : M [1 Contact can contain many COFs] Billing Contact

I wanted to have a relationship with Contacts1 : M [1 Contact can contain many COFs] Shipping Contact

I have created relationships via script

PFB below script

\custom\metadata\custom_relationships_cof.php

<?php

$dictionary["itb_cof_billto_accounts"] = array (
 'relationships' =>
 array (
   'itb_cof_billto_accounts' =>
   array (
     'lhs_module' => 'Accounts',
     'lhs_table' => 'accounts',
     'lhs_key' => 'id',
     'rhs_module'=> 'itb_cof',
     'rhs_table'=> 'itb_cof',
     'rhs_key' => 'id',
     'relationship_type' => 'many-to-many',
	 'true_relationship_type' => 'one-to-many',
     'join_table' => 'itb_cof_accounts',
     'join_key_lhs' => 'account_id',
     'join_key_rhs' => 'itb_cof_id',
	 'relationship_role_column' => 'account_role',
     'relationship_role_column_value' => 'Bill To',
   ),
 ),
 'table' => 'itb_cof_accounts',
 'fields' =>
 array (
   'id' =>
   array (
     'name' => 'id',
     'type' => 'id',
   ),
   'date_modified' =>
   array (
     'name' => 'date_modified',
     'type' => 'datetime',
   ),
   'deleted' =>
   array (
     'name' => 'deleted',
     'type' => 'bool',
     'len' => '1',
     'default' => '0',
     'required' => true,
   ),
   'account_id' =>
   array (
	 'name' => 'account_id',
     'type' => 'id',
     
   ),
   'itb_cof_id' =>
   array (
     'name' => 'itb_cof_id',
     'type' => 'id',
   ),
   'account_role' =>
   array (
     'name' => 'account_role',
     'type' => 'varchar',
     'len' => '20',
   ),
 ),
);

$dictionary["itb_cof_shipto_accounts"] = array (
 'relationships' =>
 array (
   'itb_cof_shipto_accounts' =>
   array (
     'lhs_module' => 'Accounts',
     'lhs_table' => 'accounts',
     'lhs_key' => 'id',
     'rhs_module'=> 'itb_cof',
     'rhs_table'=> 'itb_cof',
     'rhs_key' => 'id',
     'relationship_type' => 'many-to-many',
	 'true_relationship_type' => 'one-to-many',
     'join_table' => 'itb_cof_accounts',
     'join_key_lhs' => 'account_id',
     'join_key_rhs' => 'itb_cof_id',
	 'relationship_role_column' => 'account_role',
     'relationship_role_column_value' => 'Ship To',
   ),
 ),
 'table' => 'itb_cof_accounts',
 'fields' =>
 array (
   'id' =>
   array (
     'name' => 'id',
     'type' => 'id',
   ),
   'date_modified' =>
   array (
     'name' => 'date_modified',
     'type' => 'datetime',
   ),
   'deleted' =>
   array (
     'name' => 'deleted',
     'type' => 'bool',
     'len' => '1',
     'default' => '0',
     'required' => true,
   ),
   'account_id' =>
   array (
	 'name' => 'account_id',
     'type' => 'id',
     
   ),
   'itb_cof_id' =>
   array (
     'name' => 'itb_cof_id',
     'type' => 'id',
   ),
   'account_role' =>
   array (
     'name' => 'account_role',
     'type' => 'varchar',
     'len' => '20',
   ),
 ),
);


$dictionary["itb_cof_contacts_shipto"] = array (
 'relationships' =>
 array (
   'itb_cof_contacts_shipto' =>
   array (
     'lhs_module' => 'Contacts',
     'lhs_table' => 'contacts',
     'lhs_key' => 'id',
     'rhs_module'=> 'itb_cof',
     'rhs_table'=> 'itb_cof',
     'rhs_key' => 'id',
     'relationship_type' => 'many-to-many',
	 'true_relationship_type' => 'one-to-many',
     'join_table' => 'itb_cof_contacts',
     'join_key_lhs' => 'contact_id',
     'join_key_rhs' => 'itb_cof_id',
	 'relationship_role_column' => 'contact_role',
     'relationship_role_column_value' => 'Ship To',
   ),
 ),
 'table' => 'itb_cof_contacts',
 'fields' =>
 array (
   'id' =>
   array (
     'name' => 'id',
     'type' => 'id',
   ),
   'date_modified' =>
   array (
     'name' => 'date_modified',
     'type' => 'datetime',
   ),
   'deleted' =>
   array (
     'name' => 'deleted',
     'type' => 'bool',
     'len' => '1',
     'default' => '0',
     'required' => true,
   ),
   'contact_id' =>
   array (
	 'name' => 'contact_id',
     'type' => 'id',
     
   ),
   'itb_cof_id' =>
   array (
     'name' => 'itb_cof_id',
     'type' => 'id',
   ),
   'contact_role' =>
   array (
     'name' => 'contact_role',
     'type' => 'varchar',
     'len' => '20',
   ),
 ),
);

$dictionary["itb_cof_contacts_billto"] = array (
 'relationships' =>
 array (
   'itb_cof_contacts_billto' =>
   array (
     'lhs_module' => 'Contacts',
     'lhs_table' => 'contacts',
     'lhs_key' => 'id',
     'rhs_module'=> 'itb_cof',
     'rhs_table'=> 'itb_cof',
     'rhs_key' => 'id',
     'relationship_type' => 'many-to-many',
	 'true_relationship_type' => 'one-to-many',
     'join_table' => 'itb_cof_contacts',
     'join_key_lhs' => 'contact_id',
     'join_key_rhs' => 'itb_cof_id',
	 'relationship_role_column' => 'contact_role',
     'relationship_role_column_value' => 'Bill To',
   ),
 ),
 'table' => 'itb_cof_contacts',
 'fields' =>
 array (
   'id' =>
   array (
     'name' => 'id',
     'type' => 'id',
   ),
   'date_modified' =>
   array (
     'name' => 'date_modified',
     'type' => 'datetime',
   ),
   'deleted' =>
   array (
     'name' => 'deleted',
     'type' => 'bool',
     'len' => '1',
     'default' => '0',
     'required' => true,
   ),
   'contact_id' =>
   array (
	 'name' => 'contact_id',
     'type' => 'id',
     
   ),
   'itb_cof_id' =>
   array (
     'name' => 'itb_cof_id',
     'type' => 'id',
   ),
   'contact_role' =>
   array (
     'name' => 'contact_role',
     'type' => 'varchar',
     'len' => '20',
   ),
 ),
);


$dictionary["itb_cof_opportunities"] = array (
 'relationships' =>
 array (
   'itb_cof_opportunities' =>
   array (
     'lhs_module' => 'Opportunities',
     'lhs_table' => 'opportunities',
     'lhs_key' => 'id',
     'rhs_module'=> 'itb_cof',
     'rhs_table'=> 'itb_cof',
     'rhs_key' => 'id',
     'relationship_type' => 'many-to-many',
     'true_relationship_type' => 'one-to-many',
     'join_table' => 'itb_cof_opportunities',
     'join_key_lhs' => 'opportunity_id',
     'join_key_rhs' => 'itb_cof_id',
   ),
 ),
 'table' => 'itb_cof_opportunities',
 'fields' =>
 array (
   'id' =>
   array (
     'name' => 'id',
     'type' => 'id',
   ),
   'date_modified' =>
   array (
     'name' => 'date_modified',
     'type' => 'datetime',
   ),
   'deleted' =>
   array (
     'name' => 'deleted',
     'type' => 'bool',
     'len' => '1',
     'default' => '0',
     'required' => true,
   ),
   'opportunity_id' =>
   array (
     'name' => 'opportunity_id',
     'type' => 'id',
   ),
   'itb_cof_id' =>
   array (
     'name' => 'itb_cof_id',
     'type' => 'id',
   ),
 ),
 'indices' =>
 array (
   array (
     'name' => 'itb_cof_opportunitiespk',
     'type' => 'primary',
     'fields' => array ('id'),
   ),
   array (
     'name' => 'idx_opp_cof_opp',
     'type' => 'index',
     'fields' => array ( 'opportunity_id'),
   ),
   array (
     'name' => 'idx_cof_oportunities',
     'type' => 'alternate_key',
     'fields' => array ( 'itb_cof_id' ),
   )
 ),
);

\custom\Extension\application\Ext\TableDictionary\custom_relationships_cof.php

<?php 
include('custom/metadata/custom_relationships_cof.php');
?>

After doing QRR it prompted for create table in the query box after comparing the vardefs

I executed the code and the fields got created verified the relationship tables are present in the database.

Now I have created a relate fields in the COF module as below

\custom\Extension\modules\itb_cof\Ext\Vardefs\COF_Fields.php

<?php


$dictionary["itb_cof"]["fields"]["shipping_account_name"] = array(
      'name' => 'shipping_account_name',
      'rname' => 'name',
      'id_name' => 'shipping_account_id',
      'vname' => 'LBL_SHIPPING_ACCOUNT_NAME',
      'type' => 'relate',
      'table' => 'shipping_accounts',
      'isnull' => 'true',
      'link' => 'shipping_accounts',
      'module' => 'Accounts',
      'source' => 'non-db',
      'populate_list' => 
      array (
        'shipping_address_street' => 'shipping_address_street',
        'shipping_address_city' => 'shipping_address_city',
        'shipping_address_state' => 'shipping_address_state',
        'shipping_address_postalcode' => 'shipping_address_postalcode',
        'shipping_address_country' => 'shipping_address_country',
      ),
    );
$dictionary["itb_cof"]["fields"]["shipping_account_id"] = array(
      'name' => 'shipping_account_id',
      'type' => 'relate',
      'vname' => 'LBL_SHIPPING_ACCOUNT_ID',
      'source' => 'non-db',
      'link' => 'shipping_accounts',
      'rname' => 'id',
      'massupdate' => false,
      'module' => 'Accounts',
      'studio' => 'false',
      'id_name' => 'account_id',
    );
$dictionary["itb_cof"]["fields"]["shipping_contact_name"] = array(
      'name' => 'shipping_contact_name',
      'rname' => 'full_name',
      'id_name' => 'shipping_contact_id',
      'vname' => 'LBL_SHIPPING_CONTACT_NAME',
      'type' => 'relate',
      'link' => 'shipping_contacts',
      'table' => 'shipping_contacts',
      'isnull' => 'true',
      'module' => 'Contacts',
      'source' => 'non-db',
    );
$dictionary["itb_cof"]["fields"]["shipping_contact_id"] = array(
      'name' => 'shipping_contact_id',
      'rname' => 'id',
      'id_name' => 'shipping_contact_id',
      'vname' => 'LBL_SHIPPING_CONTACT_ID',
      'type' => 'relate',
      'link' => 'shipping_contacts',
      'table' => 'shipping_contacts',
      'isnull' => 'true',
      'module' => 'Contacts',
      'source' => 'non-db',
      'massupdate' => false,
    );
$dictionary["itb_cof"]["fields"]["account_name"] = array(
      'name' => 'account_name',
      'rname' => 'name',
      'id_name' => 'account_id',
      'vname' => 'LBL_ACCOUNT_NAME',
      'type' => 'relate',
      'link' => 'billing_accounts',
      'table' => 'billing_accounts',
      'isnull' => 'true',
      'module' => 'Accounts',
      'source' => 'non-db',
      'massupdate' => false,
      'studio' => 
      array (
        'edit' => 'false',
        'detail' => 'false',
        'list' => 'false',
      ),
    );
$dictionary["itb_cof"]["fields"]["account_id"] = array(
      'name' => 'account_id',
      'type' => 'relate',
      'link' => 'billing_accounts',
      'rname' => 'id',
      'vname' => 'LBL_ACCOUNT_ID',
      'source' => 'non-db',
      'massupdate' => false,
      'module' => 'Accounts',
      'studio' => 'false',
      'id_name' => 'account_id',
    );
$dictionary["itb_cof"]["fields"]["billing_account_name"] = array(
      'name' => 'billing_account_name',
      'rname' => 'name',
      'id_name' => 'billing_account_id',
      'vname' => 'LBL_BILLING_ACCOUNT_NAME',
      'type' => 'relate',
      'link' => 'billing_accounts',
      'table' => 'billing_accounts',
      'isnull' => 'true',
      'module' => 'Accounts',
      'source' => 'non-db',
      'importable' => 'required',
      'required' => true,
      'populate_list' => 
      array (
        'billing_address_street' => 'billing_address_street',
        'billing_address_city' => 'billing_address_city',
        'billing_address_state' => 'billing_address_state',
        'billing_address_postalcode' => 'billing_address_postalcode',
        'billing_address_country' => 'billing_address_country',
        'gst_treatment_c' => 'gst_treatment_c',
      ),
    );
$dictionary["itb_cof"]["fields"]["billing_account_id"] = array(
      'name' => 'billing_account_id',
      'type' => 'relate',
      'vname' => 'LBL_BILLING_ACCOUNT_ID',
      'source' => 'non-db',
      'link' => 'billing_accounts',
      'rname' => 'id',
      'massupdate' => false,
      'module' => 'Accounts',
      'studio' => 'false',
      'id_name' => 'account_id',
    );
$dictionary["itb_cof"]["fields"]["billing_contact_name"] = array(
      'name' => 'billing_contact_name',
      'rname' => 'full_name',
      'id_name' => 'billing_contact_id',
      'vname' => 'LBL_BILLING_CONTACT_NAME',
      'type' => 'relate',
      'link' => 'billing_contacts',
      'table' => 'billing_contacts',
      'isnull' => 'true',
      'module' => 'Contacts',
      'source' => 'non-db',
      'required' => true,
    );
$dictionary["itb_cof"]["fields"]["billing_contact_id"] = array(
      'name' => 'billing_contact_id',
      'rname' => 'id',
      'id_name' => 'billing_contact_id',
      'vname' => 'LBL_BILLING_CONTACT_ID',
      'type' => 'relate',
      'link' => 'billing_contacts',
      'table' => 'billing_contacts',
      'isnull' => 'true',
      'module' => 'Contacts',
      'source' => 'non-db',
      'massupdate' => false,
    );

$dictionary["itb_cof"]["fields"]["shipping_accounts"] = array(
      'name' => 'shipping_accounts',
      'type' => 'link',
      'relationship' => 'itb_cof_shipto_accounts',
      'vname' => 'LBL_SHIP_TO_ACCOUNT',
      'source' => 'non-db',
      'link_type' => 'one',
    );
$dictionary["itb_cof"]["fields"]["billing_accounts"] = array(
      'name' => 'billing_accounts',
      'type' => 'link',
      'relationship' => 'itb_cof_billto_accounts',
      'vname' => 'LBL_BILL_TO_ACCOUNT',
      'source' => 'non-db',
      'link_type' => 'one',
      'populate_list' => 
      array (
        'account_id' => 'billing_account_id',
        'account_name' => 'billing_account_name',
      ),
    );
$dictionary["itb_cof"]["fields"]["shipping_contacts"] = array(
      'name' => 'shipping_contacts',
      'type' => 'link',
      'relationship' => 'itb_cof_contacts_shipto',
      'vname' => 'LBL_SHIP_TO_CONTACT',
      'source' => 'non-db',
      'link_type' => 'one',
    );
$dictionary["itb_cof"]["fields"]["billing_contacts"] = array(
      'name' => 'billing_contacts',
      'type' => 'link',
      'link_type' => 'one',
      'vname' => 'LBL_BILL_TO_CONTACT',
      'relationship' => 'itb_cof_contacts_billto',
      'source' => 'non-db',
    );


$dictionary["itb_cof"]["fields"]["opportunities"] = array(
      'name' => 'opportunities',
      'type' => 'link',
      'vname' => 'LBL_OPPORTUNITY',
      'relationship' => 'itb_cof_opportunities',
      'link_type' => 'one',
      'source' => 'non-db',
      'populate_list' => 
      array (
        'account_id' => 'billing_account_id',
        'account_name' => 'billing_account_name',
        'id' => 'opportunity_id',
        'name' => 'opportunity_name',
      ),
    );

$dictionary["itb_cof"]["fields"]["opportunity_name"] = array(
      'name' => 'opportunity_name',
      'rname' => 'name',
      'id_name' => 'opportunity_id',
      'vname' => 'LBL_OPPORTUNITY_NAME',
      'type' => 'relate',
      'table' => 'Opportunities',
      'isnull' => 'true',
      'module' => 'Opportunities',
      'link' => 'opportunities',
      'massupdate' => false,
      'source' => 'non-db',
      'len' => 50,
      'auto_populate' => true,
      'populate_list' => 
      array (
        'account_id' => 'billing_account_id',
        'account_name' => 'billing_account_name',
        'id' => 'opportunity_id',
        'name' => 'opportunity_name',
      ),
    );
$dictionary["itb_cof"]["fields"]["opportunity_id"] = array(
      'name' => 'opportunity_id',
      'type' => 'relate',
      'source' => 'non-db',
      'rname' => 'id',
      'id_name' => 'id',
      'vname' => 'LBL_OPPORTUNITY_ID',
      'table' => 'opportunities',
      'module' => 'Opportunities',
      'link' => 'opportunities',
    );

 ?>

After that I did a quick repair and rebuild

Then in studio I have placed the fields into the record view of COF module

when I related the account, contact, opportunity record into the relate field on record view of COF module

When I try to create COF record or edit any existing COF record all the other values gets save but these related field value does not get save

I also checked into the respective database relationship table there is no entry.

can anyone point me out what I am missing here ? or where did I go wrong ?

Shrikant Gaware · Accepted Answer

I see that you're trying to create Quotes-like relationships via code. 
 
 But for a relationship save to work, you have to create "links" in both side of the relationship. 
 You have created "link" fields for your custom module, but you also have to create a similar link in your Accounts/Contacts/Opportunities. You can check how the quote links are added to accounts and create something similar for your custom module: 
 'quotes' => array ( 'name' => 'quotes' , 'type' => 'link' , 'relationship' => 'quotes_billto_accounts' , 'source' => 'non-db' , 'module' => 'Quotes' , 'bean_name' => 'Quote' , 'ignore_role' => true , 'vname' => 'LBL_QUOTES' , ) , 
 Now to the saving part - I was curious on why it didn't work, and looks like you will have to add an additional flag in your relate field definition to make it work. 
 i.e - in your relate field, add this flag for all parent name fields: 
 $dictionary [ "itb_cof" ][ "fields" ][ "shipping_account_name" ] = array ( 'name' => 'shipping_account_name' , 'rname' => 'name' , 'id_name' => 'shipping_account_id' , 'vname' => 'LBL_SHIPPING_ACCOUNT_NAME' , 'type' => 'relate' , 'save' => true 
 
 This is because, Sugar expects this flag or a preset relationship fields for such relate fields to work. Eg: data/SugarBean.php, check the method - which enforces if (isset($def['save'])) 
 protected function handle_remaining_relate_fields ( $exclude = array ())