HOWTO - Hack Sugar to support related module info in subpanel (one-many)

[kenneth.thorman]

UPDATE:
I have been unable to get the other suggested ways (in this thread) to work so I have been using the fix that I outline in this thread for different modules. If anyone need an installable sugar module that allows you to install this hack for SugarCrm versions 5.0.0, 5.0.0a, 5.0.0b, 5.0.0c and 5.0.0e they can be found here (https://sourceforge.net/project/show...roup_id=210096). There are also some other modules that might be of interest.




NOTE: This post describes how to hack the Sugar core code to add a relative uniform way of allowing related module info to be displayed with a link in the subpanel. This code change is rather simple and should not contain any errors however please use at your own risk and please test it on a development system before taking it anywere close to a live system. This posting is AS-IS with no liability, guarantees, yada, yada, yada (you know the drill).

Any feedback on that the HACK is not necessarily will be very welcome. Any feedback what the cleanest official solution for this would also be very welcome. I am pretty unhappy with the core hack .

I will also describe how to do this hack.
I have used some time to try to avoid this hack but in the end after several deep debugging sessions I had to give up. I could not find the needed infrastructure there. I am sure it is there somewhere and I am just missing it. If there is any Sugar gurus out there that would care to comment so I do not lead anyone astray with this post please feel free?




Goal: I would try to use the existing infrastructure in Sugar to do this subpanel relation for me. There is a ton of options in the different def files around the place. I would go rather far to not do any SQL whatsoever even though a way have been described here (http://dl.sugarforge.org/sugardocs/N...on/Vardefs.pdf on page 4 and 5). However I chose to try get by with the definition files only and no direct SQL.

First of all we (to my knowledge) need the hack.

In SugarCrm 5.0 Full Release /data/SugarBean.php we need 2 changes:


Change #1
In line 2996 change the line from

$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' ' . $field;

to

$ret_array['select'] .= ' , ' . $params['join_table_alias'] . '.' . $data['rname'] . ' `' . $field . '`';

(This change is only included so the automatic subpanel select can handle weird column aliases like "case" (module Cases) so Sugar automatically can assign the value from the database to the correct field defined in the vardef. One could argue that this is not a hack but rather improving on the already existing code, since this will make the existing code more robust. If the field name in the vardef (specified in the modules builder) is a reserved Mysql word, then this could throw a mysql error. So maybe you could even call it a bug fix . This should probably be done in all the SQL generating statements, but I will leave that up to Sugars judgment. )



Change #2

After line 3009 please paste the following:

if(isset($value['related_record_key']) && !empty($filter))
{
$ret_array['select'] .= " , {$params['join_table_alias']}.{$value['related_record_key']} as {$value['target_record_key']}";
}


(this adds support for a new named array field for the /modules/SOMEMODULE/metadata/subpanels/default.php the "related_record_key". I could not get any of the different sql statement generating options in SugarBean.php to do what I wanted.)

Now the only file that needs to be changed to support this functionality is:

/modules/SOMEMODULE/vardefs.php
/modules/SOMEMODULE/metadata/subpanel/default.php (or some other file if you have defined them)


I have attached an example from one working module that is using this technique. See post #2 for a more tutorial like post




STEPS TO VISUALIZE - how this hack implements a missing functionality in the current SugarCrm implementation.

With the rich metadata interface that Sugar has I would prefer configuration over code anytime. And ultimately this should have support in Studio and ModuleBuilder.

Sugar ModuleBuilder rocks and is very good, but currently if you use ModuleBuilder to edit subpanels then if you drag-n-drop a "relate" field to the subpanel then that field will not show any data when the subpanel is shown. That is either a bug or missing implementation. If the GUI allows drag and drop field on a subpanel then IMHO it is also expected to be able to handle this in view mode.

Steps to reproduce

1. Go Admin / ModuleBuilder
2. Create new package "test"
3. New Basic module "test"
4. Add a new field called "case"
5. Add a relationship to Leads, Label="Leads"
6. Edit the Available subpanels and include the field "case" in this subpanel
7. Save
8. Edit the EditView, Listview and Detailview to also include the "case" field.
9. Deploy
10. Quick Rebuild
11. Go to Leads
12. Select a lead
13. Go to test subpanel
14. Create new test and relate to a case
15. Save
16. In my version there is no data in the subpanel for the field case.



UPDATE: The "OUTSTANDING ISSUES" is no longer relevant due to SugarCrm patch 5.0.0a.

OUTSTANDING ISSUES:
The LEFT OUTER JOIN that is created automatically should in my opinion need a where clause filtering on the parent id to return to correct rows, I have not had time to investigate this further right now, but will look into it. It seems to be working as is though.

UPDATE:

This is the sql that is generated from a entity that has 3 relate fields.

SELECT resource_allocation.id,
resource_allocation.start_date,
resource_allocation.end_date,
resource_allocation.hard_booking,
jt0.name `project`,
resource_allocation.project_id,
jt0.id as project_id,
jt0.assigned_user_id project_owner,
'Project' project_mod,
jt1.name `bug`,
resource_allocation.bug_id,
jt1.id as bug_id,
jt1.assigned_user_id bug_owner,
'Bugs' bug_mod,
jt2.name `case`,
resource_allocation.acase_id,
jt2.id as acase_id,
jt2.assigned_user_id case_owner,
'Cases' case_mod,
resource_allocation.assigned_user_id,
'resource_allocation' panel_name
FROM resource_allocation ´
LEFT JOIN project jt0 ON jt0.id= resource_allocation.project_id AND jt0.deleted=0 AND jt0.deleted=0
LEFT JOIN bugs jt1 ON jt1.id= resource_allocation.bug_id AND jt1.deleted=0 AND jt1.deleted=0
LEFT JOIN cases jt2 ON jt2.id= resource_allocation.acase_id AND jt2.deleted=0 AND jt2.deleted=0
where resource_allocation.deleted=0


There are a few sub optimal things with this query.

1. For each LEFT JOIN there are 2 "AND jt0.deleted=0 AND jt0.deleted=0 "
2. The LEFT JOIN will cause all rows from the resource_allocation table to be fetched this will in turn end up returning potentially quite a few rows. Over time in this scenario a resource (i.e. a management consultant) can be assigned to many allocations during a year, maybe hundreds if allocations are short. This will cause a big amount of data.

The more relate fields you have and want to show in a subpanel the bigger this problem becomes.
Any ideas on how to fix this?

[kenneth.thorman]

Walk through

1. Install the attached demo module
2. Create a main entity and name it parent
3. Click on it in detailview and see that you have a subpanel allowing you to create many-to-many subpanelrelate
4. Create one and relate both a bug and a case to this and save it.

We will be editing the files directly under the installed module in sugar but you can also change then from the unzipped zip file, zip it up and reinstall. Every time we change the vardef.php file we must make a Admin/Quick Repair to renew the cached vardefs.

(Sugar50 - is the root installation folder)

1.First changing the subpanel so it shows us the fields that we want to see
Edit Sugar50\modules\kst_subpanelrelate\metadata\subpan els\default.php so it end up looking like

$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name),
),

