starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

Global Search in v4 Service Fails for Custom Fields #2831

New issue

Closed

opened 2026-02-20 15:29:26 -05:00 by deekerman · 6 comments

deekerman commented

2026-02-20 15:29:26 -05:00

Owner

Originally created by @seancunningham on GitHub (Mar 8, 2018).

We are using a third party service called Integromat to facilitate nightly sync's between our service and our SuiteCRM instance using the V4 controller. When trying to execute a global search on a custom variable that has been created, it appears the SQL query has the original module's table name in the where clause for the search.

Expected Behavior

I would expect this search to perform correctly without 500 errors.

Actual Behavior

This search returns a 500 to the service, due to an error with the database query. Relevant log lines are below from both the suitecrm.log and php_error.log files.

Custom Variable Definition:
$dictionary['Accounts']['fields']['voi_c'] = array(
'name' => 'voi_c',
'vname' => 'LBL_VOI',
'labelValue' => 'Organization ID',
'type' => 'varchar',
'len' => 50,
'unified_search' => true,
'source' => 'non-db',
);

SuiteCRM Log:
[FATAL] Error running count query for Account List: Query Failed: SELECT count(*) c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0
AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0
AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0
AND jt4.deleted=0 where (accounts.voi_c LIKE '%xxx%') AND accounts.deleted=0: MySQL error 1054: Unknown column 'accounts.voi_c' in 'where clause'

PHP Error Log:
PHP Fatal error: Uncaught Exception: Database failure. Please refer to suitecrm.log for details. in /var/www/html/suitecrm/include/utils.php:1773
Stack trace:
#0 /var/www/html/suitecrm/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /var/www/html/suitecrm/include/database/DBManager.php(328): DBManager->registerError('Error running c...', 'Error running c...', true)
#2 /var/www/html/suitecrm/include/database/MysqliManager.php(167): DBManager->checkError('Error running c...', true)
#3 /var/www/html/suitecrm/data/SugarBean.php(4189): MysqliManager->query(' SELECT count(*...', true, 'Error running c...')
#4 /var/www/html/suitecrm/service/v4/SugarWebServiceUtilv4.php(109): SugarBean->process_list_query(' SELECT accoun...', 0, -1, 20, 'accounts.voi_c ...')
#5 /var/www/html/suitecrm/service/v4/SugarWebServiceImplv4.php(306): SugarWebServiceUtilv4->get_data_list(Object(Account), 'voi_c ASC', 'accounts.voi_c ...', 0, -1, -1, 0, false)
#6 /var/www/html/suitecrm/service/core/REST/SugarRestJSON.php(91): SugarWebS in /var/www/html/suitecrm/include/utils.php on line 1773

Possible Fix

I believe the where clause in the v4 php controller is skipping considering whether the queried field is a custom one or not. Unsure of code fix at the moment.

Steps to Reproduce

Create a custom variable.
Using v4 Api, attempt search on variable.
Experience bug.

Your Environment

SuiteCRM Version used: 7.10.1
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome Version 64.0.3282.167 (Official Build) (64-bit)
Environment name and version (e.g. MySQL, PHP 7): PHP v7.0.22, MariaDB v10.0.34
Operating System and version (e.g Ubuntu 16.04): Ubuntu v16.04.1

Originally created by @seancunningham on GitHub (Mar 8, 2018). We are using a third party service called Integromat to facilitate nightly sync's between our service and our SuiteCRM instance using the V4 controller. When trying to execute a global search on a custom variable that has been created, it appears the SQL query has the original module's table name in the where clause for the search. #### Expected Behavior I would expect this search to perform correctly without 500 errors. #### Actual Behavior This search returns a 500 to the service, due to an error with the database query. Relevant log lines are below from both the suitecrm.log and php_error.log files. **Custom Variable Definition:** $dictionary['Accounts']['fields']['voi_c'] = array( 'name' => 'voi_c', 'vname' => 'LBL_VOI', 'labelValue' => 'Organization ID', 'type' => 'varchar', 'len' => 50, 'unified_search' => true, 'source' => 'non-db', ); **SuiteCRM Log:** [FATAL] Error running count query for Account List: Query Failed: SELECT count(*) c FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 LEFT JOIN users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0 AND jt2.deleted=0 LEFT JOIN accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0 AND jt3.deleted=0 LEFT JOIN campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0 AND jt4.deleted=0 where (accounts.voi_c LIKE '%xxx%') AND accounts.deleted=0: MySQL error 1054: Unknown column 'accounts.voi_c' in 'where clause' **PHP Error Log:** PHP Fatal error: Uncaught Exception: Database failure. Please refer to suitecrm.log for details. in /var/www/html/suitecrm/include/utils.php:1773 Stack trace: #0 /var/www/html/suitecrm/include/database/DBManager.php(353): sugar_die('Database failur...') #1 /var/www/html/suitecrm/include/database/DBManager.php(328): DBManager->registerError('Error running c...', 'Error running c...', true) #2 /var/www/html/suitecrm/include/database/MysqliManager.php(167): DBManager->checkError('Error running c...', true) #3 /var/www/html/suitecrm/data/SugarBean.php(4189): MysqliManager->query(' SELECT count(*...', true, 'Error running c...') #4 /var/www/html/suitecrm/service/v4/SugarWebServiceUtilv4.php(109): SugarBean->process_list_query(' SELECT accoun...', 0, -1, 20, 'accounts.voi_c ...') #5 /var/www/html/suitecrm/service/v4/SugarWebServiceImplv4.php(306): SugarWebServiceUtilv4->get_data_list(Object(Account), 'voi_c ASC', 'accounts.voi_c ...', 0, -1, -1, 0, false) #6 /var/www/html/suitecrm/service/core/REST/SugarRestJSON.php(91): SugarWebS in /var/www/html/suitecrm/include/utils.php on line 1773 #### Possible Fix I believe the where clause in the v4 php controller is skipping considering whether the queried field is a custom one or not. Unsure of code fix at the moment. #### Steps to Reproduce 1. Create a custom variable. 2. Using v4 Api, attempt search on variable. 3. Experience bug. #### Your Environment  * SuiteCRM Version used: 7.10.1 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome Version 64.0.3282.167 (Official Build) (64-bit) * Environment name and version (e.g. MySQL, PHP 7): PHP v7.0.22, MariaDB v10.0.34 * Operating System and version (e.g Ubuntu 16.04): Ubuntu v16.04.1

