Create a list of numbers

Hey there,

I have a data type called “Cases”. It has several fields, including “Geo” (list of countries), and Rating (5 options: Low, Medium, High, Positive, Neutral).

Let’s say I have 3 cases in the database:

  • 1 case in France, rating Low
  • 1 case in United Kingdom, rating High
  • 1 case in Japan, rating Low

To feed a plugin i’m using (which build charts), I need the following lists:

  • List of unique countries referenced in cases: France, United Kingdom, Japan ( :white_check_mark: I got it using a “search for cases, grouped by geo, each item’s geo”)
  • List of counts of cases rated “Low” : in this example, it would be 1, 0, 1 (in the same order as countries)
  • etc for each rating.

I can not figure how to build this list of counts, I’ve been trying different types of groupings, aggregations… but nothing seems to be working :slight_smile:

Any help would be much appreciated,
thanks in advance

Denormalization is your best friend

Create a new data type for each rating


Have each one point to the Case

So the data structure for Low would be:
Low → Case

When rating = Low, you create a new Low

Now all you have to do to get the count is:
Do a search for Low
Do a search for High

It is pretty easy to have this list. I can display it in a textbox like below:

And here is the preview:

Search for Cases : each item's Country :unique elements is already giving the elements of unique countries, and you got this part.

Then, you format as text this list and for each country, you get the corresponding low case number:

And in this Search for Cases, you do the search based on the this text which is the current country and Low which is whatever rating you want:

You join these with a comma, and it should be good. But if you want a list, you can then split by comma ,:

You can do the same for all ratings. I did High as well in the textbox above. Totally same, except the search constraint in the format as text expression.

I set the country and rating both as text in my data type, but it doesn’t really matter. The same thing would work with options sets or other data types.

Here is the editor if you want to check more:

Amazing!! Thanks a lot! I really don’t have the reflex to use the format as text… I think i have to learn how to use it properly, it looks like I could save a lot of time in other parts of my app :slight_smile:

1 Like