Help for searching and creating target lists

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

I would like to find all Contacts whose title is President and whose Company is in the Apparel Industry.

Unfortunaley I can’t find a way to create this list.

I can search either the contacts (President) (but in this case I get the Presidents from across all Industries) or the Accounts (Apparel Industry) (In this case I get no contacts. Only the companies), but not a combination of both.

Any one can help?

Thanks

2

Off the top of my head, I can think of two things:

  • You could create a Relate field for your Contacts module, and give the field name exactly the same name as the industry field on your Accounts module. I think this can work.
  • Or, you could write the industry name from the Accounts module to the a different field in the Contacts module, using Workflows. Then you’d be able to search for both from the Contacts search.
3

Thanks for the suggestions but they are both clearly hacks that could lead to a lot of headaches and making it useless to have accounts and contacts: in that case I would need one big table with lots of duplicated (full f errors) data.!

In fact I would like to be able to cross search Accounts and Contacts in any field (for example all Sales Directors in Companies whose HQ is in France in France from a specific Industry and with sales greater than 10M and more than 100 Employees

With your solution I would have to essentially duplicate all company data in each contact and if something changes in the company I have to modify each contact of the same company.

4

Maybe the second option is a “hack”, but the first certainly is not. You use relate fields to build a relationship in one specific field of a module, to another specific field in another module - which is exactly what you’re trying to do. It’s just like relating one module to another, but limiting it to a field.

As another option, reports can do this perfectly.

1 Like
5

Thanks,
Reports seems to be a good idea.
I gave it a try but it was not possible to use certain fields (for example I didn’t find the contacts email (maybe I have to define it in Studio).
And then I didn’t understand how to connect the conditions with OR or AND.
I will study it further and thanks again.

Still I don’t understand why this feature is not part of the search functionality!

A.

6

OK. I looked at it further and with reports I can manage to do what I want if I use also exclusion lists.

It’s a bit tricky but I can do almost everything I could do with logical operators (AND, OR, NOT, XOR)

Still I believe this feature should be in the search function.

AM

7

Playing further with Reports I find too many limitations to create an accurate selection.

For example:
. it is not possible to use wildcards (%)
. it is not possible to compare values (eg: greater than, greater than or equal to, less than, less than or equal to)
. it is not possible to find values that do not contain a word or viceversa
. etc…

It would be nice if these things were added both to the reports functionality as well as to the search functionality.

I found an addon for Sugar called Enhanced search which does these things but, unfortunaletly it’s not free and rather expensive!
Maybe someone can install it and try to understand how it works to try to implement the missing functionality in SuiteCRM.

8

For the time being I modified the code in modules/AOR_Reports/AOR_Reports.php.

I changed the line:

                    if(!$where_set) $query['where'][] = $field.' '.$app_list_strings['aor_sql_operator_list'][$condition->operator].' '.$value;

with:

if(strpos($value, "%") !== FALSE)
  $MyOperator = "LIKE";
else 
  $MyOperator = $app_list_strings['aor_sql_operator_list'][$condition->operator];

if(!$where_set) $query['where'][] = $field.' '.$MyOperator.' '.$value;

I know that this is a hack but ir seems to work and gives me the possibility to create much accurate report searches.

I tried to understand the code on how these queries are built but the code is far too fragmented and, I haven’t still understood exactly how to add the options for “Contains” and “Does_Not_Contain”.

Same thing for AND and OR (It would be nice if, at the beginning of each condition line, one could chose between “AND” and “OR”, but I can’t figure out how to do it.

I know all this is is a horrible hack but I think that this functionality is a must!

9

Here is a small improvement to my hack:

if(strpos($value, "%") !== FALSE)
  if($app_list_strings['aor_sql_operator_list'][$condition->operator] == '=')
    $MyOperator = "LIKE";
  else
    $MyOperator = "NOT LIKE";
else 
  $MyOperator = $app_list_strings['aor_sql_operator_list'][$condition->operator];

if(!$where_set) $query['where'][] = $field.' '.$MyOperator.' '.$value;