Started a topic some days ago but had no answer, giving it a second chance just in case it got lost in the stream of topics.
So i recently started using SuiteCRM, i am in version 7.12.3, and when using filters i get âMySQL error 1054: Unknown column âaccounts.bonus_phone_1_câ in âwhere clauseââ. Obviously the bonus_phone_1_c is a custom field in accounts module, so it is stored in accounts_cstm table and not accounts, tried some solutions i found online but nothing worked. Anyone got an idea how to deal with it?
Hmm, to be honest i am not sure what is the difference.
In the accounts module i try to filter using the button in the top left, right next to bulk action and stuff which when you click there are basic and advance filter options.
Aw gotcha, just as you linked to a GitHub issue about the API I wanted to be sure. The API is accessed via a 3rd party application via command line or other.
Youâre using the normal interface filters, the first page you see with only a few fields on it is known as you said as the âBasic Filterâ and the second page with lots of fields is known as âAdvanced Filterâ.
When you added your new custom field, did you try to add them to the Basic or Advanced filters? If so could you try removing it and see if that then allows you to filter?
Assuming you didnât add them to the filter this does sound like a bug where the system is unable to handle the custom field. (Once Iâve got the no for the suggestion above Iâll take a look at replicating and getting a bug on the go!)
Okay, i understood the difference now, so i added the custom filters in both basic and advanced pages.
To be clear, filtering WORKS but it doesnât work when you try to do a filter combination (think the terminology is query, but take my words with a grain of salt as you probably noticed i am amateur). For instance, if I try to filter with ANY PHONE option while just the basic âphone_officeâ is in the SearchField.php 's array for the âphoneâ array it works, but if i add as a second element in the array the custom field i mentioned above it hits me with the error.
Also, if i try to filter with just 1 of the custom fields it still works, but if i add a second custom field in the array it gives me error again. (For instance, if i filter with bonus_phone_1_c it works, if i filter with bonus_phone_1_c AND bonus_phone_2_c it doesnt).
I started via studio, but when i noticed that something is wrong i went straight to the files, but i really canât find what is wrong, the error i get is the same as the guy in the thread i mentioned previously, that was the reason i was trying via API to solve the issue, i mean, the api filtering was searching in wrong database table to find a custom element, same does the normal filter for meâŚ
So, @jessica1 gave me the solution for that (and i am grateful obviously). Seems like suitecrm doesnt have code to handle custom field filtering, so you have to do the following:
In SuiteCRM, We need to add one condition of custom fields for filter search in SearchForm2.php file.
To Fix this issue, Open the below path file.
/include/SearchForm/SearchForm2.php (Approx Line No. 1123)
Add the isset condition in generateSearchWhere() function of the above file path of your CRM as suggested in the text bellow:
} else {
if (!$customField) {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = $db->concat($this->seed->table_name, $this->seed->field_name_map[$db_field]['db_concat_fields']);
}
// Relationship fields get the name directly from the field_name_map
else {
if (isset($this->seed->field_name_map[$db_field]) && isset($this->seed->field_name_map[$db_field]['source']) && $this->seed->field_name_map[$db_field]['source'] == 'custom_fields') {
$db_field = $this->seed->table_name . "_cstm." . $db_field;
}
else {
if (!(isset($this->seed->field_name_map[$db_field]) && isset($this->seed->field_name_map[$db_field]['source']) && $this->seed->field_name_map[$db_field]['source'] == 'non-db')) {
$db_field = $this->seed->table_name . "." . $db_field;
}
}
}
} else {
if (!empty($this->seed->field_name_map[$field]['db_concat_fields'])) {
$db_field = $db->concat($this->seed->table_name . "_cstm.", $this->seed->field_name_map[$db_field]['db_concat_fields']);
} else {
$db_field = $this->seed->table_name . "_cstm." . $db_field;
}
}
/modules/Accounts/metadata/SearchFields.php (Approx Line No. 1123)
Add your custom field bonus_phone_c name in phone field array of above file path of your CRM as suggested in the below text,
Also, one more issue i noticed after the filters start working for the custom fields is that in some occasions the filter is not working correctly, for instance, letâs say we have to check if the date fields 1, 2, 3 and 4 have the value of 02-10-2022, suitecrm checks the first if it is like 02-10-2022%, the second if it is equal to 02-10-2022 and for the other two it doesnât search (i will add the debug log bellow).
Personal opinion after testing (as i said before i am noob tho) is that the like value% doesnt work for dates so it isnât a valid search (at least for my testing it doesnât work), the second approach works perfect while the next two doesnât work (obviously tho, they donât contain anything to search).
DEBUG bellow:
SELECT accounts.id ,accounts_cstm.active_vivliothiki_c,accounts_cstm.valid_until_combo_c,accounts_cstm.bonus_phone_1_c,accounts_cstm.call_plan_date_c,accounts_cstm.valid_until_inwritten_c,accounts_cstm.active_inwritten_c,accounts_cstm.lastcall_c,accounts_cstm.answered_c,accounts_cstm.sindromitis_drop_c,accounts_cstm.valid_until_vivliothiki_c, accounts.name , accounts.phone_office , accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c where ((( accounts_cstm.valid_until_astseminars_c like â10-02-2022%â OR DATE_FORMAT(accounts_cstm.valid_until_inwritten_c,â%Y-%m-%dâ) = â2022-02-10â OR DATE_FORMAT(accounts_cstm.valid_until_vivliothiki_c,â%Y-%m-%dâ) = ââ OR DATE_FORMAT(accounts_cstm.valid_until_briefdirect_c,â%Y-%m-%dâ) = ââ OR DATE_FORMAT(accounts_cstm.valid_until_ergatika_emag_c,â%Y-%m-%dâ) = ââ OR DATE_FORMAT(accounts_cstm.valid_until_forologika_emag_c,â%Y-%m-%dâ) = ââ OR DATE_FORMAT(accounts_cstm.valid_until_egxeiridio_c,â%Y-%m-%dâ) = ââ ))) AND accounts.deleted=0 ORDER BY accounts.name
Just a thought (sorry if you already ruled it out)>
Instead of custom coding the standard search - did you research the use of the âReportsâ built in to Suite. That would be a no -coding solution; so easier to change each time.
Certainly, Reports can handle your logical âORâ search needs.
Thanks for the tip!
I wasnât aware of this moduleâs function! I didnât fix the last issue yet, was trying to make a custom subquery but i was having some syntax errors. But will certainly look into the documentation and hopefully that can help me get away of that filtering issue!