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 ?
