Creating reports with multiple choices for a given field

SuiteCRM Forum - English Language Installation & Upgrade Help
1

Running SuiteCRM 7.8.31 and I would like to generate reports - to later be used as target lists for email marketing campaigns - where one of the criteria fields needs to match multiple values. An example would be to select targets in several different cities for the report (and subsequent marketing campaign.)

I see that a field used for criteria can use ‘Equal to, Not equal to, Contains, Begins with and Ends with’ but I am in need of a more flexible solution by being able to express the desired matches by writing a regular expression. An example could be that selection would be based on eg ‘(New York|Washington|Baltimore)’ etc.

Surely someone must have run into this problem before and may have a work-around or solution?

Thanks.

2

You could try and devise a solution with an after_save Workflow that examines the conditions and writes a simple custom field.

  • on save, with workflow conditions, you set some field “include in report” to true or false, as appropriate
  • on the report, you use that field value to filter
3

I found this link and want to see if this would work. What do you think?

4

I looked at the code of AOR_Report.php and the function build_report_query_where and the section ‘handle like conditions’. This section has a switch statement using $condition->operator where the cases are ‘Contains’, ‘Starts With’ and ‘Ends With’. It would seem to me that it might be possible to add regular expression capabilities to ‘Contains’ - provided that the database to which the query will be sent can handle a regular expression. A quick check on the MariaDB website suggests that version 10.0.5 and later has a REGEXP operator.

After some experimentation I simply replaced $aor_sql_operator_list[‘Contains’] = ‘LIKE’ with $aor_sql_operator_list[‘Contains’] = ‘REGEXP’ which in the limited testing I have done so far seems to work as expected.

However, I found that the input field in the report where you can enter condition is limited to exactly 100 characters and for some complex patterns, eg listing a number of cities, more characters would be needed. Is it easy to increase this number?

5

That looks like a nice technique.

I don’t know about the field width change, but maybe you can search Github, there was a PR a couple of years ago, changing name length, that might help. It might be as simple as a vardefs change, and then a QR&R, running the suggested queries there. But be careful, try it in a test system first.

Maybe this one: Fields last_name and first_name in Users too short. · Issue #7351 · salesagility/SuiteCRM · GitHub

But that generated problems, made a few indexes in the database become excessively long. I don’t think it will happen with Workflow conditions, but caution is advised…

6

Hi,

You could also add an OR operator in the report query (with parenthesis if your report has conditions on other fields)

7

I agree that since this is a workflow item rather than a database change it ought to be safe to increase the field length to more than 100 characters. However, editing vardefs.php in AOR_Reports and then doing the basic rebuilding did not actually change the number of characters I could enter.

I increased the value of ‘len’ in vardefs.php from 100 to 200 and received a message about this change when rebuilding yet it is not reflected in the report builder.

Can the length be defined somewhere else? A quick review of the other files in the AOR_Reports directory did not identify any other definition.

Where should I look?

Also, if I want to add another type of condition such as using regular expressions in addition to LIKE, where would I need to alter the code? In my experimentation above I had replaced LIKE in AOR _Report.php with REGEXP but it would be preferable to add this condition option while still keeping the ‘Equal to’/LIKE condition?

8

Not sure I understand. Are you suggesting I add another condition OR in addition to the existing ones? If so, where/how do I do that?

I did try to simply add OR in the condition field but that did not work.

9

This is what I am suggesting:

Capture d’écran 2023-02-05 à 14.06.23
Capture d’écran 2023-02-05 à 14.06.23865×358 16.8 KB

10

Ah, I did not see that. I have a lot of labels showing up as “undefined” despite having done a quick rebuild a couple of times. In this case, both AND and OR showed as “undefined” as did the column title…

This should work nicely to extend the query condition capabilities, particularly if I replace LIKE with REGEXP in AOR_Reports.php as I initially did.

Even nicer would be to add REGEXP as another condition operator in addition to EqualTo, Contains, BeginsWith, EndsWith etc.

11

If you have undefined labels, you have permissions/ownership issues on your server

1 Like
12

I checked ownership, ie user and group and every file/directory is set to apache:apache.

This would leave incorrect permissions as the culprit and I will double-check this later.

13

I reran the scripts below to set permissions but it does not seem to have made any difference:
find /var/www/html/test -type d ! -perm 2755 -exec chmod 2755 {} ;
find /var/www/html/test -type f ! -perm 0664 -exec chmod 0664 {} ;
chmod -R 775 /var/www/html/test/{cache,custom,modules,themes,data,upload}
chmod 775 /var/www/html/test/config_override.php 2>/dev/null

Have I missed any permission settings?

14

If you go in Admin/Schedulers, at the bottom of the screen, what is the user name mentioned in the crontab command?

That is the one you need to use for ownerships.

15

It says apache which is indeed the owner and the group. I am running CentOS 7 and SELinux has tripped me up before so I checked that as well but I cannot find any SELinux violations in the log.

Anything else you can suggest I check? Is the list of directories with 775 permssion above correct or have I perhaps missed any?

16

Have you set up your cron jobs? Sometimes they can interfere, if set up to run as root (or any other user than apache, in your case.