# Counting the number of occurrences of an item in a joined list

**URL:** <https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277>\
**Category:** Ask for Help\
**Created:** [June 23, 2021, 12:57pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277 "2021-06-23T12:57:22Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 12:57pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/1 "2021-06-23T12:57:22Z")

</div>

Having a mental blank here…

Consider the following:

 ![Screen Shot 2021-06-23 at 8.55.42 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/4/245701ad9d56b2144b48fd8042f8924bf007e817.png)

For each row, I want to count how many times `Code` appears in the associated `Code List`.  
So expected result would be:

- Row A: 4
- Row B: 3
- Row C: 4
- Row D: 1
- Row E: 2

How? 🤔

---

<div class="post-metadata">

**Author:** ![adriasoce](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/adriasoce/32/24153_2.png) [@adriasoce](https://community.glideapps.com/u/adriasoce)\
**Post date:** [June 23, 2021, 1:13pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/2 "2021-06-23T13:13:32Z")

</div>

Maybe split code list by commas and then relate the splited column with code in order to rollup a count in that relation

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [June 23, 2021, 1:17pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/3 "2021-06-23T13:17:00Z")

</div>

This is what I was going to suggest 🙂

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 1:22pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/4 "2021-06-23T13:22:43Z")

</div>

yeah I tried that, but the problem is that the relation uses _all_ rows…

 ![Screen Shot 2021-06-23 at 9.20.39 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/4/a4c936afee6c2e9cb80da8d6cdc55a0ca434af1b.png)

So what I get is a count of how many rows each code appears in, which isn’t what I want.  
I feel like I’m missing something obvious…

---

<div class="post-metadata">

**Author:** ![Alexis\_AZOULAI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alexis_azoulai/32/20550_2.png) [@Alexis\_AZOULAI](https://community.glideapps.com/u/Alexis_AZOULAI)\
**Post date:** [June 23, 2021, 1:38pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/5 "2021-06-23T13:38:06Z")

</div>

Maybe you can adapt this method : [https://community.glideapps.com/t/workaround-countif-in-a-row/27802](https://community.glideapps.com/t/workaround-countif-in-a-row/27802)

---

<div class="post-metadata">

**Author:** ![adriasoce](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/adriasoce/32/24153_2.png) [@adriasoce](https://community.glideapps.com/u/adriasoce)\
**Post date:** [June 23, 2021, 1:40pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/6 "2021-06-23T13:40:52Z")

</div>

Oh true… We have to think in another way

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [June 23, 2021, 2:09pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/7 "2021-06-23T14:09:52Z")

</div>

Where does Code List come from? Is it transferred from a Joined List somewhere else, or is it just entered as a comma delimited list?

Also, are the only codes A-E, or could there be others?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 2:22pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/8 "2021-06-23T14:22:52Z")

</div>

> [@Jeff\_Hager](#):
>
> Where does Code List come from? Is it transferred from a Joined List somewhere else, or is it just entered as a comma delimited list?

yeah, it comes from elsewhere.

> [@Jeff\_Hager](#):
>
> Also, are the only codes A-E, or could there be others?

yeah, lots more - 51 in total.

What I gave there is just a contrived example, so I’m looking for a general solution. This is actually related to my shift roster - remember that? What I need to do is present each worker with a table that summarises how many of each shift type he is rostered to work for any given month.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2021, 2:25pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/9 "2021-06-23T14:25:18Z")

</div>

This is an interesting challenge. I really wish we could point to a column in the left side of the template column, then I can convert the list to a number with only relevant codes left, then determine the # of digits of that number using LOG.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [June 23, 2021, 2:27pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/10 "2021-06-23T14:27:29Z")

</div>

@ThinhDinh That’s exactly where I was trying to go with it. Might be something to remember for the next live coding.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 2:37pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/11 "2021-06-23T14:37:02Z")

</div>

Jeff,

Just to provide a little more context, this is my starting point…

I have one table that looks like so:

 ![Screen Shot 2021-06-23 at 10.29.48 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/7/17defb659102a73170d4733a32103c6306df21c4.png)

The list of codes is in column A.

And then the roster table looks like this:

 ![Screen Shot 2021-06-23 at 10.30.11 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/0/c01af0e2546c603397cdd25246107b99a1b94624.png)

In this table, there is one row per worker, per month, and the list of codes is in column H.  
I have no problem creating a relation between the two tables, using the signed-in userID combined in a template with the year/month, and what I started by doing was:

- created a split text column in the roster table, creating an array from the code list
- in the first table (the one that lists all the codes):
  - created a single relation back to the roster table to target the correct row
  - then dragged the split text column across through that relation with a lookup
  - and then created a multi-relation, joining the code in each row (column A), to the split text column

- and this is where I hit the brick wall…

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [June 23, 2021, 4:03pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/12 "2021-06-23T16:03:31Z")

</div>

Uggh, these kinds of situations drive me nuts. So close, but not quite there. Especially when you’re used to looping through arrays and working with each array item independently for simple things like counts, manipulation of data in simple or object arrays, or adding and removing array items.

I’m still trying to think of something, short of a brute force attack.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 9:15pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/13 "2021-06-23T21:15:14Z")

</div>

yeah, it’s crazy because intuitively it feels like it should be a very simple thing to do.

oh well, I think this might call for a code solution. Which isn’t such a bad thing, I guess. I can just extend my existing import code to generate a CSS table and stick it on the end of each row as an extra column.

---

<div class="post-metadata">

**Author:** ![V88](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/v88/32/90054_2.png) [@V88](https://community.glideapps.com/u/V88)\
**Post date:** [June 23, 2021, 10:21pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/14 "2021-06-23T22:21:33Z")

</div>

We are overdue some string / text functions

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 23, 2021, 11:09pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/15 "2021-06-23T23:09:44Z")

</div>

Well, that was easy… just needed a small helper function

```auto
function build_summary_table(code_string) {
  var code_array = code_string.split(',');
  var shifts = {};
  while (code_array.length > 0) {
    var code = code_array.shift();
    if (shifts.hasOwnProperty(code)) {
      shifts[code]++;
    }
    else {
      shifts[code] = 1;
    }
  }
  var summary = '<div class="redTable outerTableFooter"><div class="tableFootStyle"><div class="links">Shift Summary</div></div></div>';
  summary += '<div class="divTable redTable"><div class="divTableHeading"><div class="divTableRow">';
  Object.keys(shifts).sort().forEach(function (code) {
    summary += '<div class="divTableHead">' + code + '</div>';
  });
  summary += '</div></div><div class="divTableBody"><div class="divTableRow">';
  Object.keys(shifts).sort().forEach(function (code) {
    summary += '<div class="divTableCell">' + shifts[code] + '</div>';
  });
  summary += '</div></div></div>';
  return summary;
}

```

Truth be told, this is probably a better solution than anything that could be done with Glide computed columns, as it just adds one basic column, which I can use directly in a Rich Text component.

End result:

 ![Screen Shot 2021-06-24 at 7.04.39 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/a/2afc3adede1fc95274c2cf49a25bfdd37ec1e476.png)

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [June 23, 2021, 11:16pm UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/16 "2021-06-23T23:16:17Z")

</div>

That looks very nice.

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [June 24, 2021, 12:00am UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/17 "2021-06-24T00:00:51Z")

</div>

Man that’s pretty.

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [June 24, 2021, 1:31am UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/18 "2021-06-24T01:31:00Z")

</div>

Just as simple as that, right?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [June 24, 2021, 1:53am UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/19 "2021-06-24T01:53:36Z")

</div>

hehe, yup! 😆

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [June 24, 2021, 3:48am UTC](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277/20 "2021-06-24T03:48:58Z")

</div>

I see what you mean about the left hand template field. Was this your plan?

 ![Screen Shot 2021-06-23 at 11.47.37 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/0/30bec81e14eb4dd52cb70b62f8f35dbdea09edab.png)  
But instead of every code, you’d just reference the code column?

Then do some template/math trickery?

 ![Screen Shot 2021-06-23 at 11.47.28 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/b/2bbdb3fb37e25c64581f22085a8ae82b9a9e8cfd.png)

[Next page](https://community.glideapps.com/t/counting-the-number-of-occurrences-of-an-item-in-a-joined-list/28277.md?page=2)