Using LEN to count records -- based on values in a reference column?

Ask for Help
1

Grist noob here, evaluating the system. Really impressed so far! I’m curious about something I can’t find addressed in Help or the forum: getting a LEN count from a reference column in another table.

Scenario:
Studios: table of studios identified by Studio_num.

Equipment: table of equipment across all studios, with a reference column Equipment.Studio_num linking each record to a Studio.Studio_num. (So equipment can only be assigned to a Studio_num existing in Studios.)

What I’m adding:
A column in “Studios” indicating the count of equipment records associated with each studio:

What doesn’t work:
This LEN formula returns 0:

LEN(Equipment.lookupRecords(Studio_num=$Studio_num))

I presume this is because Equipment.Studio_num references a whole record in Studios.

What works:

If I create Equipment.Studio_key as $Studio_num.Studio_num, the formula returns correct counts:

LEN(Equipment.lookupRecords(Studio_key=$Studio_num))

Visuals:

image
image681×840 72.7 KB

What I’m wondering:

This additional column is a decent workaround, but maybe I’m missing a more elegant way to use dot notation to get a LEN count of relevant records from a reference column?

Thanks for any thoughts!

2

Hi there and welcome to the Community! :wave:

For being a self-proclaimed “Grist noob”, you did remarkably well with creating the lookupRecords function! KUDOS! And your presumption that it is not working due to the reference column is absolutely correct. Because a reference columns stores the column ID, we need to use the ID as the match criteria. This is admittedly a difficult topic to grasp overall.

Update your formula to len(Equipment.lookupRecords(Studio_Number=$id))

image
image1722×542 42.4 KB

That’s it!

Some extra information; we refer to these as Reverse Lookups because you’re using a reference column in reverse. The Studio Number column in the Equipment table uses the reference column to lookup data in the Studio table. We want to use that table link now from Studio to Equipment. So we use a reverse lookup and match on the $id.

If you have any follow up questions, please let us know!

Thanks,
Natalie

1 Like
3

Ah, cool! It was indeed that simple.

I had a feeling there was a way, but I hadn’t come across Reverse Lookups.

Thank you, Natalie for the quick response and clear explanation — another item for the “impressed” column. :grin:

1 Like