'where' => '',

'list_fields' => array(
'bug'=>array(
'vname' => 'LBL_BUG',
'width' => '15%',
),
'case'=>array(
'vname' => 'LBL_CASE',
'width' => '15%',
),
'edit_button'=>array(
'widget_class' => 'SubPanelEditButton',
'module' => $module_name,
'width' => '4%',
),
'remove_button'=>array(
'widget_class' => 'SubPanelRemoveButton',
'module' => $module_name,
'width' => '5%',
),
),
);


Now you can see the 2 columns bug and case, but there is no data in it.

Edit Sugar50\modules\kst_subpanelrelate\vardefs.php so it end up supporting these relations (the green are the additions to the file)

$dictionary['kst_subpanelrelate'] = array(
'table'=>'kst_subpanelrelate',
'audited'=>true,
'fields'=>array (
'bug_id' =>
array (
'required' => false,
'name' => 'bug_id',
'vname' => '',
'type' => 'id',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => 36,
),
'bug' =>
array (
'required' => false,
'source' => 'non-db',
'name' => 'bug',
'vname' => 'LBL_BUG',
'type' => 'relate',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => '255',
'id_name' => 'bug_id',
'ext2' => 'Bugs',
'module' => 'Bugs',
'studio' => 'visible',

// Manually added to provide related data from project in the listview
'link' => 'related_bug_name', // ref to related field definition
'rname' => 'name', // the related field to display
),
// Manually added to allow for related field definition this is referenced by Sugar
// when it reads the field 'link' from the field def for 'bug'
'related_bug_name' =>
array (
'name' => 'bugs',
'type' => 'link',
'relationship' => 'relationship_bug',
'side' => 'right',
'source'=>'non-db',
),
'acase_id' =>
array (
'required' => false,
'name' => 'acase_id',
'vname' => '',
'type' => 'id',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => 36,
),
'case' =>
array (
'required' => false,
'source' => 'non-db',
'name' => 'case',
'vname' => 'LBL_CASE',
'type' => 'relate',
'massupdate' => 0,
'comments' => '',
'help' => '',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => 0,
'reportable' => 0,
'len' => '255',
'id_name' => 'acase_id',
'ext2' => 'Cases',
'module' => 'Cases',
'studio' => 'visible',

// Manually added to provide related data from project in the listview
'link' => 'related_case_name', // ref to related field definition
'rname' => 'name', // the related field to display
),
// Manually added to allow for related field definition this is referenced by Sugar
// when it reads the field 'link' from the field def for 'case'
'related_case_name' =>
array (
'name' => 'cases',
'type' => 'link',
'relationship' => 'relationship_case',
'side' => 'right',
'source'=>'non-db',
'vname'=>'LBL_PROJECT',
),
),
'relationships'=>array (
// These relationsships are needed here even if you have then defined somewhere else
// Sugar checks for them in this specific place array wise. It is very important to get
// the spelling of the Modules and tables correct otherwise it will not work
'relationship_case' => array
(
'lhs_module'=> 'kst_subpanelrelate', 'lhs_table'=> 'kst_subpanelrelate', 'lhs_key' => 'acase_id',
'rhs_module'=> 'Cases', 'rhs_table'=> 'cases', 'rhs_key' => 'id',
'relationship_type'=>'one-to-many',
),
'relationship_bug' => array
(
'lhs_module'=> 'kst_subpanelrelate', 'lhs_table'=> 'kst_subpanelrelate', 'lhs_key' => 'bug_id',
'rhs_module'=> 'Bugs', 'rhs_table'=> 'bugs', 'rhs_key' => 'id',
'relationship_type'=>'one-to-many',
),

),
'optimistic_lock'=>true,
);
require_once('include/SugarObjects/VardefManager.php');
VardefManager::createVardef('kst_subpanelrelate',' kst_subpanelrelate', array('basic','assignable'));



