Dashlet Search filter return BLANK result for Relate Field search

[devmehta555]

Search filter returns BLANK result if we apply search for any relate field for that specific module

Issue

If we have any relate field exist OR custom relate field : Add that specific field in Dashlet SearchView, then if we perform search filter so it always return "No Result Found".

Expected Behavior

Ideally dashlet should filter those data based on applied filter value for specific Relate Type field.

Actual Behavior

If we have any relate field exist OR custom relate field : Add that specific field in Dashlet SearchView, then if we perform search filter so it always return "No Result Found".

Possible Fix

Steps to Reproduce

1.Go to any module which is having "Relate Type" field already exisit. If not, create any custom field from studio.
2.Add that custom field in Dashlet Search Filter.
Note : Make sure that we have some (At-least one) sample record exist where "Relate Type" field value is not empty.
3.Add that module dashlet on dashboard and apply filter for "Relate Type" field value for which we have some sample record(s) exist in CRM and Apply filter.
4.As a result, Dashlet should show such result but it says. "No Results Found".

Context

Your Environment

• SuiteCRM Version used: Version 7.10.9 Sugar Version 6.5.25 (Build 344)

• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):Version 70.0.3538.110 (Official Build) (64-bit)

• Environment name and version (e.g. MySQL, PHP 7): PHP 7.2.10-0

• Operating System and version (e.g Ubuntu 16.04):ubuntu0.18.04.1

[devmehta555]

Hello Team,
Is there any fix for this issue?

[aureh12]

Same problem here on Version 7.11.7.

I created a "relate" field to relate records to Users to be a manager.
It filters properly in List View, but filtering on the field fails in Dashlet.

Error in logs:
Query Failed: SELECT users.id , users.created_by FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c where (manager_id='b67b7596-1ede-843a-0561-5b923fb3369f' ) AND users.deleted=0: MySQL error 1054: Unknown column 'manager_id' in 'where clause'

Any code fix would be appreciated.

vardefs:
'manager_id' => array ( 'required' => false, 'name' => 'manager_id', 'vname' => 'LBL_MANAGER_ID', 'type' => 'id', 'massupdate' => 0, 'no_default' => false, 'comments' => '', 'help' => '', 'importable' => 'true', 'duplicate_merge' => 'disabled', 'duplicate_merge_dom_value' => 0, 'audited' => true, 'reportable' => false, 'unified_search' => false, 'merge_filter' => 'disabled', 'len' => 36, 'size' => '20', ), 'manager_name' => array ( 'required' => false, 'source' => 'non-db', 'name' => 'manager_name', 'vname' => 'LBL_MANAGER_NAME', 'type' => 'relate', 'massupdate' => 0, 'no_default' => false, 'comments' => '', 'help' => '', 'importable' => 'true', 'duplicate_merge' => 'disabled', 'duplicate_merge_dom_value' => '0', 'audited' => false, 'reportable' => true, 'unified_search' => false, 'merge_filter' => 'disabled', 'len' => '255', 'size' => '20', 'id_name' => 'manager_id', 'ext2' => 'Users', 'module' => 'Users', 'rname' => 'name', 'quicksearch' => 'enabled', 'studio' => 'visible', ),

[aureh12]

Hello. No news on this bug. on SuiteCRM 7.11.8.

1/ When creating a custom relate field for my custom module using Studio
=> I can filter by this relate field in dashlet
2/ When creating the field by code using the same code as generated by Module Builder (below)
=> The Dashlet returns no data, and the error above is logged
=> It work PERFECTLY in List view

Since there is nobody working on this bug, I can dig and find a solution.
Please answer my questions so I can move forward:

• Why List view queries and Dashlet list queries are not build with the same code ?
• Where is the code building the list query for a Dashlet ?

Thanks.

[aurelien1980]

This bug is back in recent 7.12.* updates
Please need a fix...

[aurelien1980]

I found how to do it. Need to copy the way the relate field is defined for account member of.

In vardefs, add the fields:

'manager_id' => 
array (
	'name' => 'manager_id',
	'vname' => 'LBL_MANAGER_ID',
	'type' => 'id',
	'required' => false,
	'reportable' => true,
	'audited' => true,
	'comment' => '', 
),
'manager_name' => 
array (
	'required' => false,
	'name' => 'manager_name',
	'rname' => 'user_name',
	'id_name' => 'manager_id',
	'vname' => 'LBL_MANAGER_NAME',
	'type' => 'relate',
	'isnull' => 'true',
	'module' => 'Users',
	'table' => 'users',
	'massupdate' => false,
	'source' => 'non-db',
	'len' => 36,
	'link' => 'manager_link',
	'unified_search' => true,
	'importable' => 'true',
),	
'manager_link' => array(
	'name' => 'manager_link',
	'type' => 'link',
	'relationship' => 'manager_relationship',
	'module' => 'Users',
	'bean_name' => 'users',
	'link_type' => 'one',
	'source' => 'non-db',
	'vname' => 'LBL_MEMBER_OF',
	'side' => 'right',
),

then add a relationship below:

'relationships' => array(
'manager_relationship' => array(
'lhs_module' => 'Users',
'lhs_table' => 'users',
'lhs_key' => 'id',
'rhs_module' => 'AGR_Inspections',
'rhs_table' => 'agr_inspections',
'rhs_key' => 'manager_id',
'relationship_type' => 'one-to-many'
)
),