How to add a related Field without adding an explicit relationship?

Question

Hi Developer, 
 In SugarCRM Studio I can add a field with the type relate and link to the Accounts module. There should not be an explicit relationship between the modules. A custom table is then created for this, in which the ID of the account is inserted. So fine, so good. However, I would like the field to be created via a vardef and the information to be directly in the original table. The question is: How do I have to knit the vardef so that the ID of the account appears directly in the SQL table of the module, but the name of the linked account is displayed on the user interface in the layout? 
 The documentation at support.sugarcrm.com/.../vardefs does not provide any revealing information. 
 Our current status looks like this: We want to implement the DIS field in the Purchased Line Items module: 
 $dictionary['PurchasedLineItem']['fields']['DIS'] = array(
 'name' => 'DIS',
 'vname' => 'LBL_DIS',
 'type' => 'relate',
 'rname' => 'name',
 'id_name' => 'id',
 'module' => 'Accounts',
 'comment' => 'The name of the account represented by the account_id field',
 'required' => false,
 'studio' => true,
); 
 But that doesn't work. The field is displayed, but cannot be used. The ID of the purchased line item is always entered. Selecting the accounts works, but there is an error when saving, the account is not written to the data record. The sugarcrm log remains empty. 
 
 Thank you for your support. 
 
 Kind regards. 
 Martin

Martin Neitzke · Accepted Answer

To create a custom relation field in the standard table you can use the extension framework by adding a name field and an id field to the table. Be sure that the name will never be part of the Sugar fields in the future, e.g. by a special prefix. 
 As an example I added my_account_name/_id to the Notes modules: 
 <?php 
 $dictionary["Note"]["fields"]["my_account_name"] =
 array (
 'labelValue' => 'my_account_name',
 'dependency' => '',
 'required_formula' => '',
 'readonly_formula' => '',
 'required' => false,
 'readonly' => false,
 'source' => 'non-db',
 'name' => 'my_account_name',
 'vname' => 'LBL_MY_ACCOUNT',
 'type' => 'relate',
 'massupdate' => true,
 'hidemassupdate' => false,
 'no_default' => false,
 'comments' => '',
 'help' => '',
 'importable' => 'true',
 'duplicate_merge' => 'enabled',
 'duplicate_merge_dom_value' => 1,
 'audited' => true,
 'reportable' => true,
 'unified_search' => false,
 'merge_filter' => 'disabled',
 'pii' => false,
 'calculated' => false,
 'len' => 255,
 'size' => '20',
 'id_name' => 'my_account_id',
 'ext2' => 'Accounts',
 'module' => 'Accounts',
 'rname' => 'name',
 'quicksearch' => 'enabled',
 'studio' => 'visible',
 );
 $dictionary["Note"]["fields"]["my_account_id"] =
 array (
 'required' => false,
 'readonly' => false,
 'name' => 'my_account_id',
 'vname' => 'LBL_MY_ACCOUNT_ID',
 'type' => 'id',
 'massupdate' => false,
 'hidemassupdate' => false,
 'no_default' => false,
 'comments' => '',
 'help' => '',
 'importable' => 'true',
 'duplicate_merge' => 'enabled',
 'duplicate_merge_dom_value' => 1,
 'audited' => false,
 'reportable' => false,
 'unified_search' => false,
 'merge_filter' => 'disabled',
 'pii' => false,
 'calculated' => false,
 'len' => 36,
 'size' => '20',
 );
 
 Add lables in the en_us language file for the two fields. 
 After Quick Repair & Rebuild i got the following DB correction script which had to be executed: 
 /*COLUMNS*/ /*MISSING IN DATABASE - my_account_id - ROW*/ ALTER TABLE notes add COLUMN my_account_id char(36) NULL; 
 DONE!

Martin Neitzke · Answer

Hi Martin Neitzke , 
 The relate fields are required to have a link to be used. However, if you provide the table that can be self sufficient as well. So all you need to do please add: 
 'table' => 'accounts', So your vardef should be: $dictionary['PurchasedLineItem']['fields']['DIS'] = array(
 'name' => 'DIS',
 'vname' => 'LBL_DIS',
 'type' => 'relate',
 'rname' => 'name',
 'id_name' => 'id',
 'module' => 'Accounts',
 'table' => 'accounts', // Table needs to be here
 'comment' => 'The name of the account represented by the account_id field',
 'required' => false,
 'studio' => true,
); 
 Hope this helps. :) Tevfik T&uuml;mer Sr. Developer Support Engineer