Save the file.
Repair sugar
Now when you click on parent there is data in the subpanels column but there is no link. Edit Sugar50\modules\kst_subpanelrelate\metadata\subpan els\default.php and change it to look like the following. The green are the additions to the file



$module_name='kst_subpanelrelate';
$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name),
),

'where' => '',

'list_fields' => array(
'bug'=>array(
'vname' => 'LBL_BUG',
'width' => '15%',

// Added for support for a detailview link to target this is standard functionality
'widget_class' => 'SubPanelDetailViewLink',

// Needed since otherwise it will point to current module (parent) when linking
// causing an ACL error since no row with this id is likely to exist. This must
// be spelled correctly since this is what ends up in the url
// index.php?module=target_module&
'target_module' => 'Bugs',

// Normally {target_module}_{related_record_key} you can find the name in the vardefs
// for current module (parent module)
'target_record_key' => 'bug_id',

// HACK IN SUGAR SOURCE CODE!!!
// The name of the row identifier in the related table, could not find any other way
'related_record_key' => 'id',
// HACK IN SUGAR SOURCE CODE - END !!!
),
'case'=>array(
'vname' => 'LBL_CASE',
'width' => '15%',

// Added for support for a detailview link to target this is standard functionality
'widget_class' => 'SubPanelDetailViewLink',

// Needed since otherwise it will point to current module (parent) when linking
// causing an ACL error since no row with this id is likely to exist. This must
// be spelled correctly since this is what ends up in the url
// index.php?module=target_module&
'target_module' => 'Cases',

// Normally {target_module}_{related_record_key} you can find the name in the vardefs
// for current module (parent module)
'target_record_key' => 'acase_id',

// HACK IN SUGAR SOURCE CODE!!!
// The name of the row identifier in the related table, could not find any other way
'related_record_key' => 'id',
// HACK IN SUGAR SOURCE CODE - END !!!
),
'edit_button'=>array(
'widget_class' => 'SubPanelEditButton',
'module' => $module_name,
'width' => '4%',
),
'remove_button'=>array(
'widget_class' => 'SubPanelRemoveButton',
'module' => $module_name,
'width' => '5%',
),
),
);




