Fixing one-to-many relationships in SugarCRM 5.1

by Sander Marechal

Updated 2008-12-12. With the release of SugarCRM 5.0 came the new Module Builder that allows administrators to easily create new custom modules. But it was quite buggy and there was no way to create one-to-many relationships between custom modules. Then came SugarCRM 5.1 that promised to resolve the issues. Besides the Relationship Editor in the Module Builder there are also the “Relate” and “Flex Relate” fields. Two different (and incompatible ways) to create relationships. Not to mention that the Relationship Editor in the Module Builder works differently from the same editor in Studio. The former creates relationships in code, the latter in the database. And there are still heaps of bugs*.

The biggest problem I have had is with one-to-many relationships, the most common relationship type when you’re creating modules. If you use “Relate” fields then no real relationship is created and you won’t get a subpanel in the parent module. If you use relationships created in the Module Builder then the related fields show up badly in the Edit and Detail views, and you cannot use them at all in the List view, subpanel or search panels.

There are four main problems when you create a one-to-many relationship in the Module Builder: The Edit and Detail view do not properly contain the related fields in the child module, the related fields are not available in the List view, are not available in the subpanel and are not available on the Search panel. Here is how to fix all these issues.

0: Table of contents

1: Module setup

In this article I will assume that you are creating two custom modules: A Computer module and an Application module. Each Computer can have one or more Applications. Simply create the two modules in the Module Builder, go to the Computers module and add a one-to-many relation to Applications.

The relationship in SugarCRM

2: Fixing the Edit and Detail views

If you now go to the Applications module and open the Edit view layout, you can see that the field that is supposed to point to the Computer parent module is not labeled.

The bug in the module layout

Luckily this is quite easy to fix, before the package is deployed. First open up the en_us.lang.php file for the Applications mode. You can find it under the custom/modulebuilder directory. Add a label for the computer as shown below:

custom/modulebuilder/packages/ComputerShop/modules/Applications/languages/en_us.lang.php
  1.   // Beginning of file snipped
  2.   ...
  3.   'LBL_ACTIVITIES_SUBPANEL_TITLE' => 'Activities',
  4.   'LBL_SHOP_APPLICATIONS_SUBPANEL_TITLE' => 'Applications',
  5.   'LBL_NEW_FORM_TITLE' => 'New Applications',
  6.   'LBL_COMPUTER' => 'Computer', // New label
  7. );

Now you can add this label in the editviewdefs.php and detailviewdefs.php files. Look for the relationship and add the label as shown below:

custom/modulebuilder/packages/ComputerShop/modules/Applications/metadata/editviewdefs.php
  1. ...
  2.   1 =>
  3.   array (
  4.     'name' => 'shop_computers_shop_applications_name',
  5.     'label' => 'LBL_COMPUTER', // New label
  6.   ),
  7. ...

Save your changes and reload the layout in the layout editor. Now the label appears correctly. You can freely rearrange the layout after this. The Module Builder will not remove your added labels.

3: Adding the Computer field to the Applications list view

Before you can fix the list view and the subpanel, you will first need to deploy your package. That’s because Sugar does not really build the relations until the package is installed. During this process all kinds of tables and variables are created and we need these. So, go ahead and deploy your package. Then create a Computer entry, an Application entry and relate the Application to the Computer. Now we will add the Computer field to the Applications list view. It is quite useful to turn on “Developer mode” during the next steps, so you can immediately see the results.

Update: Note that I do no longer deploy the package. I have the module builder generate the installable zip package. I unzip it, import it into Subversion, edit the code and then re-zip it. Please see my two part article about SugarCRM and Subversion:

First we need to look up the name of the module and the field names associated with the relationship. We will need these later. You can find the module name easily by looking at the modules directory. It will have been prefixed with the value you set when creating the package. In this case it is shop_Applications. Now open up the relationship definition and note down the field names for the relationships. There will be three fields, as show in the example below.

custom/Extensions/modules/shop_Applications/Ext/Vardefs/ComputerShop.php
  1. $dictionary["shop_Applications"]["fields"]["shop_computers_shop_applications"] = array (
  2.   'name' => 'shop_computers_shop_applications',
  3.   ...
  4. );
  5.  
  6. $dictionary["shop_Applications"]["fields"]["shop_computers_shop_applications_name"] = array (
  7.   'name' => 'shop_computers_shop_applications_name',
  8.   ...
  9. );
  10.  
  11. $dictionary["shop_Applications"]["fields"]["shop_comput_computers_ida"] = array (
  12.   'name' => 'shop_comput_computers_ida',
  13.   ...
  14. );

The contents of the first “name” field is the relationship itself, the second one is the name of the Computer that the Application is associated with and the third one is the ID of the Computer. Now we can edit the listviewdefs.php for the Applications module and add the Computer field, as shown below.

modules/shop_Applications/metadata/listviewdefs.php
  1. $module_name = 'shop_Applications';
  2. $listViewDefs [$module_name] =
  3. array (
  4.   'NAME' =>
  5.   array (
  6.     ...
  7.   ),
  8.   // The new column begins here
  9.   'SHOP_COMPUTERS_SHOP_APPLICATIONS_NAME' => // Name of the field that holds the Computer name, in ALL CAPS
  10.   array (
  11.     'width' => '32%',                        // Pick a suitable width
  12.     'label' => 'LBL_COMPUTER',               // The label you created when fixing the Edit and Detail view
  13.     'default' => true,
  14.     'link' => true,                          // Make it a link to the Computer Detailview
  15.     'module' => 'shop_Computers',            // The parent module name
  16.     'id' => 'SHOP_COMPUT_COMPUTERS_IDA',     // The field that contains the Computer ID, in ALL CAPS
  17.   ),
  18.   // End of the new column
  19.   'ASSIGNED_USER_NAME' =>
  20.     ...
  21.   ),
  22.   'TEAM_NAME' =>
  23.   array (
  24.     ...
  25.   ),
  26. );

