Relate fields not displaying in Detailview and Editview

[Darkenergy]

I'm having a problem where the values of relate fields between custom modules are not displaying and therefore can't be used as links.

I have created a number of custom modules using modulebuilder. E.g I have created an equipment module and a calibration module. I have created a one-to-many relationship between the equipment module and the calibration modules and a one-to-many relationship between accounts and equipment.

When I view the equipment detailview the account name and link is displayed correctly. However, when I view the calibration detailview there is no link displayed to equipment. The href and link do exist in the rendered page but the name field from the equipment record is not showing - so the link is not visible. This is the case with all relate fields between two custom modules.

Any ideas?

Sugar 5.5.0 CE
Win 2008 server
SQL Server 2008
PHP 5.2.1
Apache/2.0.59 (Win32)

[agcopley]

Hi,
You will need to add the parent fields you wish to see to the vardefs of the child module.
To do this in an upgrade fashion, create a vardefs.php file in custom/extension/modules/<childmodule>/ext/vardefs/ folder.

Depending on how you created the relationship, the definition willvary (relate field or 1:M relationship join)

for example:

$dictionary["Project"]["fields"]["products_project_name"] = array (
'name' => 'products_project_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_PRODUCTS_PROJECT_FROM_PRODUCTS_TITLE',
'save' => true,
'id_name' => 'products_pf535roducts_ida',
'link' => 'products_project',
'table' => 'products',
'module' => 'Products',
'studio' =>'visible',
'rname' => 'name',
);

adds a field 'name' from products to project using the link products_project (must be defined aswell)
if it were a relate field join, it would be something like this

$dictionary["Project"]["fields"]["products_project_name"] = array (
'name' => 'products_project_name',
'type' => 'relate',
'source' => 'non-db',
'vname' => 'LBL_PRODUCTS_PROJECT_FROM_PRODUCTS_TITLE',
'save' => true,
'link' => 'products_project',
'table' => 'products',
'module' => 'Products',
'studio' =>'visible',
'rname' => 'name',
'id_name' => 'product_id'
);

again, with the link defined

Rgds
Andrew

[Darkenergy]

Hi Andrew

Thanks for your reply - Modulebuilder had created the relate fields correctly and they should display and function as intended.

However, after much delving into the workings of SugarBean.php I found that my problem is caused by a bug in the MS SQL Server driver which truncates returned field names to 30 characters.

Modulebuilder generates related field names from the names of the related modules - an example in my case being 'equip_equipmenttype_equip_equipment_name' - which is longer than 30 characters and gets truncated to 'equip_equipmenttype_equip_equi'. The result of this is that the name of the relate field in the bean does not match the value that the query fetches from the database and does not get populated.

I've created a temporary workaround for this by editing SugarBean.php to truncate the field name to 30 chars which is working in my instance but could result in non-unique field names.

I've not found any other threads on this problem so I'll start a new one.

Thanks for your help

Ian

[agcopley]

Hi Andrew

Thanks for your reply - Modulebuilder had created the relate fields correctly and they should display and function as intended.

However, after much delving into the workings of SugarBean.php I found that my problem is caused by a bug in the MS SQL Server driver which truncates returned field names to 30 characters.

Modulebuilder generates related field names from the names of the related modules - an example in my case being 'equip_equipmenttype_equip_equipment_name' - which is longer than 30 characters and gets truncated to 'equip_equipmenttype_equip_equi'. The result of this is that the name of the relate field in the bean does not match the value that the query fetches from the database and does not get populated.

I've created a temporary workaround for this by editing SugarBean.php to truncate the field name to 30 chars which is working in my instance but could result in non-unique field names.

I've not found any other threads on this problem so I'll start a new one.

Thanks for your help

Ian

Ahhh this one of the oddities on MSSQL...you could switch to mysql and save yourself some cash :-) too

[Darkenergy]

Unfortunately, we've just moved from MySQL to a dedicated MSSQL database server for increased performance and manageability. I don't think it'll go down well if I suggest going back to MySQL :-(

[agcopley]

Unfortunately, we've just moved from MySQL to a dedicated MSSQL database server for increased performance and manageability. I don't think it'll go down well if I suggest going back to MySQL :-(

hmm that sounds like a political decision rather than a technical one....the vast majority of Sugar instances are run on mysql and as such its a more stable environment. There was a time when mysql was not overly performant but no longer. I'm sure the community would be interested in your reasons for switching.

Have a good one.
Andrew