1

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 Apr 24 '18 at 20:15
  • Pete,. I think you're right, would have been better – Andy Clark Apr 24 '18 at 20:16
  • and too late to revisit (do an export and reimport data as Activities)? – petednz - fuzion Apr 24 '18 at 20:24
  • Good idea, I'll discuss with client - and thanks for the suggestion – Andy Clark Apr 24 '18 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 Apr 24 '18 at 20:44
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.

HTH

Pradeep

  • Re. 1. Creating a report... can you please explain how to control the whereClause() ? How would I do that? – Andy Clark Apr 24 '18 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. – Pradeep Nayak Apr 24 '18 at 17:34
  • Thanks for the rapid reply! So no solution using standard UI? – Andy Clark Apr 24 '18 at 17:36
  • Nope unfortunately !! Can you try Search builder and export the results that might work but i am not sure. – Pradeep Nayak Apr 24 '18 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 Apr 24 '18 at 18:42

Your Answer

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

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