Now it should be working on your system.
You can edit the Sugar50\modules\kst_subpanelrelate\metadata\subpan els\default.php in one go but I wanted to make it clearer what was what and therefore did it in 2.

Any feedback on that the HACK is not necessarily will be very welcome. Any feedback what the cleanest official solution for this would also be very welcome. I am pretty unhappy with the core hack .

UPDATE:
Example: Lets assume you are having a relate field to a contact (which does not have fullname only , first_name and last_name and this contact is the customer responsible for something. You would like to display doth the first_name and the last_name for this related contact in the subpanel.

vardefs.php:
Instead of using

'rname' => 'first_name', // the related field to display

use

'db_concat_fields' =>
array (
'first_name',
'last_name',
),


Regards
Ken

[Kalendrinn]

If you are trying to set up a one-to-many or many-to-many relationship between modules you don't have to do any code hacks. First, I suggest you get the latest version of Sugar which is the full v5. If I understand right, they fixed some bugs since GA.

The trick is to add the appropriate files to the /custom folder and /metadata folder. I am still working it all out and am just at one stumbling block that is starting to get really annoying. So that said I won't post instructions till I'm done. But all I did was create a basic module using the module builder, defined a relationship to Accounts and then started copying what was done by the system and install of the new module. So far I have 6 files that build the relationship. The only other thing to do is make sure there's a table to keep track of the pairings. In my case I want to relate documents to other modules...so I created the documents_accounts table based off the new module I created.

Bah, before I write any more, I'll post what I have when I am done and figure out this last little piece. Needless to say, I have the subpanels and the data shows, but the data is incorrect as it does not reference the documents_accounts table currently (metadata file is not being read). Check my post: Implementing many-many relationships by hand - Help

[kenneth.thorman]

If you just want the relation between the modules and want to show it in the detail or edit view then it works fine, I have not been able to get it to work when you have a many-many between 2 modules and thus are showing the related module in a subpanel and that related module have related data itself, and you want to show this directly in the subpanel. Normal relations in Edit view and detail view works great.

I have updated the post with regards to the version, it is 5.0 release I am using, just thought they still called it GA?

Regards
Ken

[Kalendrinn]

It sounds like you want to view specific data from one module in the subpanel listing of another module. Is that correct? I think that can be done by editing the subpaneldefs.php file or creating a new one in /custom/modules/MODULE/metadata? Not positive though, that's my next hurdle for future modules.

[kenneth.thorman]

You are correct in that is what I am trying to achieve. Attaching a few screen shots so people can see it. Also attaching a fully working module (please see post #2). The core hack will need to be installed though for this to work. I have been through quite a few of the files around the place, but I have so far not been able to locate that missing setting that will allow me to do it without the hack. I am probably just missing something obvious.

[julian]

Once this is working properly, it'd be a great candidate for a Wiki article.

[kenneth.thorman]

Hi Julian

I still have a few outstanding issues that I need to confirm for this hack. It is working on my system and I have tested it quite a bit. The LEFT OUTER JOIN that is created automatically leaves me wondering though. I would expect that a where clause should be needed in the select to get the correct row data, but it seems to be working as it is? I must admit thought that after I got it working (after a few hours of trial and error and debugging) I wanted to have some feedback on the core hack from the gurus out there so I posted this on the forums and I did not pursue the matter further.

Julian do you have access to some core developers, so you can ask if this is a way out hack?
Is there a better official way to achieve this?

Regards
Ken

[gunni]

With the screenshots i get an idea about what you are trying to achieve. But its not clear to the end for me.
You build kind of relation between 3 modules?
Or could it be described like the contact-opportunity relationship, where there is additional data for the relationship, and in your case this additional data is a relation to a third module?

[kenneth.thorman]

Well the original case that caused this is that we are working with an allocation of resources. A resource can be allocated to either a bug, case or a project. It has a startdate, enddate, hardbooking, bug, case, project. This is the only interesting data on this and thus to get a proper idea of what the allocation is about you need to be able to show this information in a subpanel on a many-many related module. I do not only want the name of the related module in the bug, project, or case I want a direct link to the bug, project or case.

This post provides a solution to this.