SugarCRM many-to-one relations to standard modules

by Sander Marechal

The module builder in SugarCRM allows you to create one-to-many relations between one of your custom built modules and a standard module. However, it does not allow you to create many-to-one relationships. This means that it is not possible to create an Invoices module for example, where one account relates to many invoices, but each invoice only relates to one account. While you can create such a relationship by using the “Relate” field, you will not get a subpanel on the Accounts module that shows all the invoices that relate to in.

In this article I will show you how you can create a many-to-one relation between your custom module and a standard module. This relationship works the same way as the one-to-many relationship with full support for searching and subpanels, only the other way around. My setup is based on my previous two SugarCRM articles: “Keeping SugarCRM under Subversion control” and “Build custom SugarCRM modules in Subversion”. That means that I will be working inside the installable zip package that is generated by the SugarCRM module builder.

You can download the full invoicing package that I created in this article. It has been tested on SugarCRM 5.1.0b.

0: Table of contents

  1. 1: Create the package in the module builder
  2. 2: Build the new relation
    1. 2.1: The language file
    2. 2.2: The layoutdefs
    3. 2.3: The relationship metadata
    4. 2.4: The vardefs
  3. 3: Add the new relation to the layouts
  4. 4: Update the manifest
  5. 5: Deploy

1: Create the package in the module builder

Start off by creating your package in the module builder. In this case I created two simple modules: Invoices and InvoiceLines. I related them and then I published the package. Then I created a clean SugarCRM installation and installed the package on it, giving me two custom modules. Below you can see some screenshots of the setup.

And here is a screenshot of the module after installation.

2: Build the new relation

If you look at the source code of the package then you will find the relationship between the Invoices and InvoiceLines in the SugarModules/relationships/ directory.

  1. build/
  2. src/
  3. | SugarModules/
  4. | | language/
  5. | | modules/
  6. | | relationships/
  7. | | | language/
  8. | | | | inv_InvoiceLines.php
  9. | | | | inv_Invoices.php
  10. | | | layoutdefs/
  11. | | | | inv_Invoices.php
  12. | | | relationships/
  13. | | | | inv_invoices_inv_invoicelinesMetaData.php
  14. | | | vardefs/
  15. | | | | inv_InvoiceLines.php
  16. | | | | inv_Invoices.php
  17. | icons/
  18. | LICENSE.txt
  19. | manifest.php

We are simply going to copy and change those to implement the one-to-many relationship from the Accounts module to the Invoices module.

2.1: The language file

The files in the relationships/languages directory contain the name of the subpanel as it appears on the parent module. We need to add to the Accounts module here so I created Account.php and added a label for the Invoices subpanel title. I opted to use a sensible short name here instead of the overly long names like the module builder generates.

  1. <?php
  2. $mod_strings["LBL_INVOICES"] = "Invoices";
  3. ?>
2.2: The layoutdefs

The layoutdefs contain the setup for the subpanel. Copy layoutdefs/inv_Invoices.php to layoutdefs/Accounts.php and take a look at its contents. It is easy to modify correctly for the relationship between accounts and invoices. As you can see, the relationship name will become accounts_inv_invoices and I have used the shortened title label from the language file above.

  1. <?php
  2. $layout_defs["Accounts"]["subpanel_setup"]["accounts_inv_invoices"] = array (
  3.   'order' => 100,
  4.   'module' => 'inv_Invoices',
  5.   'subpanel_name' => 'default',
  6.   'sort_order' => 'asc',
  7.   'sort_by' => 'id',
  8.   'title_key' => 'LBL_INVOICES',
  9.   'get_subpanel_data' => 'accounts_inv_invoices',
  10. );
  11. ?>
2.3: The relationship metadata

The actual definition of the relationship is in the relationships/relationships directory. It consists of two main parts: the definition of the relationship and the definition of the database table that will hold the relationship, both the fields and the indices. Copy the file inv_invoices_inv_invoicelinesMetaData.php to accounts_inv_invoicesMetaData.php and modify it. As before it is pretty straight forward.

