Query and group on multiple fields?

I have a thing called Animal that has 3 fields say, fruit 1, fruit 2 and fruit 3. Each field has the type “fruit” option set with 7 different fruits. Is it possible to use the :group by (or some other) function to aggregate across all of these fields and count the total number of occurrences of each entry?

As an example:

animal 1
fruit 1 = banana, fruit 2 = orange , fruit 3 = pear

animal 2
fruit 1 = apple, fruit 2 = banana , fruit 3 = plum

animal 3
fruit 1 = plum, fruit 2 = grapes , fruit 3 = banana

I would like to get an aggregated list of the count of each fruit in these combined fields (sorted from highest to lowest)

banana (3), plum (2), pear (1), orange (1), grapes (1), apple (1)


I was able to generate a list of all the entries from each field so now I have a list of texts like this:

Banana, Banana, Banana, Plum, Plum, Pear, Orange, Grapes, Apple.

Is there an easy way to get a count of each item in this list as a list of numbers?

Reference the text list, and use a filter operator, and in the filter operator have a dynamic expression that looks to see if the option set display value is shown.

I think what you’d likely want to do, is have a repeating group of the option set of fruits displayed, then in that repeating group have a text element that will show the option sets display value, and then have the dynamic expression to look at the list of texts (where ever that is held) and filter by saying text is ‘currnent cells option set display’ followed by :count.

1 Like

Worked great. I appreciate you!

The method you mentioned worked great for getting the counts, however, I also need to sort the repeating group on the :counts in descending order. Are there any methods you can think of to do this?

probably using the :group by operator