Save it, and take a look at the Applications tab in Sugar.

The fixed listview

4: Adding the Computer field to the Applications subpanel

Fixing the subpanel is very similar to fixing the list view. Open up subpanels/default.php and modify it according to the example below.

modules/shop_Applications/metadata/subpanels/default.php
  1. $module_name='shop_Applications';
  2. $subpanel_layout = array (
  3.   'top_buttons' =>
  4.   array (
  5.     ...
  6.   ),
  7.   'where' => '',
  8.   'list_fields' =>
  9.   array (
  10.     'name' =>
  11.     array (
  12.       ...
  13.     ),
  14.     // The new column begins here
  15.     'shop_computers_shop_applications_name' =>             // Name of the field that holds the Computer name, not in caps this time
  16.     array (
  17.       'widget_class' => 'SubPanelDetailViewLink',          // We want to make it a link to the computer
  18.       'width' => '35%',                                    // Pick a suitable width
  19.       'default' => true,
  20.       'vname' => 'LBL_COMPUTER',                           // The label you created when fixing the Edit and Detail view
  21.       'target_module' => 'shop_Applications',              // The parent module name
  22.       'target_record_key' => 'SHOP_COMPUT_COMPUTERS_IDA',  // The field that contains the Computer ID, in ALL CAPS
  23.       ),
  24.     // End of the new column
  25.     'edit_button' =>
  26.     array (
  27.       ...
  28.     ),
  29.     'remove_button' =>
  30.     array (
  31.       ...
  32.     ),
  33.   ),
  34. );

Save your changes and take a look at the Detailview for the Computer.

The fixed subpanel

5: Adding the Computer field to the Applications search panel

The last fix we need to make is to add the Computer field to the Basic and Advanced search pages. Luckily, these are a lot simpler. Open up metadata/searchdefs.php and make the changes according to the example below.

modules/shop_Applications/metadata/searchdefs.php
  1. $module_name = 'shop_Applications';
  2. $searchdefs [$module_name] =
  3. array (
  4.   'layout' =>
  5.   array (
  6.     'basic_search' =>
  7.     array (
  8.       'name' =>
  9.       array (
  10.         ...
  11.       ),
  12.       // Start of the new search field
  13.       'computer' =>                                              // You can put any name you want here
  14.       array (
  15.               'name' => 'shop_computers_shop_applications_name', // Name of the field that holds the Computer name
  16.               'width' => '10%',                                  // Pick a suitable width
  17.               'default' => true,
  18.               'label' => 'LBL_COMPUTER',                         // The label you created when fixing the Edit and Detail view
  19.       ),
  20.       // End of the new search field
  21.       ...
  22.     ),
  23.     'advanced_search' =>
  24.     array (
  25.       'name' =>
  26.       array (
  27.         ...
  28.       ),
  29.       // Start of the new search field
  30.       // This is identical to the search field above
  31.       'computer' =>
  32.       array (
  33.               'name' => 'shop_computers_shop_applications_name',
  34.               'width' => '10%',
  35.               'default' => true,
  36.               'label' => 'LBL_COMPUTER',
  37.       ),
  38.       // End of the new search field
  39.       ...
  40.     ),
  41.   ),
  42.   ...
  43. );

And now your search box will look like this.

The fixed search panel

I hope this spares someone from the agony, hair-pulling and cursing I did, trying to figure this all out.

6: Footnotes

…heaps of bugs*. The enormous amount of bugs in SugarCRM is a real problem. Their bugtracker is choke-full of bugs of which only few ever seem to get solved. I have waded through quite a bit of the SugarCRM source code and I am not surprised there are so many bugs. It’s quite a mess, typical of their closed development model. There is no peer review and no decent way for the community to contribute back changes. They release SugarCRM under GPLv3 but I don’t think they have really understood how FOSS works: FOSS creates superior software because development is totally open and transparent. Many eyes make all bugs shallow and there are plenty of people to fix things and review changes before they are merged in. A FOSS license like the GPLv3 is only a means to that end; A required item for the FOSS development process to happen. The SugarCRM development model is as closed as that of any proprietary shop, so the quality of the code that flows out of SugarCRM is of equal low quality as most proprietary software is.

Creative Commons Attribution-ShareAlike

Comments

#1 puneet kumar singh

hi boss,
your conversation for building the new module is good but i don't understand how we manage to advance search and saved search and layouts.

thanx

#2 Sander Marechal (http://www.jejik.com)

@puneet kumar singh:

The module builder creates them for you. Just create a module in the module builder and fix the labels on the relationships as in step 1. Then publish your module and install it. After that you can edit the layouts and search panels as shown in step 2, 3 and 4.

#3 Jim Barnes (http://jimbarnes.info)

Sander ; Greetings, found you on a google

just wrestling with the many bugs myself looking for an easy fix to adding custom subpanels to existing layouts without creating new modules.

Your comment regarding having waded through the code is of interest as i would like to discuss with someone ideas related to a re-gen of it and an inclusion of it with other similar gpl3 systems to create something that is of industrial strength with cms, erp, crm, voip etc integrated

please contact my email if your interested

#4 Jim Barnes (http://jimbarnes.info)

Sander ; Greetings again,

basic question can i add field editing capability to a subpanel, so a tick box can be checked and the record saved without having to pull up the edit screen for the record

#5 Sander Marechal (http://www.jejik.com)

