To do that in an upgrade safe manner you must add some files in /custom directory.

The many-to-many relationship involves the existence of a new table that make connection of other to tables.
Lets suppose that we have many products that can be assigned to many categories. Our tables are categories and products, and the new relationship table will be products_categories. SugarCRM will automatically create the new table if the relationship definition is correctly described.

The definition for many-to-many relationship in SugarCRM is stored in $dictionary array so you have to add a new structure to it.

So, you have to add two files in “custom” directory ():

  • product_categoriesMetaData.php – in /custom/metadata/
  • products_categories.php – in /custom/Extension/application/Ext/TableDictionary/

product_categoriesMetaData.php will contain the new $dictionary array with definition of many-to-many relationship, and can be described like this:

$dictionary["products_categories"] = array (
'true_relationship_type' => 'many-to-many',
'from_studio' => false,
'relationships' =>
array (
'products_categories' =>
array (
'lhs_module' => 'Categories',
'lhs_table' => 'categories',
'lhs_key' => 'id',
'rhs_module' => 'Products',
'rhs_table' => 'products',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'products_categories',
'join_key_lhs' => 'categories_id',
'join_key_rhs' => 'products_id',
),
),
'table' => 'products_categories',
'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' => 'categories_id',
'type' => 'varchar',
'len' => 36,
),
4 =>
array (
'name' => 'products_id',
'type' => 'varchar',
'len' => 36,
),
),
'indices' =>
array (
0 =>
array (
'name' => 'products_categories_pk',
'type' => 'primary',
'fields' =>
array (
0 => 'id',
),
),
1 =>
array (
'name' => 'products_categories_alt',
'type' => 'alternate_key',
'fields' =>
array (
0 => 'categories_id',
1 => 'products_id',
),
),
),
);

products_categories.php file from TableDictionary folder will actually make a reference to the above file to be executed and its content will be:

include('custom/metadata/products_categoriesMetaData.php');

After that, you have to go in SugarCRM administration panel and execute a “Quick Repair and Rebuild”.

At the end of the screen you’ll be noticed to execute an SQL query about a “CREATE TABLE” statement. This will actually create the new “products_categories” table.