Unable to filter with custom fields

Hello forum,

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?

Thanks in advance.

Hi @Vagos91,

Sorry your last post went under the radar, always best to try bumping if you didn’t get the chance.

As for your issue, have you tried running a “Quick Repair & Rebuild” from the admin > repair menu?

Back linking to now locked post: Filter issues with custom fields

Thanks for the reply,
I tried repair, also tried the solutions in the https://github.com/salesagility/SuiteCRM/issues/7285 and from an other thread that i can’t locate right now, which was kinda similar.

By filters are we meaning API filters? or just bog-standard filters within the interface?

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).

For reference i think it would help you if you could see the code in the SearchFields.php, so here is a pastebin link.

I’ll tag in some developers who know wayyyyy more than I do!

If you’re doing this via the backend (not via studio for example) then they should be able to assist. @samus-aran @clemente.raposo @pgr batman beacon

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…

New update doesn’t have something related to the issue, right?

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,

array (
    'query_type' => 'default',
    'db_field' => 
    array (
      0 => 'phone_office',
      1 => 'bonus_phone_1_c',
    'vname' => 'LBL_ANY_PHONE',
  • Not sure why this isn’t in the core files but it would be great if it gets added next updates!
1 Like

Thank you @Vagos91

Let me know if you are need of anything else.

1 Like

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

Hi Vagos

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.

The documentation is here

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!