Can I do this with Functions (or something else)?

[Ann_Shepherd]

My base has two tables: Stores and Products.

The Stores table has a field linked to the Products table that lists all of the products for sale at each store.

Within the Products table, I have an Availability field that identifies whether each product is “In Stock” or “Out of Stock”

I would like to have a field in Stores that counts the number of products that are “In Stock” for each store.

I can’t figure out how to do that. I have successfully “counted” the number of Products by Store. But because the Availability data is a layer deeper, I can’t seem to get at it.

Am hoping someone can help. Thanks!

[Justin_Barrett]

Welcome to the community, @Ann_Shepherd! :grinning_face_with_big_eyes: Here’s a quick [Products] table I made with a handful of products:

The [Stores] table looks like this:

To calculate which products at each store are in stock, I used a rollup field. Most people probably only use the minimal example formulas in rollups, but the formula can be anything as long as it utilizes the values item, which is an array of the data pulled from the linked records. Here’s my setup of that rollup field:

In short, I’m converting the array to a string with no separation between items, removing the “Out of Stock” entries with one SUBSTITUTE() function, changing “In Stock” to a single character (“1” in this case) with another, then returning the length of the resulting string, which tells me how many products are in stock at that store.

[Ann_Shepherd]

thanks so much, @Justin_Barrett!
Really appreciate your thorough and timely response.