Hi,
I have a relationship one-to-many in the same custom module “PS_agenti”. By code I’ve added a custom field “ruolo_aziendale” to the relationship table “ps_agenti_ps_agenti_1_c”.
I’ve tried in the dictionary to add this code:
$dictionary[“PS_Agenti”][“fields”][“ruolo_aziendale”] = array (
‘table’ => ‘ps_agenti_ps_agenti_1_c’,
‘name’ => ‘ruolo_aziendale’,
‘type’ => ‘relate’,
‘dbType’ => ‘varchar’,
‘len’ => ‘55’,
‘source’ => ‘non-db’,
‘vname’ => ‘LBL_RUOLO_AZIENDALE’,
);
But the field, that in the meantime I fill in the database with dumb data, is not showing.
If I remove the “source => non-db” i receive an error in the log that tells me there is no such field “ruolo_aziendale” in the table “PS_agenti”. How could I tell to retrieve the filed in the right table ‘ps_agenti_ps_agenti_1_c’??
Could you please help me?
Thanks a lot.
Bye
Hi,
Why you want to add the field in the related table?
Field should be there on either the ONE side module or within the MANY side module.
This is the common practice
Thanks
Hi and thanks for the reply.
I wanto to add the field in the relationship table because the left side and the right side are the same table of the same module “PS_Agenti”
Basically if you want to do so you will be unable to fetch the field via Sugar Bean and also field will not be visible on the View (Edit+Detail+List) because you are going out of scope. As per my knowledge.
You have to use custom queries to play with that field.
Can you please explain me the purpose of that field?
And how you want to save it?
You can also create a custom table for this field only.
Thanks
Yes.
An agent from PS_Agenti is linked to another agent(agency) in a specific role. That’s why in the relationship i want to specify that role (ruolo_aziendale).
I know I have to create a custom file in the edit view or via a popup, but for now i just want to let it show in the subpanel of the relationship in the viev of the agent.
Hope I’ve explained it right
In that case you can create a custom field in the same table and can view it in the sub-panel
No issue
I’ts a bit complicated (too complicated!) to get relationship fields to work in SuiteCRM. I’ve used this tutorial in the past:
and I added a few comments about this here
I am sorry.
Here is another solution.
Please create the Custom Field in the relationship table. and then use after save hook in the module and enter the custom field value within the table.
Later you can create a custom sub panel to view the data by writing your own custom query.
To create a custom sub-panel please see this help
http://urdhva-tech.blogspot.com/2013/02/add-custom-subpanel-in-accounts-module.html
Thanks
Thank you all,
I try to make a custom subpanel to show the field. And then try to make a custom popup to have a field to populate the table column
I tried to follow this guide, but something in the step 2 is not clear: http://sugarmods.co.uk/how-to-add-custom-fields-to-a-relationship-table-and-display-them-in-a-subpanel-sugarcrm-suitecrm/
Why should I create 3 definition when i create, in the database, only a field?
Thank you so much
Did you read my comments on that forum thread I posted above? I guess I was also confused about this, and still am. These are “non-db” fields created on the Contact bean, I guess it’s a convoluted way to get the data pulled into the Contact, even if it doesn’t belong there because it lives in a different db table.
But you don’t have to question it, you just have to make it work 
it took me a long time to get it right, but in the end the solution worked very well.
Yes, I’m confused too and i can’t make it work 
I am also having troubles with this.
I think using the contacts module as an example is a bad idea as at is very difficult to ascertain what is relevant to the meeting/contact etc functionality with delegates etc and what parts are required just for the relationship fields functionality.
Implementing all the fields and the functionality on a module without existing relationship fields would have been better.
this is my go at it.
I’ve created the relationship_meta
<?php
$primaryModule = "MOD_ModuleAs";
$secondaryModule = "MOD_ModuleBs";
$primaryModuleLC = strtolower($primaryModule);
$secondaryModuleLC = strtolower($secondaryModule);
$relationshipName = "{$primaryModuleLC}_{$secondaryModuleLC}";
$LHKey = "moda_id";
$RHKey = "modb_id";
$dictionary[$relationshipName] =
array (
'true_relationship_type' => 'many-to-many',
'from_studio' => false,
'relationships' =>
array (
$relationshipName =>
array (
'lhs_module' => $primaryModule,
'lhs_table' => $primaryModuleLC,
'lhs_key' => 'id',
'rhs_module' => $secondaryModule,
'rhs_table' => $secondaryModuleLC,
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => $relationshipName,
'join_key_lhs' => $LHKey,
'join_key_rhs' => $RHKey
)
),
'table' => $relationshipName,
'fields' =>
array (
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
array (
'name' => 'date_modified',
'type' => 'datetime',
),
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
array (
'name' => $LHKey,
'type' => 'varchar',
'len' => 36,
),
array (
'name' => $RHKey,
'type' => 'varchar',
'len' => 36,
),
array (
'name' => 'relfield',
'type' => 'varchar',
'len' => 36,
'default' => 'IT WORKED'
)
),
'indices' =>
array (
array (
'name' => "{$relationshipName}pk",
'type' => 'primary',
'fields' => array ('id'),
),
array (
'name' => "idx_{$relationshipName}_modulea",
'type' => 'index',
'fields' => array ($LHKey),
),
array (
'name' => "idx_{$relationshipName}_moduleb",
'type' => 'index',
'fields' => array ($RHKey),
),
array (
'name' => "idx_{$relationshipName}",
'type' => 'alternate_key',
'fields' => array ($LHKey, $RHKey, 'role'),
)
)
);
I’ve added vardefs on right hand side module to alias relationship fields to the sugarbean
...
// pull fields from relationship into module
'mod_rel_fields' =>
array (
'name' => 'mod_rel_fields',
'rname' => 'id',
'relationship_fields' =>
array (
'id' => 'mod_rel_id', // alias id to mod_rel_id from relationship to right hand side module
'relfield' => 'field_i_want' // alias relfield to field_i_want from relationship to right hand side module
),
'vname' => 'LBL_DONT_THINK_THIS_MATTERS',
'type' => 'relate',
'link' => 'mod_moduleas_mod_modulebs',
'link_type' => 'relationship_info',
'join_link_name' => 'mod_moduleas_mod_modulebs',
'source' => 'non-db',
'importable' => 'false',
'duplicate_merge' => 'disabled',
),
'field_i_want' =>
array (
'massupdate' => false,
'name' => 'field_i_want',
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_FIELD_I_WANT',
'importable' => 'false'
),
'mod_rel_id' =>
array (
'name' => 'mod_rel_id',
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_FIELD_I_WANT', //or another label don't think this will be displayed
'studio' => array ('listview' => false), // not sure if needed
),
...
and within the subpanel for the right hand side module (could be a specialised one or a default one I believe) I have added the special relationship_info field and alised id fields as query only fields with the field i want defined as normal. the query_only option makes me believe that these fields will have their queries executed so the values can be used by sugars orm
...
'list_fields' =>
array (
'name' =>
array (
'name' => 'name',
'widget_class' => 'SubPanelDetailViewLink',
'vname' => 'LBL_NAME',
'width' => '22%',
'sortable' => true,
'module' => $moduleName,
'default' => true
),
'field_i_want' =>
array (
'vname' => 'LBL_FIELD_I_WANT',
'width' => '22%',
'sortable' => false,
'default' => true
),
'edit_button' =>
array (
'vname' => 'LBL_EDIT_BUTTON',
'widget_class' => 'SubPanelEditButton',
'module' => $moduleName,
'width' => '5%',
),
'remove_button' =>
array (
'vname' => 'LBL_REMOVE',
'widget_class' => 'SubPanelRemoveButton',
'module' => $moduleName,
'width' => '5%',
),
'mod_rel_fields' =>
array (
'usage' => 'query_only'
),
'mod_rel_id' =>
array (
'usage' => 'query_only'
)
),
I also added $field_i_want and $mod_rel_id as public properties on the right hand side module bean object.
when inside the crm system looking at the subpanel to MOD_ModuleBs from MOD_ModuleAs, the field and label are displayed in the subpanel but with no value (should be ‘IT WORKED’)
Thank you, I’ll try and I’ll let you all know.
small update on this.
after doing some work where I have had to change a name field on a module to display a concatenation of other fields (think first_name last_name for a person);
to my disbelief; it does not work with just the metadata on it’s own and it infact needs a bit of custom work within a sugarbean file.
within the person.php template I can see that the full_name field needs to be built within an extended retrieve function.
this leads me to believe that the metadata information is just to make the fields queryable when they need to be accessed at whatever particular point in the application where it need to be accessed.
you need to write up how to build up the field value on your own
Got this working, I attempted to attach a screenshot but to no avail
In the next week or so (no promises but I’ll try to make the time to) I will write up a tutorial on what is required to get this to work.
Thank you very much for this write up! We need stuff like this!
I’ll have to try this tutorial one day, I’d like to learn this better.
One thing I would like to ask is if you could possibly edit your post (or re-post, and I can delete the old one) to add the exact file names and paths. This is important for beginners who might not know where things are, and it also helps with troubleshooting - when something is mysteriously not working, people have to check small details like file names and paths.
Once again: thanks a lot for this!
most welcome pgr
I can’t edit my previous post anymore so I’ll post it again with filepaths
Hi all,
so this is the bare minimum you require to get relationship fields to work.
I will be using two predefined modules called TEST_Foos and TEST_Bars in my examples.
the relationship field is called amaze and is a Yes/No Enum (other fieldtypes are supported)
Assume that there is nothing special about these modules as they are bare minimum modules.
I will only write up the information required for relationship fields so editviewdefs etc won’t be included
In this example the relationship fields will show in the subpanel within Foos detailview
but this can works both ways
RELATIONSHIP
/////////////////////////
filepath: custom/Extension/application/Ext/TableDictionary/test_foos_test_bars.php
include('custom/metadata/test_foos_test_barsMetaData.php');
filepath: custom/metadata/test_foos_test_barsMetaData.php
$dictionary["test_foos_test_bars"] = array (
'relationships' =>
array (
'test_foos_test_bars' =>
array (
'lhs_module' => 'TEST_Foos',
'lhs_table' => 'test_foos',
'lhs_key' => 'id',
'rhs_module' => 'TEST_Bars',
'rhs_table' => 'test_bars',
'rhs_key' => 'id',
'relationship_type' => 'many-to-many',
'join_table' => 'test_foos_test_bars',
'join_key_lhs' => 'foo_id',
'join_key_rhs' => 'bar_id',
),
),
'table' => 'test_foos_test_bars',
'fields' =>
array (
array (
'name' => 'id',
'type' => 'varchar',
'len' => 36,
),
array (
'name' => 'date_modified',
'type' => 'datetime',
),
array (
'name' => 'deleted',
'type' => 'bool',
'len' => '1',
'default' => '0',
'required' => true,
),
array (
'name' => 'foo_id',
'type' => 'varchar',
'len' => 36,
),
array (
'name' => 'bar_id',
'type' => 'varchar',
'len' => 36,
),
array (
'name' => 'amaze',
'type' => 'varchar',
'len' => 36,
)
),
'indices' =>
array (
array (
'name' => 'foobar_pk',
'type' => 'primary',
'fields' => array ('id'),
),
array (
'name' => 'foobar_idx',
'type' => 'index',
'fields' => array ('foo_id'),
),
array (
'name' => 'foobar_alt',
'type' => 'alternate_key',
'fields' => array ('bar_id'),
),
),
);
Here we define the relationship table between foos and bars with the extra column for the amaze field
MODULE Foo
/////////////////////
vardef snippet for link (place in fields array)
filepath: modules/TEST_Foos/vardefs.php
'bars' =>
array (
'name' => 'bars',
'type' => 'link',
'relationship' => 'test_foos_test_bars',
'module' => 'TEST_Bars',
'bean_name' => 'TEST_Bar',
'source' => 'non-db',
'vname' => 'LBL_BARS',
),
in Foos vardefs we need to define a link field to access the foobar relationship
subpaneldef
filepath: modules/TEST_Foos/metadata/subpaneldefs.php
'bars' =>
array (
'order' => 10,
'sort_order' => 'desc',
'subpanel_name' => 'ForFoos',
'title_key' => 'LBL_SUBPANEL_TITLE',
'module' => 'TEST_Bars',
'get_subpanel_data' => 'bars',
'top_buttons' =>
array (
array (
'widget_class' => 'SubPanelTopSelectButton',
'mode' => 'MultiSelect'
)
),
)
typical subpanel definitions
language file
filepath: modules/TEST_Foos/language/en_us.lang.php
$mod_strings =
array (
'LBL_SUBPANEL_TITLE' => 'Bars',
'LBL_ASSIGNED_TO' => 'Assigned To',
);
typical language definition for foos, note we don’t need to specify the label for our relationship field on this side
MODULE BARS
/////////////////////////
vardefs snippet (place in fields array)
filepath: modules/TEST_Bars/vardefs.php
'foos' =>
array (
'name' => 'foos',
'type' => 'link',
'relationship' => 'test_foos_test_bars',
'source' => 'non-db',
'vname' => 'LBL_FOOS',
'duplicate_merge' => 'disabled',
),
'amaze_fields' =>
array (
'name' => 'amaze_fields',
'rname' => 'id',
'relationship_fields' =>
array (
'id' => 'amaze_id',
'amaze' => 'so_amaze'
),
'type' => 'relate',
'link' => 'foos',
'link_type' => 'relationship_info',
'source' => 'non-db',
'importable' => 'false',
'duplicate_merge' => 'disabled',
'studio' => false,
),
'amaze_id' =>
array (
'name' => 'amaze_id',
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_AMAZE'
),
'so_amaze' =>
array (
'massupdate' => false,
'name' => 'so_amaze',
'type' => 'varchar',
'studio' => 'false',
'source' => 'non-db',
'vname' => 'LBL_AMAZE',
'options' => 'y_n_list',
'importable' => 'false',
),
so this is where things get a little different.
same as above we define a link for the foobar relationship on the bar side
but as well as this there are 3 other definitions
amaze_fields definition allows to map the relationship fields to variables on bar
'relationship_fields' =>
array (
'id' => 'amaze_id',
'amaze' => 'so_amaze'
),
this section maps the id of the relationship record to amaze_id on our bar sugarbean/DAO.
This is so the sugarbean/DAO can keep track of the correct row in the relationship table
amaze from the relationship table maps to the so_amaze variable on our bar sugarbean/DAO
ForFoos subpanel
filepath: modules/TEST_Bars/metadata/subpanels/ForFoos.php
$moduleName = 'TEST_Bars';
$subpanel_layout =
array (
'top_buttons' =>
array (
array ('widget_class' => 'SubPanelTopButtonQuickCreate'),
),
'where' => '',
'fill_in_additional_fields'=> true,
'list_fields' =>
array(
'name' =>
array (
'name' => 'name',
'widget_class' => 'SubPanelDetailViewLink',
'vname' => 'LBL_NAME',
'width' => '22%',
'sortable' => true,
),
'amaze_fields' =>
array (
'usage' => 'query_only',
),
'amaze_id' =>
array (
'usage' => 'query_only',
),
'so_amaze' =>
array (
'vname' => 'LBL_AMAZE',
'width' => '10%',
'sortable' => false,
'default' => true,
),
'edit_button' =>
array (
'vname' => 'LBL_EDIT_BUTTON',
'widget_class' => 'SubPanelEditButton',
'module' => $moduleName,
'width' => '5%',
),
'remove_button' =>
array (
'vname' => 'LBL_REMOVE',
'widget_class' => 'SubPanelRemoveButton',
'module' => $moduleName,
'width' => '5%',
),
),
);
within the bar subpanel meant for foo you can see that our three special variables are in the list_fields array.
amaze_fields and amaze_id need the query_only value so that when suite is generating the subpanel it can associate the relationship fields to the sugarbean/DAO. the so_amaze field is specified normally.
language file
filepath: modules/TEST_Foos/language/en_us.lang.php
$mod_strings =
array (
'LBL_SUBPANEL_TITLE' => 'Foos',
'LBL_AMAZE' => 'Amaze',
'LBL_ASSIGNED_TO' => 'Assigned To',
);
the amaze field exists within bar so the label needs to be in here and not in foo.
subpanels do not provide a way to edit these values (apart from with a widget that fires a link to a controller, no inline editing from what I understand) but you can have a look at FP_event’s controller for the markas*** actions. these will give you a good idea on how to update and set these relationship values or of course make up your own
Hi everyone.
I’m currently unable to archive what I’d like by following the tutorial.
I’ve created a module for storing courses and I’d like to create a custom field in the many-to-many table for storing a valutation.
(I’m stuck on an old version of SuiteCRM because of my company, we won’t upgrage any way soon; anyway everything about this issue should be the same as a newer release)
include('custom/metadata/rintr_rin_training_contactsMetaData.php'); 5 =>
array (
'name' => 'rintr_val_stars',
'type' => 'stars',
'dbType' => 'int'
),
I know at least this is OK because on Quick Repair I got prompted to create the new column in the SQL database.
'rintr_val_stars' =>
array (
'name' => 'rintr_val_stars',
'type' => 'link',
'relationship' => 'rintr_rin_training_contacts',
'module' => 'rintr_training',
'bean_name' => 'rintr_rin_training_contacts',
'source' => 'non-db',
'vname' => 'LBL_RIN_TRAINING_CONTACTS',
),
// Testing
'rin_training' =>
array(
'order' => 10,
'sort_order' => 'desc',
'subpanel_name' => 'Partecipanti',
'title_key' => 'LBL_SUBPANEL_TITLE',
'module' => 'rin_training',
'get_subpanel_data' => 'rin_training',
'top_buttons' =>
array(
array(
'widget_class' => 'SubPanelTopSelectButton',
'mode' => 'MultiSelect'
)
),
),
'contacts' =>
array (
'name' => 'contacts',
'type' => 'link',
'relationship' => 'rintr_rin_training_contacts',
'source' => 'non-db',
'vname' => 'LBL_FOOS',
'duplicate_merge' => 'disabled',
),
'rintr_val_stars_fields' =>
array (
'name' => 'val_stars_fields',
'rname' => 'id',
'relationship_fields' =>
array (
'id' => 'rintr_val_stars_id',
'rintr_val_stars' => 'so_rintr_val_stars'
),
'type' => 'relate',
'link' => 'contacts',
'link_type' => 'relationship_info',
'source' => 'non-db',
'importable' => 'false',
'duplicate_merge' => 'disabled',
'studio' => false,
),
'rintr_val_stars_id' =>
array (
'name' => 'rintr_val_stars_id',
'type' => 'varchar',
'source' => 'non-db',
'vname' => 'LBL_VAL_STARS_ID'
),
'so_rintr_val_stars' =>
array (
'massupdate' => false,
'name' => 'so_rintr_val_stars',
'type' => 'int',
'studio' => 'false',
'source' => 'non-db',
'vname' => 'LBL_VAL_STARS',
'importable' => 'false',
),
$module_name='rintr_rin_training';
$subpanel_layout = array(
'top_buttons' => array(
array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name),
),
'where' => '',
'list_fields' => array(
'name'=>array(
'vname' => 'LBL_NAME',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '45%',
),
'date_modified'=>array(
'vname' => 'LBL_DATE_MODIFIED',
'width' => '45%',
),
'rintr_val_stars_fields' =>
array (
'usage' => 'query_only',
),
'rintr_val_stars_id' =>
array (
'usage' => 'query_only',
),
'so_rintr_val_stars' =>
array (
'vame' => 'LBL_VAL_STARS',
'width' => '10%',
'sortable' => false,
'default' => true,
),
'edit_button'=>array(
'vname' => 'LBL_EDIT_BUTTON',
'widget_class' => 'SubPanelEditButton',
'module' => $module_name,
'width' => '4%',
),
'remove_button'=>array(
'vname' => 'LBL_REMOVE',
'widget_class' => 'SubPanelRemoveButton',
'module' => $module_name,
'width' => '5%',
),
),
);
Also this works because it gets displayed in the course subpanel, as shown in the image.
I’m probably doing something wrong in the vardefs definition.
I’ve wasted a whole work day on this and I’m looking for any kind of help.
Thanks a lot in advance for any answer.