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

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
  • 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!

  • 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

Reply Children