Filtering in list view based on a related module's parameter

[manasan123]

In the Tasks module List View advanced filter, I want to include task’s Contact’s mobile phone as an option. I don’t know which files to look into and I couldn’t find any documentation about filtering either. It would be great to have some guidance as to where to begin.

P.S: I am just using a custom input box in the list view page so I just need an endpoint to call for filtering the list view based on provided mobile phone number.

[p.konetskiy]

@manasan123
I decided that i would write solution fastest then wouldn’t speak about files and format.
custom/modules/Tasks/metadata/searchdefs.php

...
'advanced_search' => array(
...
    'contact_mobile' => array('name' => 'contact_mobile','label' => 'LBL_CONTACT_MOBILE','type' => 'varchar'),
...
),

custom/modules/Tasks/metadata/SearchFields.php

$searchFields['Tasks'] = array (
...
    'contact_mobile' => array(
        'query_type'=>'format',
        'operator' => 'subquery',
        'subquery' => "SELECT t.id FROM tasks AS t LEFT JOIN (contacts AS c) ON (c.id=t.contact_id) WHERE c.deleted='0' AND c.phone_mobile LIKE '%{0}%'",
        'db_field'=>array('id')
    ),
...
);

And you shoud add about label ‘LBL_CONTACT_MOBILE’ to your language file.

[manasan123]

I am facing some SQL syntax error near the '%{0}%'
If I provide 123 as the phone number the syntax gets formed as '%'123'%' and the redundant quotes causes SQL error. I did some digging and I found a string_format function in SearchForm2.php that literally replaces occurrences of {0} with '123' with single quotes around the value without considering the surrounding operators .
Is this a bug or is there any way to get around this?

[p.konetskiy]

@manasan123
Yes, I know how work the function in SearchForm2.php. I don’t have some comments because I tested the decision with 2 configurations:

• SuiteCRM 7.11.15, MySQL 5.7.31, PHP 7.3
• SuiteCRM 7.10.22 and MariaDB 10.3.21, PHP 7.3

It works without problems. I didn’t see messages about error.
I wrote about the similar solution into other comment. It’s working too.

Filtering Targets by Converted/Unconverted

@pstevens Module Prospects has the field ‘lead_id’. It has id of Lead after converting. I found one interesting, standard and working way. copy files: modules/Prospects/metadata/SearchFields.php --> custom/modules/Prospects/metadata/SearchFields.php modules/Prospects/metadata/searchdefs.php --> custom/modules/Prospects/metadata/searchdefs.php Add code to files custom/modules/Prospects/metadata/SearchFields.php: ... $searchFields['Prospects'] = array( // add code start …

[manasan123]

I actually have SuiteCRM 7.11.5 and I am still getting the error in the logs stating MySQL query failed. I looked at the post you referred but I couldn’t see any string substitution in the subquery there. The syntax error is only occurring when we are using string substitution with Percent Sign Wildcard.

[p.konetskiy]

@manasan123
Can you do the command directly in SQL?

[manasan123]

No, I am getting the same error

[p.konetskiy]

I don’t see full command on the screenshot. Please write it.

[manasan123]

SELECT  tasks.id ,tasks_cstm.model_name_c,tasks_cstm.task_number_c, tasks.contact_id , tasks.assigned_user_id , tasks.status , tasks.name  , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name , contacts.assigned_user_id contact_name_owner  , 'Contacts' contact_name_mod, tasks.date_entered  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, tasks.created_by  FROM tasks  LEFT JOIN tasks_cstm ON tasks.id = tasks_cstm.id_c   LEFT JOIN  contacts contacts ON tasks.contact_id=contacts.id AND contacts.deleted=0 AND contacts.deleted=0  LEFT JOIN  users jt1 ON tasks.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 where ((tasks.id IN (SELECT t.id FROM tasks AS t LEFT JOIN (contacts AS c) ON (c.id=t.contact_id) WHERE c.deleted='0' AND c.phone_mobile LIKE '%'24250'%'))) AND tasks.deleted=0 ORDER BY tasks_cstm.task_number_c ASC LIMIT 0,21

[p.konetskiy]

@manasan123
I think that no quotes needed before and after number (24250).
I taked part of your code:

SELECT t.id FROM tasks AS t LEFT JOIN (contacts AS c) ON
 (c.id=t.contact_id) WHERE c.deleted='0' AND c.phone_mobile LIKE '%'24250'%'

and got yours error.
When I remove quotes all began to work.

SELECT t.id FROM tasks AS t LEFT JOIN (contacts AS c) ON
 (c.id=t.contact_id) WHERE c.deleted='0' AND c.phone_mobile LIKE '%24250%'

[manasan123]

Yeah, it does work when I remove the quotes but those quotes are generated by the string_format funcion. How do I remove those extra quotes without directly editing the function itself?

[p.konetskiy]

@manasan123
I analyzed code and tested.

1. I switched on debug mode. Look at screenshot. There aren’t quotes.
2. The string of number (24250) can be modified by function ‘from_html’ of file include/utils/db_utils.php but the function get number and return number without modofication.

I don’t know which function modifyed your string.

[manasan123]

For anyone having similar objectives you can follow two ways,

1. In custom/modules/Tasks/metadata/SearchFields.php include contacts.phone_mobile as an element in the db_field array of contact_name. This method will search in phone_mobile, first_name and last_name fields for match.

'contact_name' =>
  array(
    'query_type' => 'default',
    'db_field' =>
    array(
      0 => 'contacts.first_name',
      1 => 'contacts.last_name',
      2 => 'contacts.phone_mobile',
    ),
    'force_unifiedsearch' => true,
  ),

2. Create a new entry in the advanced search for contact_mobile as suggested by @p.konetskiy in Filtering in list view based on a related module's parameter but you might face some SQL synatax error in some SuiteCRM versions. if you do face errors you can update your instance to the latest release with the fix or you can edit the string_format function in include\utils.php file(not upgrade safe) as below.

function string_format($format, $args, $escape = true)
{
    $result = $format;

    /* Bug47277 fix.
     * If args array has only one argument, and it's empty, so empty single quotes are used '' . That's because
     * IN () fails and IN ('') works.
     */
    if (count($args) == 1) {
        reset($args);
        $singleArgument = current($args);
        if (empty($singleArgument)) {
            return str_replace('{0}', "''", $result);
        }
    }
    /* End of fix */

    if ($escape) {
        $db = DBManagerFactory::getInstance();
    }
    for ($i = 0; $i < count($args); ++$i) {
        if (strpos($args[$i], ',') !== false) {
            $values = explode(',', $args[$i]);
            if ($escape) {
                foreach ($values as &$value) {
                    $value = $db->quote($value);
                }
            }
            $args[$i] = implode("','", $values);
            $result = str_replace('{'.$i.'}', $args[$i], $result);
       }
        else if ($escape){       
            $result = str_replace('{'.$i.'}', $db->quote($args[$i]), $result);
        }
        else{       
            $result = str_replace('{'.$i.'}', $args[$i], $result);
        }
    }

    return $result;
}