Sugar Developer Guide Version 5.1

Many-to-Many Relationships

In the ./metadata directory, all the many-to-many relationships are defined and included in the $dictionary array. The files are stored in ./metadata/<relation_table_name>MetaData.php. Tables are generated based on these definitions. These files are included via the ./modules/TableDictionary.php. If you create a custom many-to-many relationship, you will need to add the reference to your newly relationships by adding the new reference in the file custom/application/Ext/TableDictionary/tabledictionary.php. You may need to create this file if it does not exist. These changes will take affect after you clear the Sugar Cache by running the “Quick Repair and Rebuild” option from the Admin Repair screen.

The following are the definitions for $dictionary[<relation_table>]. They are similar to the Vardefs. If necessary use that page as a reference as well.

•	<relation_table> - the index for this relationship in the $dictionary array

•	table - the name of the table that is created in the database

•

fields - array containing arrays for each column definition. The join table must have a field for the primary key of each table to be linked, a primary key for the join table itself, a deleted field for relationship unlinking, and a date_modifed field to track when the relationship changes. Additional fields can be added to track the role played by the relationship,

•	indices - the database indices. Note see the example for indices below for necessary values.

•	relationships - definitions of the relationships between the two tables

o	lhs_module - the left hand module. Should match $beanList index

o	lhs_table - the left hand table name

o	lhs_key - the key to use from the left table

o	rhs_module - the right hand module. Should match $beanList index

o	rhs_table - the right hand table name

o	rhs_key - the key to use from the right table

o	relationship_type - relationship type

o	join_table - join table used to join items

o	join_key_lhs - left table key. Should exist in table field definitions above

o	join_key_rhs - right table key. Should exist in table field definitions above

Note that Relationship metadata and the Vardefs are the critical building blocks of the new ListViews. In conjunction with [module]/metadata/ListViewdefs.php, these three elements generate your ListView.

For example, you may need to display data from another module or even just another table (not part of a module). To do this, you will need to get all your queries in order () AND add the reference in Vardefs.php. The new ListViews can ONLY display data registered in the Vardefs.

For example:

'store_name' => array ( 'name' => 'store_name',

'rname' => 'name',

'id_name' => 'id',

'vname' => 'LBL_STORE_NAME',

'type' => 'relate',

'link'=>'store_name',

'table' => 'jd_stores',

'join_name'=>'stores',

'isnull' => 'true',

'module' => 'Stores',

'dbType' => 'varchar',

'len' => 100,

'source'=>'non-db',

'unified_search' => false,

'massupdate' => false,

'comment' => 'The name of the store represented by the store_id field in customer_service' ),

 

In this case, Stores is not a module, but jd_stores is a table. The joins with that table are handled in fill_in_additional_detail_fields(), fill_in_additional_list_fields(), create_list_query(), and (depending on your situation) create_new_list_query(). You need to get all these things straight, but you will still not get your data on your ListView is you do not register it in Vardefs.