Count unique values from array column (via values from Pack)?

Making Packs
1

I’m building a private Pack that pulls information from an API into a Coda table. Most of the information that the API returns is a string or a number, but there’s one field that’s an array. For example:

{ "items": [
	{  
		"stringProperty": "String",
		"numberProperty": 2,
		"arrayProperty": ["cat", "dog", "fish", "bird"],
	}
}

Where the arrayProperty could contain any number of values.

I think I’ve got my schema set up to handle the array (or at least the Pack isn’t returning an error — hat tip to Pack formula resultType of object with property type of StringArray? for that one). The schema looks like this:

const mySchema = coda.makeObjectSchema({
  properties: {
    stringProperty: { type: coda.ValueType.String },
    numberProperty: {
      type: coda.ValueType.Number,
      fromKey: "id",
    },
    arrayProperty: {
      type: coda.ValueType.Array,
      items: { type: coda.ValueType.String }
    },
  }

On the resulting table when I install the Pack, the arrayProperty column’s type is Text.

What I’d now like to do is create a related table that shows all the arrayProperty items individually, and be able to create another column in the related table that counts the unique uses of each item (how many times “cat” appears overall, how many times “dog” appears, etc.).

I have tried changing the arrayProperty column to column type “Relation” and “Create a new table,” which does seem to make a table of the individual array items; however, when I create a number column with a formula (First Table.filter(arrayProperty = thisrow.arrayProperty).Count()), I only get results for rows with cat or dog in them individually (so, given cat and cat, dog, the formula column returns just 1 for cat and 0 for dog, not 2 and 1 respectively).

I figure this is just a matter of my configuring something wrong. Can anyone point me in the right direction? Thanks!

2

Please post in Making Packs

Sincerely
Jannis

3

@Jannis, sure, happy to. But I was thinking this actually wasn’t a Packs problem, despite that the information is coming from a Pack; I’m thinking it’s a Related columns problem (what we used to call Lookups!). Do you have a guess at what the solution might be that leads you to believe this could be solved over in the Packs channel?

4

I think I got it.

First Table.Filter(arrayProperty.Contains(thisRow.arrayProperty)).Count() seems to work. It’s not enough just to filter; this one also needs the Contains() function. If anyone knows a better way, I’m still open to it. Thanks!

5

Yeah you’ve got it right here, using Contains to search for a match within the array.

Note that, if you run into any trouble with the second table, you could make it a sync table via the Pack, and then reference table 2 values explicitly from table 1. But if your auto-populated table 2 is working fine, may as well stick with that cause it’s easier.

6

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