3

I have a custom field that allows users to select multiple options using a checkbox input.

The custom field has:

  • Option A
  • Option B
  • Option C
  • ...

The user can pick one or more of these, e.g. A+B or A+C or B+C+D etc.

I want to create a report that shows for example:

Selected option Number of contacts
A 12
B 7
C 22
...

I've tried to create a search display using SearchKit by grouping on the custom field but instead of grouping on the individual options, this groups on the combined options that the user has selected.

I get something like this instead, which is not what I want:

Selected option Number of contacts
A+B 9
A+C 1
B+C+D 4
...

Any suggestions?

3 Answers 3

3

I was able to achieve something very similar to your request using multiple Data Segments, with the following steps:

  1. Create a Data Segment for each of your option values (e.g. if the options are A, B & C you will create 3 data segments: Data segments
  2. For each segment, you'll create one default option (No) followed by an option (Yes) that checks whether the field contains the given value (order matters here - the default needs to come first): Data segment edit
  3. Create a search that includes all data segments as columns, and aggregates them using SUM(). For aggregation, you want to group all contacts into a single row, so group by something they all have in common (you can group by "contact type" or "contact is deleted"). In this example I gave one contact A+B and a second contact B+C and it correctly gives sums for all 3: Saved search
4
  • hmm maybe this is what i need to do for summary reporting for lineitems, since all my approaches so far have given incorrect totals
    – petednz - fuzion
    Commented Dec 20, 2023 at 3:09
  • Thanks @coleman, with a bit of playing around, I've managed to get this working correctly now, which is great. It works fine for the 17 options that we've got on this site but may be unwieldy if there are many more than this. As mentioned in chat, it may perform slowly if there is a lot of data, In which case you've suggested saving the search as an entity display so that it is run by cron rather than the user. Commented Dec 20, 2023 at 16:08
  • I've discovered that data segments can be exported to PHP in a similar way to saved searches. This means that all of the configuration can be saved in an extension. Commented Dec 20, 2023 at 16:09
  • 1
    Yes, and since you can put logic inside a .mgd.php file, it can generate each DataSegment in a loop based on the existing option values. That would make the .mgd.php file much smaller and also responsive to changes in the field's options.
    – Coleman
    Commented Dec 20, 2023 at 16:17
1

Initially, I thought of creating a DataSegment with values like:

  • A: [your_custom_field, CONTAINS, A]
  • B: [your_custom_field, CONTAINS, B]
  • C: [your_custom_field, CONTAINS, C]

However the nature of DataSegments is that it stops looking after the first true condition is reached, so items with A+B would be listed as "A" only, B+C would be "B" only, etc.

Instead I think you need to create one Data Segment per option, with one option per segment. You could then create a search, grouped by Contact Id, and add each segment as a column (this gives you a pivoted version of your example, but at least you will have the data).

1
  • I've just been looking at data segments. The problem is that is that it only counts each contact once. It matches for the first segment but not the subsequent ones. Commented Dec 19, 2023 at 18:50
1

If you are using CiviCRM's out of the box multivalue checkbox then all the solutions are likely to be a hack and not performant due to that field type being an 'SQL antipattern'.

It may not be feasible in your case, but, I would consider switching to storing the data in a custom dataset that allows multiple rows per entity (this is currently only possible for contacts but should become available for all in early 2024). This would open up the option to do the query 'properly'.

Though note: if your plan is to collect this info from the public on a form builder form then you may need or want to improve the UI for presenting multi-value custom data as depending on your use case, this might look fairly clunky.

In the mean time, aside from what Coleman has suggested, another approach that might be worth exploring is using the EntitySet API which allows you to do unions. I am not sure if / how well that is supported that in Searchkit but a union of single row queries that each gives a count for where field like '%A% might work. If there aren't too many rows and they don't change too often.

1
  • This is a potential option but as you say, isn't a great option for those inputting the data. Commented Dec 19, 2023 at 19:39

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.