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

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!

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',

$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ümer
Sr. Developer Support Engineer

Harald, if you add a relate field in Studio it would be added to the _cstm table, not the core table, right?

The alter table statement here resulting from the vardef being added in code seems to suggest that it's adding the custom field to the core table... 

Am I misunderstanding?

thanks,
FrancescaS

Thank you Harald,
that was exactly what we needed and it seems to work very well. Thanks for the very detailed code snippet. This has clarified a lot about integrating related fields without relationships.

Francesca Shiekh : To add the account ID in the table, the Alter Table command is executed when repairing. That's what we wanted: Add the new field to the core table so the value doesn't show up in an extra custom table.

Thanks for your support.

Martin

Hi Tevfik Tümer ,
I tried your suggestion, but it did not bring about any changes. Thanks for your input though.
Kind regards
Martin

My point is that I believe anything custom (added by us customers and not by Sugar) should go in the _cstm table not the core table for it to be upgrade safe.

Francesca Shiekh

Harald was pointing out the technique needed to get a field added to a core table rather than into a linked _cstm table as that is what Martin Neitzke originally asked for help with.

The only thing about this technique (as it uses the Extension framework) that is not upgrade safe is the potential that you are using a field name that Sugar may use in a future version. Harald's solution to this is to use a unique prefix to avoid clashes. My preference is to use the _c suffix instead as that is guaranteed to be unique in a module as it is the suffix used for fields added via Studio - and hence into the _cstm table. To avoid confusing future devs who are looking at it and may expect _c fields to be in the _cstm table you could add a double suffix _core_c or even use two underscores, __c

I have used the Extension technique since I first came to Sugar dev work (c. 2007 if you are asking!!) as I tend to prefer to avoid table joins where possible. As an oldie, I am still of the mindset to be as frugal with processing power as possible

This is largely personal preference and the documented way of making upgrade-safe field additions is to use Studio or a package (or, if you want to do it manually, use an entry in to the fields_meta_data table so the field goes into _cstm) so you are not wrong. However, it is not really an issue to use the core table if you are careful.

Thanks,

JH.

Thank you for the explanation, that's good to know.

In the 12 years I've worked with Sugar I have always avoided creating new fields outside the _cstm table, but I agree, your solution is cleaner when it comes to joins etc.
I particularly dislike Studio putting the fields in _cstm when I have had to add fields to a module that is custom in the first place.

There is always more to learn! :)

Thanks!

Hi Martin Neitzke ,

I'm sorry if it didn't fix right away. There could be many reasons for it. I noticed that you didn't present source field.
Following code block created the field sac_c in the Cases module on my local instance. You can give a try and validate. Please remember to add the sac_c field to the record view as well. 

<?php
$dictionary['Case']['fields']['sac_c'] = array(
    'name' => 'sac_c',
    'vname' => 'Contact',
    'type' => 'relate',
    'rname' => 'name',
    'id_name' => 'id',
    'module' => 'Contacts',
    'table' => 'contacts',
    'comment' => '',
    'required' => false,
    'studio' => true,
    'source' => 'custom_fields',
);

Tevfik Tümer

Your code example contains the line:

    'source' => 'custom_fields',

which I believe will cause the resulting field to be created in the _cstm table which Martin Neitzke was trying to avoid.

Thanks,

JH.