CiviCRM Community Forums (archive)

Here's are two interesting related bugs and a patch to fix them.

Start with CiviCRM 3.4 from SVN, with sample data.

If you filter a report on a searchable Yes/No Radio custom field, you can see two types of errors. For this example, I created a custom field in the "Food Preference" set named "Extra helpings," as a searchable Yes/No Radio.  Then try a couple of things on the report "Event Participant Report (List)":

1. Be sure that none of the fields from the "Food Preferences" custom set are selected for display. Then, under filters for this custom set, ensure that no filters are used, except the "Extra helpings" filter, which should be set to "No".  Click "Preview report" to generate the database error: "Unknown column 'value_food_preference_2_civireport.extra_helpings_8' in 'where clause'"

2. Open the report anew without the above settings. This time, be sure that at least one of the fields from the "Food Preferences" custom set is indeed selected for display. (This gets us past the above error.) And under filters for this custom set, set the "Soup Selection" filter to "Bean Broth", and the "Extra helpings" filter to "No." Click "Preview report," and observe that, while there's no database error, only the "Soup Selection" filter is shown at the top of the report output, where you would normally expect all filters to be shown.

These two bugs are happening because the "No" filter value for Yes/No Radio fields is '0', which fails the test that's meant to check if the particular table is in use. In bug #1, this filter is the only thing that would lead the table to be included in the join, and since it fails the check, the table isn't included.  In bug #2, the filter is not displayed because the logic that checks for the existence of the filter doesn't see any value for the filter.

The fix might be as simple as this patch, which accepts string '0' as a valid value for a filter:

Index: CRM/Report/Form.php
===================================================================
--- CRM/Report/Form.php   (revision 35751)
+++ CRM/Report/Form.php   (working copy)
@@ -2001,7 +2001,7 @@
                                 $pair[$op] = (count($val) == 1) ? ( ($op == 'notin')? ts('Is Not'): ts('Is') ) : $pair[$op];
                                 $val       = implode( ', ', $val );
                                 $value     = "{$pair[$op]} " . $val;
-                            } else if ( !is_array( $val ) && !empty( $val ) && isset($field['options']) &&
+                            } else if ( !is_array( $val ) && ( !empty( $val ) || $val == '0' ) && isset($field['options']) &&
                                         is_array( $field['options'] ) && !empty( $field['options'] ) ) {
                                 $value = "{$pair[$op]} " . CRM_Utils_Array::value( $val, $field['options'], $val );
                             } else if ( $val ) {
@@ -2456,7 +2456,7 @@
             foreach( $prop['filters'] as $fieldAlias => $val ) {
                 foreach( array( 'value', 'min', 'max', 'relative' ,'from', 'to' ) as $attach ) {
                     if ( isset( $this->_params[$fieldAlias.'_'.$attach ] ) &&
-                         !empty( $this->_params[$fieldAlias.'_'.$attach ] ) ) {
+                         ( !empty( $this->_params[$fieldAlias.'_'.$attach ] ) || $this->_params[$fieldAlias.'_'.$attach ] == '0' ) ) {
                         return true;
                     }
                 }

Should I go ahead and post an issue and patch, or is there more to consider?

Thanks,
Allen