Suitecrm: Relationships created in Studio create incorrect vardefs

[tsummerer]

Issue

A one-to-many relationship contains 3 fields on the 'many' side. There is a link field to define the relationship, a relate field to display the related record's name, and an id field to hold the related Id.

For example, here's the Account - Opportunities relationship:

'accounts' => array(
    'name' => 'accounts',
    'type' => 'link',
    'relationship' => 'accounts_opportunities',
    'link_type' => 'one',
    'module' => 'Accounts',
    'bean_name' => 'Account',
    ...
),
'account_name' => array(
    'name' => 'account_name',
    'type' => 'relate',
    'rname' => 'name',
    'id_name' => 'account_id',
    'table' => 'accounts',
    'join_name' => 'accounts',
    'module' => 'Accounts',
    'dbType' => 'varchar',
    'link' => 'accounts',
    'source' => 'non-db',
    ...
),
'account_id' => array(
    'name' => 'account_id',
    'type' => 'id',
    'source' => 'non-db',
    ...
),

When you create a relationship via Studio it creates 2 link fields and no id field. The "_ida" field is set as a link field when really it should be an id type.

$dictionary["TEST_TEST"]["fields"]["accounts_test_test_2"] = array (
  'name' => 'accounts_test_test_2',
  'type' => 'link',
  'relationship' => 'accounts_test_test_2',
  'source' => 'non-db',
  'id_name' => 'accounts_test_test_2accounts_ida',
  ...
);
$dictionary["TEST_TEST"]["fields"]["accounts_test_test_2_name"] = array (
  'name' => 'accounts_test_test_2_name',
  'type' => 'relate',
  'rname' => 'name',
  'id_name' => 'accounts_test_test_2accounts_ida',
  'table' => 'accounts',
  'module' => 'Accounts',
  'source' => 'non-db',
  'link' => 'accounts_test_test_2',
  ...
);
$dictionary["TEST_TEST"]["fields"]["accounts_test_test_2accounts_ida"] = array (
  'name' => 'accounts_test_test_2accounts_ida',
  'type' => 'link',
  'source' => 'non-db',
  ...
);

This causes trouble with the Elasticsearch connector #7742 and with the API (not sure that there's a ticket yet)

Expected Behavior

Studio should create proper fields

Actual Behavior

Studio generates 2 link fields and no id field.

Possible Fix

Steps to Reproduce

1. Create a 1-M relationship via Studio
2. Look at the generated vardefs
   3.
   4.

Context

This causes issues in Elasticsearch - cannot view results in modules that have custom relationships.
I believe that this caused issues with the API - but I can't remember if it was v8 or V8.

Your Environment

• SuiteCRM Version used: 7.11.8
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):Chrome
• Environment name and version (e.g. MySQL, PHP 7):LAMP
• Operating System and version (e.g Ubuntu 16.04):LAMP

[Mac-Rae]

Confirmed in latest :+1:

[tsummerer]

So I've been researching this in regards to a few other issues, and it turns out to be a bit more complicated.

Self-referencing relationships (ie. Accounts related to other Accounts, etc) need to have 2 link fields.
There is a normal link field which provides/powers the subpanel, but there needs to be an additional link field which is tied to the relate field and is designated as the 'right side' of the relationship.

For whatever reason, it looks like the original authors sort of 'cheated' by occasionally treating the _ida field as an ID field and occasionally treating it like a link field. There is evidence of various bandages throughout to deal with this.

The solution that I'm using in my customer instances is to create a 4th field for self-referncing relationships - like this:

$dictionary["Account"]["fields"]["accounts_accounts_1"] = array (
  'name' => 'accounts_accounts_1',
  'type' => 'link',
  'relationship' => 'accounts_accounts_1',
  'source' => 'non-db',
  'module' => 'Accounts',
  'bean_name' => 'Account',
  'vname' => 'LBL_ACCOUNTS_ACCOUNTS_1_FROM_ACCOUNTS_L_TITLE',
  'id_name' => 'accounts_accounts_1accounts_ida',
);
$dictionary["Account"]["fields"]["accounts_accounts_1_name"] = array (
  'name' => 'accounts_accounts_1_name',
  'type' => 'relate',
  'source' => 'non-db',
  'vname' => 'LBL_ACCOUNTS_ACCOUNTS_1_FROM_ACCOUNTS_L_TITLE',
  'save' => true,
  'id_name' => 'accounts_accounts_1accounts_ida',
  'link' => 'accounts_accounts_1accounts_rhsSideLink',    // <--- Notice that this points to the RHS link
  'table' => 'accounts',
  'module' => 'Accounts',
  'rname' => 'name',
);
$dictionary["Account"]["fields"]["accounts_accounts_1accounts_ida"] = array (
  'name' => 'accounts_accounts_1accounts_ida',
  'type' => 'id',
  'relationship' => 'accounts_accounts_1',
  'source' => 'non-db',
  'reportable' => false,
  'side' => 'right',
  'vname' => 'LBL_ACCOUNTS_ACCOUNTS_1_FROM_ACCOUNTS_R_TITLE',
);

$dictionary["Account"]["fields"]["accounts_accounts_1accounts_rhsSideLink"] = array (
  'name' => 'accounts_accounts_1accounts_rhsSideLink',
  'type' => 'link',
  'relationship' => 'accounts_accounts_1',
  'source' => 'non-db',
  'reportable' => false,
  'side' => 'right',
  'vname' => 'LBL_ACCOUNTS_ACCOUNTS_1ACCOUNTS_RHSSIDELINK',
);

[PedroErnst]

Can confirm this issue. Suite expects a string sometimes, and a Link2 other times in the _ida field, which can cause occasional 500 errors that are hard to reproduce.

[chris001]

Time for some code comparative analysis.
How does Sugar 9.0 code do this?
How does Sugar 8.0 code do this?
How does VTiger code do it?
All above have working feature with the relationships created in Studio creating correct vardefs, and are based on SugarCRM 6.5, which is also what Suite is based on...

[labcerouno]

@pgorod this is quite important

[labcerouno]

fix #8472 works!

[pgorod]

@pgorod this is quite important

Agreed, but I don't run things around here, I'm just a regular community member like you :-)