deekerman

2026-02-20 15:29:26 -05:00

closed this issue
added the
Type: Bug

Priority:Moderate

Status: Requires Updates

Area: API
labels

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@QuickCRM commented on GitHub (Mar 9, 2018):

Hi,
I'm not sure it's an error in the REST API as you have:
'source' => 'non-db',
in your field definition.

@QuickCRM commented on GitHub (Mar 9, 2018): Hi, I'm not sure it's an error in the REST API as you have: 'source' => 'non-db', in your field definition.

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@seancunningham commented on GitHub (Mar 9, 2018):

I actually tried with and without the non-db option to see if that was the key.

@seancunningham commented on GitHub (Mar 9, 2018): I actually tried with and without the non-db option to see if that was the key.

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@QuickCRM commented on GitHub (Mar 10, 2018):

We use custom fields built with Studio
('source' => 'custom_fields’,)

and global_search works perfectly well with v4/v4_1 REST API (even though we have extended it to support Security Suite)

—
support@quickcrm.fr
www.quickcrm.fr/mobile http://www.quickcrm.fr/mobile
Visit our FAQ http://www.quickcrm.fr/mobile/en/content/16-faq

Le 9 mars 2018 à 21:16, Sean notifications@github.com a écrit :

I actually tried with and without the non-db option to see if that was the key.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/salesagility/SuiteCRM/issues/5438#issuecomment-371933288, or mute the thread https://github.com/notifications/unsubscribe-auth/AH3OMhqvifQvYFjO6MCuT8H-umnVJB09ks5tcuMMgaJpZM4SiWPb.

@QuickCRM commented on GitHub (Mar 10, 2018): We use custom fields built with Studio ('source' => 'custom_fields’,) and global_search works perfectly well with v4/v4_1 REST API (even though we have extended it to support Security Suite) — support@quickcrm.fr www.quickcrm.fr/mobile <http://www.quickcrm.fr/mobile> Visit our FAQ <http://www.quickcrm.fr/mobile/en/content/16-faq> > Le 9 mars 2018 à 21:16, Sean <notifications@github.com> a écrit : > > I actually tried with and without the non-db option to see if that was the key. > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub <https://github.com/salesagility/SuiteCRM/issues/5438#issuecomment-371933288>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AH3OMhqvifQvYFjO6MCuT8H-umnVJB09ks5tcuMMgaJpZM4SiWPb>. >

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@seancunningham commented on GitHub (Mar 20, 2018):

I changed the source to 'custom_fields' and, unfortunately, continued to experience the issue. I believe there may be some other configuration issue causing this issue.

Could you provide an example of a custom variable configuration for me? As well as confirm you are on the latest release of SuiteCRM? Many thanks

@seancunningham commented on GitHub (Mar 20, 2018): I changed the source to 'custom_fields' and, unfortunately, continued to experience the issue. I believe there may be some other configuration issue causing this issue. Could you provide an example of a custom variable configuration for me? As well as confirm you are on the latest release of SuiteCRM? Many thanks

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@samus-aran commented on GitHub (Aug 2, 2018):

@seancunningham Has this been resolved?
Can you confirm that the custom field is indeed in the database? Sounds like the vardefs are not being picked up correctly.
Have you done a repair and re-build and the accounts_cstm is definitely there? How are you querying the API itself passing in this custom field?
Some more info would be much appreciated.

@samus-aran commented on GitHub (Aug 2, 2018): @seancunningham Has this been resolved? Can you confirm that the custom field is indeed in the database? Sounds like the vardefs are not being picked up correctly. Have you done a repair and re-build and the accounts_cstm is definitely there? How are you querying the API itself passing in this custom field? Some more info would be much appreciated.

deekerman commented

2026-02-20 15:29:29 -05:00

Author

Owner

@Dillon-Brown commented on GitHub (Apr 10, 2019):

Closed due to inactivity

@Dillon-Brown commented on GitHub (Apr 10, 2019): Closed due to inactivity