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

Having a mental blank here…

Consider the following:

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?

1 Like

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

1 Like

This is what I was going to suggest

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

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…

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

Oh true… We have to think in another way

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?

2 Likes

yeah, it comes from elsewhere.

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.

1 Like

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.

3 Likes

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

3 Likes

Jeff,

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

I have one table that looks like so:

The list of codes is in column A.

And then the roster table looks like this:

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…

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.

4 Likes

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.

2 Likes

We are overdue some string / text functions

2 Likes

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

``````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:

8 Likes

That looks very nice.

Man that’s pretty.

Just as simple as that, right?

1 Like

hehe, yup!

2 Likes