We are using Civi (version 4.6.3) primarily as a case management tool.

In Advanced Search, when I try to create a query with more than one Custom Field Set used for two different Case Activities, the search falsely returns no results.

I recreated the error in demo -- I created two new custom fields to extend two different Case Activities for Housing Support Case Type : 1. Activity: Secure Temporary Housing Custom Field Set: case-housing1-test Field: Housing Status (dropdown select options:Temporary Housing, Homeless) 2. Activity: Long-term Housing Plan Custom Field Set: case-housing2-test Field: Is long term plan complete? (yes or no) I created a new contact: Joe Jack, and then created a Housing Support Case for him. In Secure temporary housing activity, Housing Status, I selected "Homeless". In Long-term housing plan activity, I selected "yes" to the question "Is long term plan complete?" . Now if you go to Advanced Search and do a search for Activities, housing status - Homeless, long term plan complete - yes, the search returns no records. If you do a search for housing status - homeless, you get Joe Jack. If you do a search for long term plan complete - yes, you get Joe Jack. But if you do a search for both you get nothing.

I believe the reason for this is that it doesn't recognize that the two activities belong to the same case. I was able to pull the correct results in a custom report I created by creating a view for each activity type and joining them in a SQL statement on case id.

Also,this is a separate issue, in Search Builder, when I search a custom field used for Activities where field IS NULL , it returns all records where that field IS NOT NULL but the value in the column shows the field is empty. screen shot 1 - gender is null If I do the same search but instead ask for records where field IS NOT NULL, it returns all the same records but this time the actual value is displayed in the column. screenshot 2 - gender is not null This is also true for is empty, is not empty.

  • Please edit your question to provide more information. What is your civi version? What search screen are you using exactly? Can you provide a screenshot? Can you reproduce this problem on the demo site? – Coleman Aug 26 '15 at 2:07
  • I am aware that in a localised version of civicrm the IS NULL is twice in the list. The first one meaning IS EMPTY which differs from IS NULL. – Jaap Jansma - CiviCooP Aug 31 '15 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.