How to count occurrences for each values in an array?

Hey folks, I’m stuck with this one and could really use some help. I have 3 tables set up:

  • Brands contain all infos for brands, with relation to Reviews
  • Reviews contains user reviews of different brands, and every row contains Tags for each brand.
  • Tags is a helper table with all the tags

I’m trying to count how many times each Tag have show up for each brand:

In this example, the count should be:
Bad Packaging 1
Ethically Sourced 1
Tasty :yum: 2
High Sell-Through 1

I hope to generate something like the following, and plot the infos in a radial chart
Screen Shot 2023-03-02 at 6.41.20 PM

Thanks so much in advance!

Would you mind adding screen shots of all 3 tables?
I think the one you already added is from your Reviews table? :thinking:

Hey Darren! Sure thing!


Okay, cool.
Another question - when would you want this summary to be generated?
For example, is it something you would expect to have available when you view a Brand Details screen?

Also, the joined_Tags column shown in your Brand Database table - is that via a relation to your Reviews table?

Just want to make sure I have a clear picture of your setup and desired flow.

  1. Yea, ideally tags and counts can be displayed when people visit individual brand’s page.
  2. Correct, it’s pulling from the reviews table.

Okay, take a look at this:

1 Like

Wow, thank you Darren! Amazing solve as always!

Curious, would this be possible to solve with either Experimental code or Javascript?

Counting occurrences in an array feels like such a simple task, had no idea can get this complicated. :joy:

Yeah, it would be possible.
If I was to do it using JavaScript I’d still take the same approach, but I’d replace about two-thirds of those columns in the Tags table with a single JavaScript column. Essentially, once you have the Joined List of tags, you can pass that to a JavaScript column and do all the work there.

So the input to the JavaScript column would be the jlTags column, and the output would be the same as the ifTagSummary column.

2 Likes

Well,

I usually take this way to solve this requirement (via JS):

Saludos!

1 Like

Hey gvalero! Great seeing you here, I actually started with your solution few days ago, I was able to extract counts from my Tags table using relation to Reviews table and a joined list.
But couldn’t quite get the js code to work inside Brands table which is what I wanted. I hope to have individual tag counts for each brand and display them.

Any chance you could help adapt the code?

Hey @gvalero @Darren_Murphy , also I noticed that inside a collection, if i use Tags as filter, it actually does what I hope. So the function is there, just not sure how to extract it.

filter by Tags

That is natively handled by Glide and you don’t have that info available to be extracted.

@mikec here is a JavaScript option that I alluded to earlier in the thread:

let arr = p1.split(/,\s?/);
return arr.filter(x => x === p2).length;

Note:

  • this is done in the Tags table, not in the Brands table. It’s much easier to do it this way.
  • a regular expression is used in the split. This handles the case where there is a space after the comma in the joined list.

Hola!

Sorry for my late reply, but I see that Darren already found the missing piece you needed.

Saludos!

1 Like

Thanks @Darren_Murphy, works brilliantly! Appreciate the help.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.