Answers.Body: <p>I think you can do this either by </p>
  
  <ol>
  <li><p>Creating a report and controlling the whereClause().</p></li>
  <li><p>Use hook and alter the sql to use OR/AND.</p></li>
  </ol>
  
  <p>HTH</p>
  
  <p>Pradeep </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T17:28:42.073Z
Answers.Text: Re. 1. Creating a report... can you please explain how to control the whereClause() ?  How would I do that?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T17:34:48.950Z
Answers.Text: 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.
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T17:36:16.190Z
Answers.Text: Thanks for the rapid reply!  So no solution using standard UI?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T17:39:32.420Z
Answers.Text: Nope unfortunately !! Can you try Search builder and export the results that might work but i am not sure.
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T18:42:05.717Z
Answers.Text: 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!
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-24T17:25:09.073Z
Answers.LastActivityDate: 2018-04-24T17:25:09.073Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>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?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-04-24T20:15:07.103Z
Comments.Text: 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
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-04-24T20:16:41.043Z
Comments.Text: Pete,. I think you're right, would have been better
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-04-24T20:24:12.003Z
Comments.Text: and too late to revisit (do an export and reimport data as Activities)?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-04-24T20:26:36.980Z
Comments.Text: Good idea, I'll discuss with client - and thanks for the suggestion
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-04-24T20:44:35.760Z
Comments.Text: while thinking about that if you are perhaps using drupal you can get bettr reporting and workflow using Views and Webforms imo.
Comments.UserDisplayName: petednz - fuzion
Tags: <custom-fields><civireport><4.7>
Title: How to report on repeating custom fields
ThreadText: # How to report on repeating custom fields
  
  (Asked by: username_0 on 2018-04-24)
  
  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?
  
  **Tags:** custom-fields, civireport, 4.7
  
  > **Comments:**
  > **username_1**: username_0 - 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  
  > **username_0**: Pete,. I think you're right, would have been better  
  > **username_1**: and too late to revisit (do an export and reimport data as Activities)?  
  > **username_0**: Good idea, I'll discuss with client - and thanks for the suggestion  
  > **username_1**: while thinking about that if you are perhaps using drupal you can get bettr reporting and workflow using Views and Webforms imo.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2018-04-24. Score: 0
  
  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.
  
  HTH
  
  username_2
  
  > **Comments:**
  > **username_0**: Re. 1. Creating a report... can you please explain how to control the whereClause() ?  How would I do that?  
  > **username_2**: 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.  
  > **username_0**: Thanks for the rapid reply!  So no solution using standard UI?  
  > **username_2**: Nope unfortunately !! Can you try Search builder and export the results that might work but i am not sure.  
  > **username_0**: 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!  
  
  ---
  
  