In SugarCRM, when you create a relationship, and it doesn’t matter whether it is a 1-2-many or many-2-many relationship, it always creates you a middle table. Which is kinda annoying, since for 1-2-many relationships you don’t really needed. And when I say middle table, I mean a link table between 2 tables.

It’s the best to describe that in a real life example. Let’s assume, you want to start storing in Sugar your suppliers details and their contact details. What you would normally do, you would create a Suppliers module to store the supplier details – the name, address, contract details, etc and a ContactDetails module to store information about different ways you can contact the supplier – so type of contact details and a value. Then you would create a 1-2-many relationship between them 2:

Now, when you look in the database, Sugar is gonna create for you the 3 “nice” tables:

But, what we really want is 2 tables with a “foreign” key in the contact details table, something like this:

Fortunately, it’s not that difficult. Searching google for a solution brings a few posts. Me personally I have based on 2 posts by John Mertic from SugarCRM:

• Create one-to-many relationship in code, no middle table required
• HOWTO: Add a subpanel using code

The problem is, they cover the topic well in terms of concept, but you can’t really follow it step by step, at least not without a good understanding of SugarCRM as a development platform. It took me several hours to match all the dots together, before I made the whole thing work. And that’s because I simply did not understands some of the things John did there (and I am not quite sure whether I understand it now). So here’s the step by step guide, that I follow, and my understanding how it all works. I assume you already have the 2 modules created, and what we will do now, is just create the relationship between them so Sugar modifies db schema for us, add the panel to the parent module so we can see associated records on the Suppliers screen and modify the panel so we can create new records using quick create form.

1. Step 1: Create the relate fields

First we will create fields required for the relationship: the id field and relate field. Create a new file called SupplierRelate.php in custom/Extension/modules/Demo_SupplierContactDetails/Ext/Vardefs/ and define the 2 fields in there as below:

There is a few things you have to pay attention to. First of all make sure the module name is correct in the $dictionary array. In my case it is Demo_SupplierContactDetails, following the convention of <PackageName>_<ModuleName> – I named my package Demo and the module SupplierContactDetails. Next the field name is really important and it has to follow the pattern of <ParentBean>_<FieldName>, so in my case it is demo_suppliers_name and demo_suppliers_id (all lowercase). If you don’t do it right, later on the quick create form is not gonna work. If you change the values in the field definitions to match appropriate parent module name, everything should be fine. Take a note of name and id_name in demo_suppliers_name field definition and name in demo_suppliers_id field definition. The relate field (‘name’ one) we will be using to associate SupplierContactDetails data with a parent supplier, while the ‘id’ field will be used to alter the demo_suppliercontactdetails table and add the parent id column in there (which will be called demo_suppliers_id)

2. Step 2: Define the relationship

Next, we will define the relationship. Will do that in the same file, as follows:

First is the link to the relationship – note the relationship element of the first array that is used below for the relationship definition itself and module and bean name of the parent module.  Secondly note the 2 sides of the relationship in the relationship definition – the left hand side (lhs) pointing at Demo_SupplierContactDetails table and demo_suppliers_id key (the same name we have define above in the id field definition) and right hand side (rhs) pointing at Demo_Suppliers table with id key.

To complete the relationship we also have to define the link in the parents module. So let’s create another file called SupplierContactDetailsRelate.php in custom/Extension/modules/Demo_Suppliers/Ext/Vardefs/:

Note the module name in the $dictionary array which point at our parent module Demo_Suppliers, the name of the field that point at the child module (although I am not sure whether it is important, but I guess even for clarity and readability it is worth to stick to it) and finally the relationship name which has to be the same as the one we have used in the definition in the child module above.

3. Step 3: Add missing labels in the language pack

Don’t forget about the label definitions. I only care about en_UK packs so I have added the new label definitions into:

• modules/Demo_Suppliers/language/en_UK.lang.php
• modules/Demo_SupplierContactDetails/language/en_UK.lang.php

That includes:

• ‘LBL_SUPPLIER_NAME’ => ‘Supplier name’,
• ‘LBL_SUPPLIER_ID’ => ‘Supplier ID’,
• ‘LBL_SUPPLIERS’ => ‘Suppliers’,
• ‘LBL_SUPPLIER_CONTACTDETAILS’ => ‘Supplier Contact Details’,

4. Step 4: Repair and rebuild

Now, when you go to the admin panel and run Quick Repair and Rebuild Sugar will generate the SQL query you need to run to alter the child table (instead of creating the middle table):

and achieve what we wanted from the very beginning, no middle table.

5. Step 5: Add relate field to SupplierContactDetails layout

But, that’s not it. While we have created the relationship, we can’t associate the records together yet. First thing we have to do, is to add the newly created relate field (Supplier name) onto detail, edit, quick create and list view of the Demo_SupplierContactDetails module. You can easily do that via Studio:

Now we can easily associate and see the parent record information.

6. Step 6: Create the subpanel for Suppliers module

The final step will be to add subpanel in the Suppliers module, so we can easily see the associated records and create them from within the same module. All we have to do is create a new file called demo_suppliercontactdetails_subpanel.php in custom/Extension/modules/Demo_Suppliers/Ext/Layoutdefs/ with the following contents:

Once again, note the name of the subpanel in $layout_defs array – demo_suppliercontactdetails (although I don’t know whether it has to match the child module – again will definitely make it easier to understand what the panel is for), module and get_subpanel_data need to point at the child module. It’s extremly important that get_subpanel_data points at the child module, otherwise no data will be retrieved. And finally top buttons will allow us to create new contact details record using quick create form. I have also added the label to modules/Demo_Suppliers/language/en_UK.lang.php and run ‘Repair and Rebuild’.

But that’s not it. We now have to define the subpanel itself. Create a brand new file called Demo_Suppliers_subpanel_demo_suppliercontactdetails.php in custom/modules/Demo_SupplierContactDetails/metadata/subpanels/. It’s very important that this filename matches the name of the subpanel defined in the file above – Demo_Suppliers_subpanel_demo_suppliercontactdetails, and follows the pattern I used. If you live that file empty (with only opening php tag at the top) you will be able to go into Studio and drag the columns you want on the subpanel and simply click ‘Save and Deploy’.

 

The file will then look something like this:

Please note the 2 elements of the array at the bottom – edit_button and remove_button. Studio won’t have them on the column list – I have added them manually afterwards. These 2 buttons will allow you to edit and delete a record on the list. And so here it is our completed subpanel:

and the quick create form (I have already tweaked it in Studio to show only relevant fields):

Note: I have noticed, that the remove button actually doesn’t work. Will have to find out why.