i would like to discuss with someone ideas related to a re-gen of it and an inclusion of it with other similar gpl3 systems to create something that is of industrial strength with cms, erp, crm, voip etc integrated


I am interested in tossing some ideas around but I do not have the time to participate in such a project myself. My general idea on the matter is simply this: Don't.

SugarCRM is rotten at it's core. It's developed behind closed doors by programmers who focus on features rather than quality. The best thing you can do is start from scratch with a solid base and see if there is anything salvageable after you got the core right.

Toss out SugarBean, Link and vardefs, pick a tried and proven base MVC like Cake PHP, Symfony or the Zend framework and work from there. Use as many external components as you can. That's one problem that SugarCRM and other so-called "commercial open source" packages have. They sell proprietary enterprise editions so they cannot use GPL code. They can only use zlib/bsd-style code to build on. A true free/open-source CMS system can build on the wealth of other copyleft software out there and should be able to build something relatively quickly.

Can i add field editing capability to a subpanel, so a tick box can be checked and the record saved without having to pull up the edit screen for the record?


I have no idea on how to do this without a lot of custom code. It's not a problem I had to face with SugarCRM so far.

#6 Jesse

I think that sugar is buggy and loaded with bugs because the paying customers crave more features and it makes its way into the system. Then it doesn't get updated or fixed until the customer has a problem with it. Sugar is a dream to work with compared to others like Vtiger. That one is really a mess its codebase is so buggy and hard to follow. I have found sugar useful for prototyping a database. Then using an external application framework like cakephp I can produce a more customized application quickly. The only reason I result to this is because I hate developing on the database side. Also you could try http://www.wavemaker.com/ which make's ajax apps with a point and click interface.

I do agree sugarcrm is buggy and slow, but it also gives you a lot of features in "mostly free" software that you would have to pay for otherwise.

#7 Sander Marechal (http://www.jejik.com)

I haven't used many of the other "open source" CRM systems so I can't really compare that. I have used the big brand closed ones in the past (e.g. SAP) and know quite a lot of non-CRM open source web applications. Sugar doesn't compare too well to either of them.

You're absolutely right that featuritis is the problem. It matches what I said in the footnote of the article. Sugar releases it's software both as closed source and a less featureful open source variant. They call it "commercial open source" which is a misnomer in my opinion. Like I said, the true power of open source code does n't come from the license but from the open development model. It is that development model that makes open source code so much better: Enough eyes make all bugs shallow.

SugarCRM derives its income from people who have the closed source version. It must keep adding new features to the closed source version or people will not pay for it anymore (because open source programmers implement those same features as extensions to the open source version). Their developers are so busy building features that they do not have time to solve the bugs.

And that is a problem. The development process at SugarCRM is also closed. It has to be, or you cannot make a closed source enterprise version. So, there is no peer code review and the community can not prevent bugs from being committed to the repository. All we can do is write patches and submit them, but the programmers at SugarCRM are too busy to review and commit them all.

In short, the so-called "commercial open source" marries the worst of two worlds in my opinion. I really hope that some day they understand this at SugarCRM and open up their development process. They have a lot of potential but they need to let go of this "enterprise" mentality.

#8 omer121

Hi Sander and Thanks for your fix that helped me for detail and edit view.

I have not yet managed to get listview working and don't understand what I am mistaking.

Here is my case:

