CiviCRM Community Forums (archive)

Hi,

I have set up a CiviCRM Contact view in Views 6.x-2.11 / CiviCRM 3.2.4. I have exposed filters for two custom fields: one a checkbox field, the other a multi-select. When I test either filter, it only retrieves contacts that have a single value selected for the relevant field: contacts with multiple values specified are never retrieved, unless the filter is left blank. This applies whether or not the "Force single" box is checked for the exposed filter, i.e. whether a Select or Multi-select is used as the exposed filter widget.

The Live Preview for the view reveals that the problem occurs because an incorrect WHERE clause is generated, e.g.:

WHERE (civicrm_contact.contact_type in ('Organization')) AND (civicrm_value_test_ocp_6.category_11 in ('2'))

The actual values stored in civicrm_value_test_ocp_6:

mysql> select id, entity_id, replace(category_11, char(1), '*'), replace(location_12, char(1), '*') from civicrm_value_test_ocp_6;
+----+-----------+------------------------------------+------------------------------------+
| id | entity_id | replace(category_11, char(1), '*') | replace(location_12, char(1), '*') |
+----+-----------+------------------------------------+------------------------------------+
|  1 |         3 | *1*2*                              | *1*2*                              |
|  2 |         4 | *2*                                | *2*3*                              |
|  3 |         5 | *1*4*                              | *2*                                |
+----+-----------+------------------------------------+------------------------------------+
(I've used asterisks to make the separators visible.)

So the WHERE clause above only matches entity_id 4 but should match both 3 and 4. I believe the clause should be something like:

civicrm_value_test_ocp_6.category_11 like concat('%', char(1), '2', char(1), '%')

or if multiple options are selected in the exposed view widget, then e.g.

(civicrm_value_test_ocp_6.category_11 like concat('%', char(1), '1', char(1), '%')) OR (civicrm_value_test_ocp_6.category_11 like concat('%', char(1), '2', char(1), '%'))

Edit 16 Nov 2010: I've filed an issue in JIRA: CRM-7093.

Edit 12 July 2011: there are now 3 issues about this on JIRA, looks as though it should be fixed in 3.4/4.1 (we ended up not using Views for this job).
http://issues.civicrm.org/jira/browse/CRM-7093
http://issues.civicrm.org/jira/browse/CRM-7292
http://issues.civicrm.org/jira/browse/CRM-7516

Dave J