Note that I chose better names for the fields that hols the account and invoice IDs and better names for the indices. The module builder creates pretty nonsensical names for these. By default the module builder creates names from the first part of the left hand module and the last part of the right hand module. This can be outright dangerous if you use long module names. You can get collisions&mdah;that is, the same name for different relationships. One of the many bugs that still haunt the module builder. So, be smart and change the names.

  1. <?php
  2. $dictionary["accounts_inv_invoices"] = array (
  3.   'true_relationship_type' => 'one-to-many',
  4.   'relationships' => array (
  5.     'accounts_inv_invoices' => array (
  6.       'lhs_module' => 'Accounts',
  7.       'lhs_table' => 'accounts',
  8.       'lhs_key' => 'id',
  9.       'rhs_module' => 'inv_Invoices',
  10.       'rhs_table' => 'inv_invoices',
  11.       'rhs_key' => 'id',
  12.       'relationship_type' => 'many-to-many',
  13.       'join_table' => 'accounts_inv_invoices',
  14.       'join_key_lhs' => 'account_id',
  15.       'join_key_rhs' => 'invoice_id',
  16.     ),
  17.   ),
  18.   'table' => 'accounts_inv_invoices',
  19.   'fields' => array (
  20.     array (
  21.       'name' => 'id',
  22.       'type' => 'varchar',
  23.       'len' => 36,
  24.     ),
  25.     array (
  26.       'name' => 'date_modified',
  27.       'type' => 'datetime',
  28.     ),
  29.     array (
  30.       'name' => 'deleted',
  31.       'type' => 'bool',
  32.       'len' => '1',
  33.       'default' => '0',
  34.       'required' => true,
  35.     ),
  36.     array (
  37.       'name' => 'account_id',
  38.       'type' => 'varchar',
  39.       'len' => 36,
  40.     ),
  41.     array (
  42.       'name' => 'invoice_id',
  43.       'type' => 'varchar',
  44.       'len' => 36,
  45.     ),
  46.   ),
  47.   'indices' => array (
  48.     array (
  49.       'name' => 'accounts_inv_invoicesspk',
  50.       'type' => 'primary',
  51.       'fields' => array ('id'),
  52.     ),
  53.     array (
  54.       'name' => 'idx_account_id',
  55.       'type' => 'index',
  56.       'fields' => array ('account_id'),
  57.     ),
  58.     array (
  59.       'name' => 'idx_invoice_id',
  60.       'type' => 'alternate_key',
  61.       'fields' => array ('invoice_id'),
  62.     ),
  63.   ),
  64. );
  65. ?>
2.4: The vardefs

Now we can add the proper fields to the vardefs of the modules. Both the Accounts and Invoices module need a field that points to the relationship, and the Invoices module also needs fields to hold the account name and ID. In relationships/vardefs create Accounts.php as follows:

  1. <?php
  2. $dictionary["Account"]["fields"]["accounts_inv_invoices"] = array (
  3.   'name' => 'accounts_inv_invoices',
  4.   'type' => 'link',
  5.   'relationship' => 'accounts_inv_invoices',
  6.   'source' => 'non-db',
  7. );
  8. ?>

Then, open up relationships/vardefs/inv_Invoices.php and add three fields. One for the relationship, one for the account name and one for the account ID. These are similar to the fields you can find in the vardefs file for the invoice lines.

  1. $dictionary["inv_Invoices"]["fields"]["account_name"] = array (
  2.   'name' => 'account_name',
  3.   'type' => 'relate',
  4.   'source' => 'non-db',
  5.   'vname' => 'LBL_ACCOUNT',
  6.   'save' => true,
  7.   'id_name' => 'account_id',
  8.   'link' => 'accounts_inv_invoices',
  9.   'table' => 'accounts',
  10.   'module' => 'Accounts',
  11.   'rname' => 'name',
  12. );
  14. $dictionary["inv_Invoices"]["fields"]["account_id"] = array (
  15.   'name' => 'account_id',
  16.   'type' => 'link',
  17.   'relationship' => 'accounts_inv_invoices',
  18.   'source' => 'non-db',
  19. );
  21. $dictionary["inv_Invoices"]["fields"]["accounts_inv_invoices"] = array (
  22.   'name' => 'accounts_inv_invoices',
  23.   'type' => 'link',
  24.   'relationship' => 'accounts_inv_invoices',
  25.   'source' => 'non-db',
  26. );

As you can see, I have given the account_name field a label of LBL_ACCOUNT. We need to add this to the language file of the Invoices module. So, open up modules/inv_Invoices/language/en_us.lang.php and add an entry for that label.

  1. <?php
  2. $mod_strings = array (
  3.   ...
  4.   'LBL_ACCOUNT' => 'Account',
  5. );
  6. ?>

3: Add the new relation to the layouts

The next thing to do is adding the account_name field to the editviewdefs, detailviewdefs and listviewdefs on the Invoices module. The editviewdefs and detailviewdefs speak for themselves. The listviewdefs is a little more complicated as you can see below. See also my older article “Fixing one-to-many relationships in SugarCRM 5.1” about adding relationships to listviewdefs, search panels and more.

  1. <?php
  2. $listViewDefs['inv_Invoices'] = array (
  3.   ...
  4.   'ACCOUNT_NAME' =>
  5.   array(
  6.     'width' => '32%',
  7.     'label' => 'LBL_ACCOUNT',
  8.     'default' => true,
  9.     'link' => true,
  10.     'module' => 'Accounts',
  11.     'id' => 'ACCOUNT_ID',
  12.   ),
  13.   ...
  14. );
  15. ?>

4: Update the manifest

The new relationship is finished now but we need to make sure that the module loader loads the files that we just added. Open up manifest.php and add the relevant entries as shown below.

  1. $installdefs = array (
  2.   ...
  3.   'layoutdefs' => array (
  4.     ...
  5.     array (
  6.       'from' => '<basepath>/SugarModules/relationships/layoutdefs/Accounts.php',
  7.       'to_module' => 'Accounts',
  8.     ),
  9.   ),
  10.   'relationships' => array (
  11.     ...
  12.     array (
  13.       'meta_data' => '<basepath>/SugarModules/relationships/relationships/accounts_inv_invoicesMetaData.php',
  14.     ),
  15.   ),
  16.   ...
  17.   'language' => array (
  18.     ...
  19.     array (
  20.       'from' => '<basepath>/SugarModules/relationships/language/Accounts.php',
  21.       'to_module' => 'Accounts',
  22.       'language' => 'en_us',
  23.     ),
  24.   ),
  25.   'vardefs' => array (
  26.     ...
  27.     array (
  28.       'from' => '<basepath>/SugarModules/relationships/vardefs/Accounts.php',
  29.       'to_module' => 'Accounts',
  30.     ),
  31.   ),
  32.   ...
  33. );

