Reports cannot filter for Contacts with multiple selections in a multi-select Custom Field
Overview
If a Contact has multiple selections in a multi-select Custom Field, a Report which filters for "one of" those selections will never find that Contact. There is no "includes" or similar option which finds Contacts who have the specified selection and other selections as well. The generated SQL syntax is not compatible with the storage format of multi-select custom fields when more than one option is selected.
For example: Consider the demo database at https://demo.circle-interactive.co.uk/
It has a Custom Field called "Most Important Issue" with the options "Environment", "Education", and "Social Justice".
Suppose we create another Custom Field which uses the same options, and it is a multi-select. We'll call this field "Important Issues" and check "searchable" and "multi-select".
Now we edit two contacts at random - give one of them "Environment" in their "Important Issues" field, and give the other one "Environment" and "Social Justice" in the same field.
Last, we create a Contact Summary report showing the Important Issues. In the filters, we select "Is one of" - "Environment", and run the report. It will find the Contact who only has "Environment" selected, but the one who has that and "Social Justice" will not be shown.
If you see this issue shortly after it is reported, the described setup may be visible in this report:
https://demo.circle-interactive.co.uk/civicrm/report/instance/39?reset=1&force=1
Reproduction steps
- Log into a civi demo site (e.g. demo.circle-interactive.co.uk)
- Create new custom fieldset "Test Report Filter Issue"
- Add a custom field:
- Field Label: Important Issues
- Alphanumeric Select
- Multi-Select is enabled
- Reuse an existing set of multiple choice options if suitable (e.g. from "Most Important Issue") or create a new set
- Searchable: Yes
- Edit two random Contacts
- For one, set the new "Important Issues" field to "Environment"
- For the other, set the new field to "Environment" and "Social Justice"
- Create a new "Constituent Report (Summary)" (/civicrm/report/contact/summary?reset=1)
- in "Columns", tick "First name", "Last name", and "Important Issues" (the new custom field)
- in "Filters", choose "Important Issues" - "Is one of" - "Environment"
- Run the report
Current behaviour
The report only finds the Contact who has the single selection.
There is no other way to filter for people who do have the Environment selection (e.g. "includes one of" as distinct from "is exactly one of").
Expected behaviour
If some sites do want the "is exactly one of" filter, others (like ours) will need a separate "includes one of" option. I may be able to do this with custom report templates for our site as needed, but a system-wide solution would be much better.
The behaviour appears to have changed when we upgraded to 5.27 from the previous ESR, though we've only noticed it a few months later.
From our perspective, the old behaviour was correct and expected, and the "is one of" text could have been improved by changing it to "includes one of". However, the new behaviour may be desired by some sites. I think that may be implied by this old issue, but I may be misunderstanding since they're also discussing the regex as it relates to the storage format:
https://issues.civicrm.org/jira/browse/CRM-18803
Environment information
- Browser: Chrome 86.0.4240.111 (Official Build) Arch Linux (64-bit)
- CiviCRM: 5.27.5, also tested on 5.29.1 and 5.30.1
- PHP: 7.3
- CMS: Drupal 7.73
- Database: MariaDB 10.3
- Web Server: Apache 2.4
Comments
A couple of years ago, there was some discussion of a similar issue with contact subtypes, and Eileen wondered whether it might affect custom fields as well:
https://github.com/civicrm/civicrm-core/pull/13158#issuecomment-442334591
At the time, it was still using a regex, but the new SQL generated by the report takes the simpler form "IN ('3')", which will never match a column with a '3' nestled between control codes and other values.
Yesterday when trying to trace the path which generated the SQL, I thought this line might be relevant: https://lab.civicrm.org/dev/core/-/blob/master/CRM/Core/BAO/CustomField.php#L2583-2584
Considering that the new behaviour might be deliberate, I thought I should report the issue rather than trying to find a solution since the correct path forward is not obvious to me.