My client wants a report on a set of 10 repeating custom fields, each one of which has a date and one of a set of outcomes. e.g there is an 'Accommodation' custom field which has 2 fields - a date and a choice of various outcomes like 'homeless to hostel'. A person can of course have more than one 'Accommodation' outcome (it's a repeating custom field). The problem is the limitation of this type of field. Advanced search can find the data correctly (by using 'OR') but then they can't be exported as export doesn't support these fields. So over in Reports - which can export them ('Export to CSV') - they can't be reported on because if I select all 10 sets of fields it uses 'AND' (see the SQL that's generated) which isn't correct. I can't think of a way round this using the UI. Can anyone suggest a solution, or is this simply something that can't be done at the moment?

  • Andy - this sort of workflow is something we normally manage via Activities rather than custom fields - just a thought - and probably not a useful one at this point of your development - and it could well be something you already considered and dismissed
    – petednz - fuzion
    Commented Apr 24, 2018 at 20:15
  • Pete,. I think you're right, would have been better
    – Andy Clark
    Commented Apr 24, 2018 at 20:16
  • and too late to revisit (do an export and reimport data as Activities)?
    – petednz - fuzion
    Commented Apr 24, 2018 at 20:24
  • Good idea, I'll discuss with client - and thanks for the suggestion
    – Andy Clark
    Commented Apr 24, 2018 at 20:26
  • while thinking about that if you are perhaps using drupal you can get bettr reporting and workflow using Views and Webforms imo.
    – petednz - fuzion
    Commented Apr 24, 2018 at 20:44

1 Answer 1


I think you can do this either by

  1. Creating a report and controlling the whereClause().

  2. Use hook and alter the sql to use OR/AND.



  • Re. 1. Creating a report... can you please explain how to control the whereClause() ? How would I do that?
    – Andy Clark
    Commented Apr 24, 2018 at 17:28
  • Create an extension, copy existing report, add operator OR/AND(optional). In function where() you can control the where clause to use AND or OR clause. Commented Apr 24, 2018 at 17:34
  • Thanks for the rapid reply! So no solution using standard UI?
    – Andy Clark
    Commented Apr 24, 2018 at 17:36
  • Nope unfortunately !! Can you try Search builder and export the results that might work but i am not sure. Commented Apr 24, 2018 at 17:39
  • Search builder can 'sort of' do it, because you can use 'OR', but as the field titles are all the same ('Date met') then you can't tell which is which - and of course you can't then export the result because export doesn't support repeating custom fields. So almost but not quite!
    – Andy Clark
    Commented Apr 24, 2018 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.