help with setting-up many-to-many relation

[JoostLand]

Hi All,

I'm using SugarCE 5.5.0 and I would like to create a many-to-many relation between the Account and Employee (which is actually a copy of the User module, right?) modules in order to assign multiple Employees as 'Account managers'. However I'm having troubles implementing this and I can't seem to figure out what I'm doing wrong. ANY HELP would be very much appriciated. Here's what I've done so far:

I must first create a new table by defining the table in the tabledictionary. Here it is:

$dictionary["accounts_employees"] = array (
'true_relationship_type' => 'many-to-many',
'relationships' =>
array (
'accounts_employees' =>
array (
'lhs_module' => 'Account',
'lhs_table' => 'accounts',
'lhs_key' => 'id',
'rhs_module' => 'Employee',
'rhs_table' => 'users',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'accounts_employees_c',
'join_key_lhs' => 'account_id',
'join_key_rhs' => 'user_id',
),
),
'table' => 'accounts_employees_c',
'fields' =>
array (
0 =>
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
1 =>
array (
'name' => 'date_modified',
'type' => 'datetime',
),
2 =>
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
3 =>
array (
'name' => 'account_id',
'type' => 'varchar',
'len' => 36,
),
4 =>
array (
'name' => 'user_id',
'type' => 'varchar',
'len' => 36,
),
),
'indices' =>
array (
0 =>
array (
'name' => 'accounts_employees_pk',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'accounts_employees_alt',
'type' => 'alternate_key',
'fields' =>
array (
0 => 'account_id',
1 => 'user_id',
),
),
),
);
I placed this in the
./custom/metadata directory and included it in ./custom/application/Ext/TableDictionary/tabledictionary.ext.php

Next I defined the link field in ./custom/Extension/modules/Employees/Ext/Vardefs, here it is:
$dictionary['Employee']['fields']['accounts_employees']=array(
'name'=>'accounts_employees',
'type'=>'link',
'relationship'=>'accounts_employees',
'source'=>'non-db',
);

All I needed to do next (I thought) was to create a subpanel in the employee detailview by creating a file in ./custom/Extension/modules/Employees/Ext/Layoutdefs. Like this:

$layout_defs["Employees"]["subpanel_setup"]["accounts_employees"] = array (
'order' => 100,
'module' => 'Account',
'subpanel_name' => 'default',
'sort_order' => 'asc',
'sort_by' => 'id',
'title_key' => 'LBL_EMPLOYEES_ACCOUNTS_FROM_ACCOUNTS_TITLE',
'get_subpanel_data' => 'accounts_employees',
);

After repairing the application the table is created nicely in the database. However when I go to a detailview of an employee I don't see a subpanel for his accounts. The subpanel tile's get rendered, but only 'All' is showing. I've manually put some data in the accounts_employees_c table, but that's not helping.

What am I doing wrong? Is there something missing in my subpanel setup? Are alle the variable names correct?
I've been on this for 3 days now, beginning to feel desperate!

Thanks a lot!
cheers,
Joost

[andopes]

You have to specify at relationship the module Users rather than Employee and Accounts rather than Account.

Regards

[JoostLand]

Thanks for your reply Andopes!

However, I realized I was doing something else wrong, the Employees module does not have any subpanels.

It works now, here's what I've did in case anybody else wants to create a many-to-many relation between employees and accounts.

create /modules/Employees/studio.php in order to create a realtionship for the employees module in studio.
create /modules/Employees/metadata/subpanels/default.php (copy from Users module)

Now you can use studio to create the realtionship.

cheers