Thread Details
  • State Verified Answer
  • Replies 12 replies
  • Answers 1 answer
  • Subscribers 352 subscribers
  • Views 2366 views
  • Users 0 members are here
Forum Actions
Related

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

Martin Neitzke

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

Parents
  • +1

    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!

  • 0 in reply to Harald Kuske

    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.

     : 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

  • 0 in reply to Martin Neitzke

    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.

  • 0 in reply to 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  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 Slight smile

    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.

  • 0 in reply to John Hawkins

    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!

Reply
  • 0 in reply to John Hawkins

    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!

Children
  • 0 in reply to Francesca Shiekh

    For fully custom modules (which sadly are not actually upgrade safe as there is nothing to stop Sugar using your module name in the future and having a clash apart from the prefix that is added by Module Builder!) I only ever use the vardefs.php and appropriate language files in the module directory for creating new fields. New fields in custom modules always go onto the main table.

    In fact, we generally have a team rule that we put updates to our own modules into the main directory. That applies to views and layouts as well. We tend to avoid using Studio for layout changes so don't need to worry about the files going into the custom tree. That way we can use source control and CI tools to manage the files from afar. And we know when a customer has been fiddling with things Slight smile

    As you say, we all learn every day.

    Thanks,

    JH.

  • 0 in reply to John Hawkins

    There are many things I would do differently now compared to what we did in the last decade. I'm trying to clean some things, but it's not easy to "back out" of older code/decisions, especially when there are multiple external integrations.

    Sadly, sometimes it's easier to live with those poor choices.