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?
5
-
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, 2018 at 20:15
-
Pete,. I think you're right, would have been better– Andy ClarkApr 24, 2018 at 20:16
-
and too late to revisit (do an export and reimport data as Activities)?– petednz - fuzion ♦Apr 24, 2018 at 20:24
-
Good idea, I'll discuss with client - and thanks for the suggestion– Andy ClarkApr 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 ♦Apr 24, 2018 at 20:44
Add a comment
|
1 Answer
I think you can do this either by
Creating a report and controlling the whereClause().
Use hook and alter the sql to use OR/AND.
HTH
Pradeep
answered Apr 24, 2018 at 17:25
-
Re. 1. Creating a report... can you please explain how to control the whereClause() ? How would I do that? 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. Apr 24, 2018 at 17:34
-
-
Nope unfortunately !! Can you try Search builder and export the results that might work but i am not sure. 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! Apr 24, 2018 at 18:42