Still can't filter by custom fields on SuiteCRM 7.11.4 via RESTful API V8

Hello suitecrm develop team and all,

Thanks for your efforts on the wonderful project.

I still got the problem that can’t filter by custom fields on suitecrm 7.11.4 via restful api v8. please help have a look at my case and point me out if I make something incorrect. Thank you in advance.

We have an integration requirement that when having incoming call, query the call number in contacts table to identify contacts.id, if yes then url jump to that contact in browser, and create a call record.

As the phone number will be entered into database with different format, I write few codes which using hook ‘before_save’ to unformat phone number and store into custom fields (e.g. phone_work_digit_c) which created in studio wtih the table name is contacts_cstm by design in suitecrm.

I used to run this in suitecrm 7.11.1 and tested on 7.11.2 and 7.11.3 too. There is a known issue that can’t filter by custom fields via restful api v8. I tried to modify as below and works (suitecrm/Api/V8/JsonApi/Repository/Filter.php), referenced on https://github.com/salesagility/SuiteCRM/issues/6455:

      foreach ($expr as $op => $value) {
            $this->checkOperator($op);
                
            if ( substr($field, -2) === "_c" ){
            $where[] = sprintf(
                '%s %s %s',
                $field,
                constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                $this->db->quoted($value)
            );
            }else{
            $where[] = sprintf(
                '%s.%s %s %s',
                $bean->getTableName(),
                $field,
                constant(sprintf('%s::OP_%s', self::class, strtoupper($op))),
                $this->db->quoted($value)
            );
            }
        }

Since the version 7.11.4 released on April 30th, 2019 and announced that issue has been fixed, I tried both ways (upgrade and fresh install) to test. But it still fails and gets errors in suitecrm.log:

==================
Thu May 6 18:07:10 2019 [5957][1][FATAL] Mysqli_query failed.
Thu May 6 18:07:10 2019 [5957][1][FATAL] Query Failed: SELECT COUNT() AS cnt FROM contacts WHERE contacts.phone_work_digit_c = ‘0952975025’ OR contacts.phone_mobile
_digit_c = ‘0952975025’ OR contacts.phone_home_digit_c = ‘0952975025’ OR contacts.phone_other_digit_c = ‘0952975025’ OR contacts.phone_fax_digit_c = ‘0952975025’ OR co
ntacts.assistant_phone_digit_c = ‘0952975025’ OR contacts.deleted = ‘0’: MySQL error 1054: Unknown column ‘contacts.phone_work_digit_c’ in ‘where clause’
Thu May 6 18:07:10 2019 [5957][1][FATAL] Mysqli_query failed.
Thu May 6 18:07:10 2019 [5957][1][FATAL] Error running count query for Contact List: Query Failed: SELECT count(
) c FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN users jt0 ON contacts.modified_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON contacts.created_by=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 LEFT JOIN users jt2 ON contacts.assigned_user_id=jt2.id AND jt2.deleted=0
AND jt2.deleted=0 LEFT JOIN accounts_contacts jtl3 ON contacts.id=jtl3.contact_id AND jtl3.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl3.account_id AND accounts.deleted=0
AND accounts.deleted=0 LEFT JOIN opportunities_contacts opportunities_contacts ON contacts.id=opportunities_contacts.contact_id AND opportunities_contacts.deleted=0
LEFT JOIN opportunities jt4 ON jt4.id=opportunities_contacts.opportunity_id AND jt4.deleted=0
AND jt4.deleted=0 LEFT JOIN contacts jt5 ON contacts.reports_to_id=jt5.id AND jt5.deleted=0
AND jt5.deleted=0 LEFT JOIN campaigns jt6 ON contacts.campaign_id=jt6.id AND jt6.deleted=0
AND jt6.deleted=0 LEFT JOIN calls_contacts jtl7 ON contacts.id=jtl7.contact_id AND jtl7.deleted=0
LEFT JOIN calls jt7 ON jt7.id=jtl7.call_id AND jt7.deleted=0
AND jt7.deleted=0 LEFT JOIN meetings_contacts jtl8 ON contacts.id=jtl8.contact_id AND jtl8.deleted=0
LEFT JOIN meetings jt8 ON jt8.id=jtl8.meeting_id AND jt8.deleted=0
AND jt8.deleted=0 LEFT JOIN fp_events_contacts_c fp_events_contacts ON contacts.id=fp_events_contacts.fp_events_contactscontacts_idb AND fp_events_contacts.deleted=0
LEFT JOIN fp_events jt9 ON jt9.id=fp_events_contacts.fp_events_contactsfp_events_ida AND jt9.deleted=0
AND jt9.deleted=0 where (contacts.phone_work_digit_c = ‘0952975025’ OR contacts.phone_mobile_digit_c = ‘0952975025’ OR contacts.phone_home_digit_c = ‘0952975025’ OR contacts.phone_other_digit_c = ‘0952975025’ OR contacts.phone_fax_digit_c = ‘0952975025’ OR contacts.assistant_phone_digit_c = ‘0952975025’ OR contacts.deleted = ‘0’) AND contacts.deleted=0: MySQL error 1054: Unknown column ‘contacts.phone_work_digit_c’ in ‘where clause’

Here is what send from postman (get token with success):

GET http://10.xx.x.186/suitecrm/Api/V8/module/Contacts?fields[Contacts]=id&filter[operator]=or&filter[phone_work_digit_c][eq]=0952975025&filter[phone_mobile_digit_c][eq]=0952975025&filter[phone_home_digit_c][eq]=0952975025&filter[phone_other_digit_c][eq]=0952975025&filter[phone_fax_digit_c][eq]=0952975025&filter[assistant_phone_digit_c][eq]=0952975025

Any suggestion will be appreciated.

Still happening… On 7.11.7…

What do you use for incoming calls?

I am having the same problem.
I can’t do any select using custom fields.

Using default fields works fine.

You mean the development tool? Node.js.
Or you mean the VoIP? Asterisk.