5: Deploy

Now the package is finished. You can now run the script to generate the installable zip and install it on your SugarCRM installation. Below are some screenshots of the Invoices module with the newly added relationship.

And this is what the edit view looks like.

You can download the full invoicing package that I created in this article. It has been tested on SugarCRM 5.1.0b.

Creative Commons Attribution-ShareAlike


#1 David


please can you send me the script or provide it in another way? thanking you in anticipation.

best regards,


#2 Sander Marechal (

Hi David,

The script is part of my article Build custom SugarCRM modules in Subversion. That article also explains how to use the script. Here is a direct download of

#3 Jim Thornton

I have followed your guide exactly. I have done it for my own custom module which was resulting in the Accounts modules displaying a new tab "Other". When you click on this tab, there is NO subpanel displayed at all.

I later followed the EXACT guide creating a Package (Invoicing) and two modules (Invoices and InvoiceLines) thinking that I did something wrong. After going step-by-step it resulted in the exact same behaviour.

I have also done the following:
Quick repair and rebuild
Rebuild Relationships
Turned developer mode ON

Nothing is helping. Here is the error from the sugarcrm.log:

04/13/09 23:58:29 [4240][1][FATAL] Bad subpanel definition, it has incorrect value for get_subpanel_data property accounts_inv_invoices

Could you PLEASE help me. I cannot figure this out for the life of me.

I'm running CE 5.2.0
I'm running in a Windows enviroment with Apache 2.2

#4 Sander Marechal (

Jim, there is a bug in the zip file that contains my invoices module. The bug does not exist in the article itself however.

Open up the zip of the installable package that you created and go to SugarModules/relationships/vardefs and open Accounts.php. Make sure that it starts with:


That is, "Account" and not "Accounts" (note the s at the end). Change it, re-install the package and do a "quick rebuild and repair" twice. Then you should see the subpanel.

#5 Anonymous Coward (

I am getting this error while installing new package. Please help.

Fatal error: require() [function.require]: Failed opening required 'modules/rprt_xEducation/rprt_xEducation.php' (include_path='D:\wamp\www\Code\crm\include/..;.;C:\php5\pear') in D:\wamp\www\Code\crm\modules\Administration\RebuildRelationship.php on line 71

xEducation module is created earliar but never installed. so why this error coming? Please help me out.

#6 Sander Marechal (

I cannot say without seeing the package, but it looks like either your current SugarCRM installation or your new package makes a reference or relation to rprt_xEducation. If I were you I'd grep your SugarCRM directory and your package to see where those references are and why they are there.

#7 Roman Mottino

Thanks Sander!
This howto was very usefull to me.

#8 matt

Very helpful! Is it also supposed to work when you are creating a many-to-one relationship for two of my own modules? I am getting

DeployedMetaDataImplementation: Modulename MYNAME is not a Deployed Module

#9 Sander Marechal (

I have never tried matt. Perhaps with a few tweaks it will work. The SugarCRM Module Builder can't create true one-to-many relationships. It creates many-to-many relationships with a special flag that it should treat them as one-to-many (yes, another crazy thing about the SugarCRM code base). So, it should not need many changes to make it work with many-to-many relationships.

#10 tedcowan

I am a newbie to SugarCRM and building a simple order capture module. Are you suggesting that out of the box, SugarCRM (5.2.0k) will create one to many relationships but the parent module's detail view will not automatically show the related child records? This has been my experience so far.

It also appears that once I begin down this road of manual package customization, I cannot use Module Builder again to tweak my UI, correct? This would suggest that I shouldn't buy Enhanced Studio either for the same reason. Please confirm.


#11 Sander Marechal (

the parent module's detail view will not automatically show the related child records?

That depends. If you use the relationship editor in Studio you will get a subpanel on the parent detail view. If you use relate-to fields then you will not get the subpanel.

The SugarCRM module builder only allows relate-to fields to built-in modules. This article explains how you can create a relationship to a built-in module like the relationship editor would create.

This would suggest that I shouldn't buy Enhanced Studio either for the same reason.

I have never used enhanced studio. But I have two remarks:

1) Studio is not the same as the module builder. With the module builder you create new packages and modules that you can install on other SugarCRM instances. With Studio you can change certain aspects of already installed modules on a running Sugar. There's a big difference.

2) If you know how to code PHP I see no reason to buy Enhanced Studio.

#12 Purushotham

hi sir,

i want each subpanel come as a pop-up.

it means some icons are there in detailview, if we click on that icons then related subpanel comes as pop-up(lightbox)

Comments have been retired for this article.