Help

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

427 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ann_Shepherd
4 - Data Explorer
4 - Data Explorer

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!

2 Replies 2

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

Screen Shot 2020-03-19 at 12.26.41 PM

The [Stores] table looks like this:

Screen Shot 2020-03-19 at 12.28.30 PM

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:

Screen Shot 2020-03-19 at 12.30.50 PM

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.

Screen Shot 2020-03-19 at 12.33.11 PM

Ann_Shepherd
4 - Data Explorer
4 - Data Explorer

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