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

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 ?

Parents Reply Children
  • Hi Shrikant,

    The relate fields data are stored in the custom table, not in the relationship table. Could you please go to your database and check in the custom table entry.  Please describe your custom table of the custom module in the database and check the fields. For relate fields, the field name in the studio and database will be different. In DB it will be like modulename_id_c.

    Please check and let us know if you can get some clue.

    Hope this information helps you Slight smile

    Thanks!!

    Poojitha.K