The first module is HMR_Employees (Because I can't ad custom fields to User table)
The second is HRM_Incomes

I just want to show the employee that is linked to the income in listview.

The Employee Subpanel (detailview) is OK and shows all Incomes records
The Income Subpanel (detailvew) is OK and shows the Employee Name

My problem is that Incomes listview shows the colomn with the right label but without Employee name (Empty)....

The HTML code generated is :

<a href="http://#" rel="nofollow" ></a><br />


The id (deabfaec-10bf-59fb-ae8a-4937adc30d92) is the right one bud nothing is displayed

custom/Extension/modules/HMR_Incomes/Ext/Vardefs/HRM.php

...
$dictionary["HMR_Incomes"]["fields"]["hmr_employees_hmr_incomes"] = array (
'name' => 'hmr_employees_hmr_incomes',
....
$dictionary["HMR_Incomes"]["fields"]["hmr_employees_hmr_incomes_name"] = array (
'name' => 'hmr_employees_hmr_incomes_name',
....
'name' => 'hmr_employe_employees_ida',
....


custom/modulebuilder/packages/HRM/modules/Incomes/metadata/listviewdefs.php

.....
<?php
// created: 2008-12-04 10:48:13
$dictionary["HMR_Incomes"]["fields"]["hmr_employe_employees_ida"] = array (
'name' => 'hmr_employe_employees_ida',
'type' => 'link',
'relationship' => 'hmr_employees_hmr_incomes',
'source' => 'non-db',
);
?>
.....


Do you have any clue to display Employee name?

Regards
Gaël

#9 Sander Marechal (http://www.jejik.com)

Hi Gaël,

I think you made an error in your post above. Your code snippet for custom/modulebuilder/packages/HRM/modules/Incomes/metadata/listviewdefs.php seems to be a bit of the vardefs file, not the listviewdefs file.

Can you post the correct bit from the listviewdefs file?

Can you also post the full array of $dictionary["HMR_Incomes"]["fields"]["hmr_employees_hmr_incomes_name"] from the file custom/Extension/modules/HMR_Incomes/Ext/Vardefs/HRM.php please?

#10 omer121

Oups, You are right ....

Here is the correct file and the comple array for the relationship.

custom/modulebuilder/packages/HRM/modules/Incomes/metadata/listviewdefs.php

....
'DATE_MODIFIED' =>
array (
'width' => '10%',
'label' => 'LBL_DATE_MODIFIED',
'default' => true,
),
'HRM_EMPLOYEES_HRM_INCOMES_NAME' =>
array (
'width' => '30%',
'label' => 'LBL_EMPLOYEE',
'default' => true,
'link' => true,
'module' => 'HMR_Employees',
'id' => 'HRM_EMPLOYE_EMPLOYEES_IDA',
),
'DESCRIPTION' =>
array (
'width' => '10%',
'label' => 'LBL_DESCRIPTION',
'sortable' => false,
'default' => true,
),
.....


custom/Extension/modules/HMR_Incomes/Ext/Vardefs/HRM.php


......
<?php
// created: 2008-12-04 12:22:38
$dictionary["HMR_Incomes"]["fields"]["hmr_employees_hmr_incomes"] = array (
'name' => 'hmr_employees_hmr_incomes',
'type' => 'link',
'relationship' => 'hmr_employees_hmr_incomes',
'source' => 'non-db',
);
?>
<?php
// created: 2008-12-04 12:22:38
$dictionary["HMR_Incomes"]["fields"]["hmr_employees_hmr_incomes_name"] = array (
'name' => 'hmr_employees_hmr_incomes_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_HMR_EMPLOYEES_HMR_INCOMES_FROM_HMR_EMPLOYEES_TITLE',
'save' => true,
'id_name' => 'hmr_employe_employees_ida',
'link' => 'hmr_employees_hmr_incomes',
'table' => 'hmr_employees',
'module' => 'HMR_Employees',
'rname' => 'name',
);
?>
<?php
// created: 2008-12-04 12:22:38
$dictionary["HMR_Incomes"]["fields"]["hmr_employe_employees_ida"] = array (
'name' => 'hmr_employe_employees_ida',
'type' => 'link',
'relationship' => 'hmr_employees_hmr_incomes',
'source' => 'non-db',
);
?>
.....


Thanks for your help

Gaël

#11 Sander Marechal (http://www.jejik.com)

I see it!

In the listview you refer to HRM_* but in the vardefs it says hmr (last two letters swapped). Change the listviewdef to use HMR_* instead and it should work :-)

#12 omer121

F***g Dyslexia!

Thanks for your help.

I have changed it but still doesn't work.

I have build a new module for Employees that is not 'person' type but 'basic' and all went OK... May be my pb comes from the 'person' preconfigured module.

However I will just start from standard for my Human Ressource package.

Thanks

#13 Sander Marechal (http://www.jejik.com)

I have changed it but still doesn't work.


Did you change all the HRMs to HMR? There are three in the listviewdefs. It is twice in the word HRM_EMPLOYEES_HRM_INCOMES_NAME and once more in HRM_EMPLOYE_EMPLOYEES_IDA.

#14 omer121

Yes I did.

I also tried to make a brand new package with different name and the pb is the same. When I make a standard ('Basic' Not 'preson') module for employees, all is OK.
This is not a big deal. I will just rebuild a 'person' module from 'basic'...

Thanks

#15 Igor Vitorac

Hello Sander,
Nice post! I was preparing to post this to sugar forum, but unfortunately I didn't have time to prepare such nice post.
I have to add that you don't have to deploy module, but you can do another thing. You can publish module, then go to that zip file and make appropriate changes. After that you install that module (modified zip). Of course, this is the longer way, but more elegant. Once you prepare package, you can distribute it. There is no need for changing the sugar after deploying the package.
I am using one PC for developing, the second for installing and testing, because I have notice some problems when developing and deploying on the same computer.

#16 Sander Marechal (http://www.jejik.com)

Hi Igor,

I know about editing the installable zip before installing it. I started doing that a short while after writing this post. See my two-part series about this:

Keeping SugarCRM under Subversion control
Build custom SugarCRM modules in Subversion

I have notice some problems when developing and deploying on the same computer.


I have set up Apache in such a way that I can run many different Sugar instances on one computer. That way it does work. Use the module builder in one instance and install the package on another. See my article Easily develop and deploy web applications from subversion for this. Basically I setup Apache with a wildcard hostname.

#17 vishwasrao salunkhe

Hi boss ,
Thanx for fixing these bugs .I was lookig for these only since last few days .I got it ,u solved my problem.
Thanx once again.

#18 kelie

I have a customer interested in building a custom module in sugar 5.1. We have just migrated them from 4.20 - 5.0 and then to 5.1. This was a painful process. There was also some customizations that took place in 4.20 those did not make the migration. Did anyone do custom modules in 5.0 and when they upgraded to 5.1 was it smooth and did they still work after the upgrade? This is what 5.0 is supposed to do make upgrading easier even after customizations. Please let me know your thoughts.

Thanks
Kelie

#19 Sander Marechal (http://www.jejik.com)

Hi Kelie. I have not done any 5.0 to 5.1 migrations. But I have a few custom modules that I created for 5.1 that I upgraded to 5.2. They worked flawlessly and did not require any changes.

Note that these modules were not very complex. If your custom modules are very complex then you may run into some trouble. Sugar keeps changing on the inside and there are plenty of bugs that can trip you up. Beware the "currency" field if you use non-US decimal and thousands separators!

#20 Julie

How do I display other fields from the parent module on the child module ?

#21 Sander Marechal (http://www.jejik.com)

Hi Julie. You display them the same way that you display the name of the parent field. In the vardefs file of the child module, simply copy/paste the field that displays the "name" field of the parent and make it display another field. You can do this my adding the "rname" parameter. If the "rname" is not set then it defaults to "name", but you can point it to any field in the parent module. The id_name, table, link and module fields should all stay the same. So:

...
  'parent_whatever' = array(
    'name' => 'parent_whatever',
    'type' => 'relate',
    ...
    'module' => 'ParentModule',
    'rname' => 'some_field',
  ),
...

#22 Julie

Hi

Thanks for above I tried it but no luck - any ideas what I am doing wrong ?
I have 2 modules
products ->> Ipallocations

parent name field from vardefs ( in modules/IPallocations directory )

'suite_products_id_c' =>
array (
'required' => false,
'name' => 'suite_products_id_c',
'vname' => '',
'type' => 'id',
'massupdate' => 0,
'comments' => '',
'help' => '',
'importable' => 'true',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => 36,
),
Copy and amended field
'suite_products_location' =>
array (
'required' => false,
'name' => 'suite_products_location',
'module' => 'Products',
'rname' => 'location'
'vname' => 'LBL_DMZ',
'type' => 'relate',
'massupdate' => 0,
'comments' => '',
'help' => '',
'importable' => 'true',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => 36,
),

Also if the field from the parent field is a related field how do I manage this. I want the customer field ( Which is a related field eg customer from accounts table)from my products table displayed when entering my ipalloaction details.

Many Thanks in advance.

Julie

#23 Sander Marechal (http://www.jejik.com)

Hi Julie. You seem to me missing a few fields in your suite_products_location definition. Try this instead:

'suite_products_location' =>
  'name' => 'suite_products_location',
  'type' => 'relate',
  'source' => 'non-db',
  'vname' => 'LBL_DMZ',
  'save' => false,
  'id_name' => 'suite_products_id_c',
  'table' => 'products', // database table that stores the products
  'module' => 'Products',
  'rname' => 'location',
),

#24 spaps

Hi Sander

I don't know which version of 5.1 this solution is written for, I suspect it is for 5.1.0a.

I am now using 5.1.0b.
When I create the same two modules as in your example, I get the same problems as you have described, and they could be solved the way you shows,
but I get another serious problem: the Applications submodule does not show up at all.

You wouldn't have guidelines for how to solve that would you?

#25 Sander Marechal (http://www.jejik.com)

spaps, are you sure that you set-up your modules correctly? Sure that they both are displayed with a tab and all that? Are you allowed access th the modules?

The above has been tested with 5.1.0 GA, 5.1.0a and 5.1.0b.

#26 spaps

Oh...
now I'm blushing...
thanks Sander for reminding me!
I hadn't Configured the tabs and set the new modules to displayed.
It works fine now!

Thanks

#27 Julie

Sorry to keep coming back on this question.

I think I am moving forward .

I sometimes have to use repair database to clear cache and align database with vardefs.

When I use create from my products module I get the following on the create screen for the IPAllocation

Location displayed as the Node Name from the products table. Then if I use the select button it will pull back the location from the products table. I want this displayed as default .
My definition in vardefs (and I changed the field in editview to point to suite_products_location ) is as follows
'suite_products_location' =>
array (
'name' => 'suite_products_location',
'vname' => 'LBL_LOCATION',
'module' => 'Suite_Products',
'link' => 'suite_products',
'table' => 'suite_products',
'rname' => 'location',
'id_name' => 'suite_produe_products_ida',
'type' => 'relate',
'dbtype' => 'id',
)

When I try to save I get error message No match for field location.

Apologies for asking for help again as I am sure it is just my inexperience.

Thanks for your patience.

Julie

#28 Sander Marechal (http://www.jejik.com)

Try setting "'save' => false" and/or "'source' => 'non-db'" on the above field. After all, you don't want to save the location itself but just the associated product ID field that points to the proper product.

#29 Vishwasrao

Hi This is great solution but not complete one.I faced problem with this.Let i created custom modules A,B,C,D with module builder,When i create relationship between A & B,It works as u said but when i am going to create relationship between B & C,it is not working that field is not appearing in the B's editview without label.

Are you getting me . Plz reply me as soon as possible.

#30 Sander Marechal (http://www.jejik.com)

Are you using the relationship editor or the "Relate to" field? My solution only works for relationships created in the relationships tab, not with "relate to" fields.

But you can always add the field to the vardefs and edit views yourself. Just look at the source code for the other modules. It's not that hard.

#31 Vishwasrao

Hi
thanx for your reply.
It worked for me also.
Thanx once again it worked great for me.

#32 Neil Robinson (http://nezil.vox.com)

I've gone through your tutorial, which was a massive help - thanks for that.

Now most things work fine, but even though the edit and details feilds seem to work fine (data is stored in the field OK, and shown in the details view), as soon as I add the feild to the list view, all of my records disappear. If I don't add the coloumn to the list view, but select something in the related field search box, then again, everything disappears.

I should explain that the relationship is between documents and users. I needed to assign a user to each document, so that I can see not just who uploaded, but who has been assigned for managing that file.

Hoping you can help...

Neil.

#33 Sander Marechal (http://www.jejik.com)

Neil, that's a tough call. This tutorial is aimed at custom modules. You seem to be creating a new relation between two standard modules. These usually work differently because of all the customisations that SugarCRM makes.

You probably didn't create this relation using the module builder but using the relationship editor in Studio. These relationships are different than the relationships that the module builder creates. SugarCRM has three different (and incompatible) ways of relating modules: Using "Relate" fields, using the Module Builder and using Studio. Don't ask me why they did that. It makes no sense at all.

For added complexity there already is a relation between these two standard modules. I'm afraid I can't help you much with this.

When data disappears from a list view then this usually indicates that the SQL query failed. The fisrt thing you should do is look at your sugarcrm.log and look at the failed query. Perhaps the cause of the failure is obvious. If not then I suggest you install XDebug on the server and attach a proper PHP debugger. Open the list view, start the debugger and then click "refresh" on the list and start debugging. Just step through the code and try to understand why the query is being built to fail. Hopefully that will help you to understand how you can fix it.

#34 Calvin

I am new to SugarCRM and I am not sure what is the best way to do this. It's good to know (or bad I suppose) that there are 3 different ways to relate modules. What I want to accomplish is to create a few 1 to many relationships between Accounts and my custom module. Any help is much appreciated!

#35 Sander Marechal (http://www.jejik.com)

Calvin, it depends on what module is the "one" and what module is the "many". If you want to relate item in your custom module to multiple Accounts, use the relationship editor in the Module Builder to do that, then read this article to fix the last few bugs.

The other way around (one Account having multiple of your custom items) is harder. There are two possible ways to do it. The first is to add a "Relate to" field to your custom module that points to Accounts. It works, but has various bugs such as the lack of a subpanel on the Accounts module. I don't know how to solve that, but you can probably find the answer to that on the SugarCRM forums.

The second way to create such a relationship is manually, using the same method as the Relationship Editor in the Module Builder uses. This method is described in my article "SugarCRM many-to-one relations to standard modules".

#36 Anonymous Coward

You should write a book about customizing Sugar, your articles are higher quality then the documentation or any commercial literature currently available

#37 joeb

I am using Sugar 5.2 CE and I can't seem to get the field to show in listview (Step 3 above). I have two custom modules (FCM_Firms and FCM_Reps) and I was able to fix the Edit and Detail views as described.

Would it be possible to tell me what I may be missing here?

custom/Extensions/modules/acm_FCM_Reps/Ext/Vardefs/ACM_Firms.php:

$dictionary["acm_FCM_Reps"]["fields"]["acm_fcm_firms_acm_fcm_reps"] = array (
'name' => 'acm_fcm_firms_acm_fcm_reps',
'type' => 'link',
'relationship' => 'acm_fcm_firms_acm_fcm_reps',
'source' => 'non-db',
'side' => 'right',
);

$dictionary["acm_FCM_Reps"]["fields"]["acm_fcm_firms_acm_fcm_reps_name"] = array (
'name' => 'acm_fcm_firms_acm_fcm_reps_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_ACM_FCM_FIRMS_ACM_FCM_REPS_FROM_ACM_FCM_FIRMS_TITLE',
'save' => true,
'id_name' => 'acm_fcm_fi53c6m_firms_ida',
'link' => 'acm_fcm_firms_acm_fcm_reps',
'table' => 'acm_fcm_firms',
'module' => 'acm_FCM_Firms',
'rname' => 'name',
);

$dictionary["acm_FCM_Reps"]["fields"]["acm_fcm_fi53c6m_firms_ida"] = array (
'name' => 'acm_fcm_fi53c6m_firms_ida',
'type' => 'link',
'relationship' => 'acm_fcm_firms_acm_fcm_reps',
'source' => 'non-db',
'side' => 'right',
);

modules/acm_FCM_Reps/metadata/listviewdefs.php:

'ACM_FCM_FIRMS_ACM_FCM_REPS_NAME' =>
array (
'width' => '15%',
'label' => 'LBL_FCM_FIRM',
'default' => true,
'link' => true,
'module' => 'acm_FCM_Firms',
'id' => 'ACM_FCM_FI53C6M_FIRMS_IDA',
),

#38 Sander Marechal (http://www.jejik.com)

It's hard to say joeb. Apart from the "'side' => 'right'" bits your code looks identical to mine and I can't see any typos. It should work. Are you sure you're not overriding the listviewdef through Studio or something? Are both module's tabs visible/accessible (you can't show fields for modules you don't have access to)? Did you do "admin -> repair -> quick rebuild and repair"? As I said, turning on developer mode really helps here because things won't get cached that aggressively.

Other than that, I'd have to see/test/debug the source myself to find the problem.

#39 Gregm

I have the same problem as Joeb

except I had sucessfully used your techniques previously and got the related info to display in list view.

But now in 5.2 those same settings do not work.

I am not clear on exactly which sugar patch broke things as it was for something I was working on over time.

I wonder if some recent change to the requirement of detail or capitalization in the listviewdefs.php field is causing this to be a problem.

If someone knows how to get listviews to display in 5.2.0d can you please post your syntax?

I have been using the following sort of code:
'BRD_B15ISO_BRD_B20TSOCASES_NAME' =>
array (
'width' => '25%',
'label' => 'LBL_B15ISO',
'id' => 'BRD_B15ISODD8B_B15ISO_IDA',
'module' => 'brd_B15ISO',
'link' => true,
'default' => true,
'sortable' => true,
),

#40 Sander Marechal (http://www.jejik.com)

Perhaps it's a specific issue for 5.2.0d? I just upgraded my test installation to 5.2.0h and both my parent fields as well as my grandparent fields are working fine. Here's the parent and grandparent field in the InvoiceLines listviewdef.php (modules: Accounts -> Invoices -> InvoiceLines):

        'INV_INVOICES_INV_INVOICELINES_NAME' => array (
                'width' => '32%',
                'label' => 'LBL_INV_INVOICES_INV_INVOICELINES_FROM_INV_INVOICES_TITLE',
                'default' => true,
                'link' => true,
                'module' => 'inv_Invoices',
                'id' => 'INV_INVOICEV_INVOICES_IDA',
        ),
        'ACCOUNT_NAME' => array (
                'width' => '32%',
                'label' => 'LBL_ACCOUNT',
                'default' => true,
                'link' => true,
                'module' => 'Accounts',
                'id' => 'ACCOUNT_ID',
        ),

#41 Dan (http://pixelhum.com/blog)

Hi, I've tried setting up a parent field in the listview in the way that you described but for some reason I get 0 records displayed. As soon as I remove the code for the field it reverts to showing me all the records.

Here's the code that I've added:

'STDNT_STUDENTS_STDNT_QUALIFICATIONS_NAME' =>
array (
'width' => '10%',
'label' => 'LBL_STUDENT',
'default' => true,
'link' => true,
'module' => 'stdnt_Students',
'id' => 'STDNT_STUDFE42TUDENTS_IDA',
),

#42 Sander Marechal (http://www.jejik.com)

@Dan: WHich version of Sugar are you on? Comments #37 and #39 seem to have the same problem on 5.2.0d. I tested my changes with 5.2.0b and 5.2.0h and it worked on both.

Let me know if you have 5.2.0h or something that I tested and I'll dive in deeper.

#43 Dan (http://pixelhum.com/blog)

@Sander: Thanks for your reply. I'm running 5.2.0f. Do you think it would be worth trying an upgrade to 5.2.0h or 5.2.0i?

#44 Daniel

Sanders,

I've tried making this work on 5.2.0h and 5.2.0i. No luck. It seems like 5.2 adds those random hex digits to *_IDA names. I followed the steps exactly and the SHOP_COMPUTERS_SHOP_APPLICATIONS_NAME never works in listview.

I tried defining another field in vardefs for application module with the same definition as SHOP_COMPUTERS_SHOP_APPLICATIONS_NAME but a different name. Placing thin into list view produces desired result. It is as if relationship fields are not loaded for listview.

I'd appreciate if you could get back to me about this one. How did you make this work on 5.2.0h/i?

#45 Daniel

I think this is a bug/feature in 5.2. Where it doesn't load relationships fields for listview. I fixed your sample by modifying SugarBean:

function fill_in_additional_list_fields(){
$this->fill_in_relationship_fields();
if(!empty($this->field_defs['parent_name']) && empty($this->parent_name)){
$this->fill_in_additional_parent_fields();
}
}

I added $this->fill_in_relationship_fields(); in fill_in_additional_list_fields(). I'm pretty sure this isn't the best fix, and I don't know why was the code changed not to load relationship fields.

#46 Daniel

for this article to work on 5.2.0h/i you would have to override bean file for applications module. I had to add this

function fill_in_additional_list_fields ()
{
$this->fill_in_relationship_fields();
return parent::fill_in_additional_list_fields();
}

for fields to be populated with data

#47 Sander Marechal (http://www.jejik.com)

Thanks Daniel. I see now why it didn't work for you but did work for me. My custom modules have custom fill_in_link_fields() functions for my grandparent fields (see this article). I think it does the same trick as your changes to make the listviews work.

#48 Dan (http://pixelhum.com/blog)

Ok, I think I've worked out why it's not working for me. I analyzed the query that SugarCRM is running to get the list of records and it's trying to get the name field from the parent. The trouble is, the parent table does not have a name field. How can I change this so it's getting first_name and last_name instead?

#49 CrashHD (http://www.fothcorp.com)

Sander, thank you for some of the great insights you have provided via blog about sugarcrm. I have found them quite refreshing.

For anyone who may be interested, here is a quick patch to get the labels working correctly for the editviews (so you don't have to manually add the label each time you create a new one to many relationship).

Let me know if you found this at all helpful.

### Eclipse Workspace Patch 1.0
#P sugarcrm_sandbox
Index: modules/ModuleBuilder/parsers/relationships/UndeployedRelationships.php
===================================================================
--- modules/ModuleBuilder/parsers/relationships/UndeployedRelationships.php	(revision 36)
+++ modules/ModuleBuilder/parsers/relationships/UndeployedRelationships.php	(working copy)
@@ -356,7 +356,7 @@
                     $GLOBALS [ 'log' ]->debug ( get_class ( $this ) . ": " . (($actionAdd) ? "adding" : "removing") . " $fieldName on $view layout for undeployed module {$parsedName [ 'moduleName' ]} in package {$parsedName [ 'packageName' ]}" ) ;
                     $parser = new GridLayoutMetaDataParser ( $view, $parsedName [ 'moduleName' ], $parsedName [ 'packageName' ] ) ;
                     
-                    if (($actionAdd) ? $parser->addField ( array ( 'name' => $fieldName ) ) : $parser->removeField ( $fieldName ))
+                    if (($actionAdd) ? $parser->addField ( array ( 'name' => $fieldName, 'label' => $_REQUEST['rhs_label'] ) ) : $parser->removeField ( $fieldName ))
                     {
                         $parser->handleSave ( false ) ;
                     }

#50 Sander Marechal (http://www.jejik.com)

@Dan: In the vardefs where your stdnt_students_stdnt_qualifications_name is defined (not in the listdefs, but the vardefs) you can set the "rname". The rname tells Sugar what field to read from the parent module. If it's not set it will default to "name" as in your case.

@CrashHD: Thanks for that. I have taken the liberty to modify your post a bit and wrap your patch in pre tags.

#51 danbee (http://pixelhum.com/blog)

Hi Sander, I did see that and tried changing it to first_name in what I thought was the correct file (custom/Extension/modules/stdnt_Qualifications/Vardefs/dBsStudents.php), but the query is still trying to get the data from the name field. The vardefs for the students module (the parent) pull in the person template which defines a name field as being the first_name and last_name fields concatenated.

#52 danbee (http://pixelhum.com/blog)

Ok, I've modified the correct file now and gotten it to work showing the first_name column. Do you know how I can get it to show the students full name?

#53 Dan (http://pixelhum.com/blog)

Fixed! I added the following lines to the vardefs.ext.php file for the module:

  'fields' => array('first_name', 'last_name'),
  'db_concat_fields'=> array(0=>'first_name', 1=>'last_name'),


Sander, thanks for your help and patience! I only wish the SugarCRM forums were as responsive as you've been.

#54 Sander Marechal (http://www.jejik.com)

Good to hear!

#55 CrashHD (http://www.fothcorp.com)

Do we know if the sugarcrm bugtracker is even used any more? I've posted a couple of fixes and have yet to even get a response. It definitely discourages me from wanting to implement this solution to our clients....

Sander any other CRM recommendations you have?

#56 Sander Marechal (http://www.jejik.com)

The bugtracker is used, but they are very, very slow to respond.

I haven't used any other open source CRM systems so I cannot recommend any. But there are other options around. There is v-tiger, which is a fork of SugarCRM. There's also Centric and Compiere. Here's a list of open sourec CRM systems. Google will probably turn up even more.

#57 cristian

Hi Sander,

How do I import decimal values in a field in a custom module?

When completed the process of importing the data in decimal format: 1000.58, stores it as Sugarcrm 10,005,800

How can I fix this?

Thanks

#58 Sander Marechal (http://www.jejik.com)

Cristian, I believe that's a know bug in SugarCRM. It doesn't just affect the import either. I have the exact same problem with monetary amounts in opportunities when I choose European-style decimal and thousand separators (i.e. a dot for thousand and a comma for decimal).

I've been waiting on a fix for nearly a year...

#59 ldebaets

Sander, Thank you for sharing this very informative and helpful knowledge. I see the problem still exists in version 6.0.1 of SugarCRM which I am working in. Do you know if the solution would work if the modules are in separate packages? As well, do you know any limit on the number of modules or relationships the modulebuilder can handle in sugar? When building a package of up to 6 modules with inter-relationships, when I got to the 4th or 5th module, the moduelbuilder seemed to choke when I tried to deploy. It simply said there was an internal error and the package probably did not deploy properly. I suspected the amount of data it had to manage exeeded an internal limit but there was nothing in the logs to indicate what happened.

#60 akuba (http://familion.ru)

I have problem with data import - sugar not correct imported data with .(dot) decimal separator.
Any ideas?

#61 Sander Marechal (http://www.jejik.com)

@ldebaets: I don't see why this should not work with separate packages.

As for the number of modules installed, I don't know if a hard upper limit in SugarCRM. What may be happening is that you are getting naming conflicts. SugarCRM auto-generates names for relationships and fields when you are working in the module builder. But, the naming scheme it uses does not guarantee uniqueness (at least not in 5.1CE/Pro). Especially with long module names there was a high probability of conflicts. This may be what is biting you.

To solve this, you can manually edit the files generated by the module builder to rename relationships and associated fields. But, I have no idea if you can still load them back in the module builder after that. Likely not, if it expects the names to be in the (wrong) scheme.

#62 layman99

I want to autofill fuctionality of dropdown list and relate fields of a subpanel in account module. i have created one -to -many relationship b/w account & contacts. Could anybody help me on this...... Thanks in advance for your efforts...

#63 Ramblin (http://www.ayuda.ca)

I am using SugarCRM 6.1.14 and have (so far) found Module Builder to produce decent baseline code, which I can then customize as required.

But it sounds like Module Builder is not something to rely on in v5.x of SugarCRM

Has anyone tried Module Builder in v6.x of SugarCRM and if so, what are your impressions?

I am trying to create a new module with Module Builder but then add a couple of fields into the relationship table - but I want to do so in a way that is both upgrade and redeploy safe (ie make customizations that will not be undone if I re-Publish in Module Builder). Is this feasible?

I originally posted this question in the SugarCRM Forum at
http://www.sugarcrm.com/forums/showthread.php?t=69667
but to-date have not received an answer I can work with

Any ideas?

#64 Marnus van Niekerk (http://www.mjvn.com)

I have built some tools that can automatically create subpanels for relate fields and fix the bad many to many relationships created by studio.
You can download these for free from http://www.mjvn.com/index.php/sugarcrm-tools/

M

#65 Mat as

Hi,

I want to create a link to an other module in the subpanel of a custom module. I add this lines in custom\modules\fide2_Items\metadata\subpanels

'fide1_produde2_items_name'=>array(
'vname' => 'LBL_NAME',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '45%',
'target_module' => 'fide1_productos',
'default' => true,
'target_record_key' => 'fide1_prod9889oductos_ida',
),

When I click on the link I have this error :

"Fatal error: Call to a member function get_summary_text() on a non-object in C:\AppServ\www\fidemar\include\MVC\View\SugarView.php on line 1088"

Please help,

Thank you,

Matías

#66 Pablo A (http://www.nissigroup.com.ar)

I've wrote a bible, but the spam filter kicks me away :(

Basically ... this is the problem (Sugar 6.4.2)

Contacts --< Inscripciones >-- Eventos

The subpanel "Inscripciones" shows everything ok but the Eventos name is empty.
I have worked with the studio.

This is the Sugar forum post. Please advice! anything!
http://www.sugarcrm.com/forums/showthread.php?t=79065

#67 Arnaud Kleinveld (http://www.ybo.com.sg)

We have been using the Module builder from version 6.2 to 6.4.5 without major problems. It seems to be relatively bug free lately.

It's important to know however that when you develop in one system and then export and import into another, for example from development to production, every customization has to go through the same process. And as far as we know data has to be restored every time an update using that process is being applied to production. Maybe we are missing something here.

Finally we built, tested and did the UAT on one system and then continued doing small customizations on production later on. For as long we were prototyping at least. Like Jess said, Sugar is perfect for prototyping you data structure. Once you have that done and your staff has loaded your database with data is time to look at another system with more business intelligence and quality and speed at it's core...

Comments have been retired for this article.