Iterating over records in a formula - is this possible?

[Andrew_Aversa]

I’m using AirTable to describe all products my company sells, or has ever sold, in our shop. Each record is a product. The tricky part is that some products are technically product bundles. To help describe this, I have a field called “Bundled Products” which can link to other records, for example:

Product X (bundle)
Bundled Products: Product Y, Product Z

Here’s where it gets tricky. The way our store reports revenue, every product (including bundles) has sales recorded separately. But for analysis purposes, I want to be able to consolidate the revenue from Product Y with the relative percent of revenue from Product X, the bundle that includes it.

I’m struggling to figure out how to create a formula or rollup for this. What I’ve done so far is define an additional field called “Part of Bundles”, so Product Y now has a reference to Product X, that contains it.

If I were writing a program for this, here are the logical steps I would need to calculate this “Master Revenue” figure for Product Y.

FOR EACH record in “Part of Bundles”:

1. Divide Product Y’s MSRP by (Bundle)'s MSRP to calculate what % of the bundle Product Y is. For the sake of example, let’s say Product Y is 60% of Product X.

2. Multiply the % calculated in step 1 by (Bundle)'s Net Revenue, to find the relative sales for this volume.

3. Repeat for all records in (Part of Bundles).

Once done, I would add THAT number to Product Y’s net revenue, so find the total amount.

In programmer logic this is super simple but it’s not apparent how to do this with AirTable. Any help appreciated… thanks in advance!

[Alessio_Monino]

hi @Andrew_Aversa,
airtable scripting can help you accomplish this. It is basically Javascript code that can be added directly as part of Airtable automations or can be used as standalone apps to perform certain actions. Turning what you just mentioned into a script and adding it inside an automation would do the trick.
Hope this helps!

Alessio
Website: alessiomonino.com
Email: alessio.monino@gmail.com

[kuovonne]

Welcome to the Airtable community!

There are many ways to approach this problem.

Here is just one way.

Let’s say you have the following tables:

• [Products]
• [Ratios] junction table between [Products] and [Bundles]
• [Bundles]
• [Sales] Each sale is linked to exactly one product or bundle

In the [Bundles] table, have the bundle’s MSRP and also a rollup of the sum of the MSRP’s of the products.

In the [Ratios] junction table, each record links to a product and a bundle. There is a rollup of the sum of the MSRPs of all the products in the bundle. There is also a rollup of the MSRP of the individual product. These two numbers are used to determine the the % of the net that goes to the product.

To find sales for the individual products, roll up sales that are linked directly.

To find the sales for the bundles, follow the linked records. In the bundle record, roll up the linked sales. In the junction table, rollup the sales for the bundle, and multiply the total bundle net amount times the product ratio to get the pro-rated amount for the product. Then, rollup the pro-rated amounts for the products from the junction table to the products table.

For a final revenue for both individual product sales and bundle sales, add the two totals in a formula field.

Note that this method does the calculations in a little different order, but the end result should. be the same.

[Randy_Steer]

I’m new to Airtable and just stumbled across this question so this suggestion might not work, but in Smartsheet or even Excel you can have computed columns, so for instance you could have a table that has 3 “imputed value” columns for Products A, B, and C that comprise Bundle X, along with direct sale entries for those products. Then a report or dashboard could sum up both direct sales and imputed values for each individual product. The imputed-value formulas might include “if” conditions to handle multiple types of bundles.

Also, @Andrew_Aversa, note that you would probably NOT want to compute the imputed value of an individual component in a bundle in quite the way you described, unless there is no price advantage in buying a “bundle”. Usually when Bundle X contains Products A, B, and C, the bundle price is a little less than the sum of A, B, and C independently. If that’s the case, you would accidentally overestimate the proportionate value of any one component if you divide the component MSRP by the bundle MSRP. So you would want to think about the proportionate values that you want to ascribe to each component in a bundle and use those in the “imputed value” column formulas.

[kuovonne]

Randy_Steer:

note that you would probably NOT want to compute the imputed value of an individual component in a bundle in quite the way you described
…
you would accidentally overestimate the proportionate value of any one component

Good point. I adjusted my post above to reflect this. It is also possible to have a manually field in the junction table to indicate the ratio.

[Andrew_Aversa]

Thanks; I only have one table at the moment. This seems like it would be a considerable amount of extra setup and manual work, which wouldn’t be much better than just calculating all this without AirTable?

Bundles are regular SKUs in our store, and I already have fields in my one (and only) table that roll up the individual MSRPs of the contained products. This calculated rollup value is what I would use when determining the % that any one individual product contributes.

Sales are also not represented in any way other than just the aggregate sales in a single field, like “2021 Net Revenue” for example, so I don’t understand why a separate table is needed for that.

[Andrew_Aversa]

Thanks; by “computed columns” I’m assuming you mean formulas. But the problem is that AirTable seems to lack a “foreach” or iterator in the formulas, so it’s not clear to me how to use it to solve this problem. Maybe I didn’t understand your suggestion?

As for the calculated % of bundle, I already have a field that calculates the true combined MSRP of a bundle and all component parts, so that is what I would use to compare an individual product’s MSRP vs. the bundle MSRP.

The problem again is that there seems to be no way to do this automatically, without writing custom fields or formulas for each and every possible product and bundle combination… not feasible when you have hundreds of bundles/products.

[Andrew_Aversa]

Thanks, I’ve looked at automations a bit and I’m not sure I understand how they would be used here. Can you use this to actually produce a value in a field? That is the ultimate goal here. I want to be able to run analytics on the data output all from a single table. If I have to create a separate dashboard or view, which is what scripting seems to require, then it’s not clear what the benefit of using AirTable for this would be over just writing my own C#, Python (etc) program for it.

[Randy_Steer]

I was trying to avoid the need to iterate/loop over all the rows by just using some formulas to create some “virtual” fields and let the summation capabilities of reports take care of selectively adding things up. However, I was supposing that you had a limited number of bundles where imputed value percentages could be worked out in advance in extra columns. If you have a lot of combinations possible, my approach wouldn’t work.

[Andrew_Aversa1]

I’m still not totally sure what you mean… but yeah, there are a lot of combinations. I think we have about 30 bundles. They contain anywhere from 2 to 60 products. Some individual products are part of up to 6 or 7 different bundles.

It’s really frustrating because the data is right there but AirTable seems to have no way to deal with it.

For example: I can create a field for a Product called “Part of Bundles”. I can manually link that product to the bundles it is a part of. That’s easy enough.

Then I can create a field that lists out the sum bundle MSRPs of every bundle its part of, so now I have a field that looks like:

6198,598,399

I can also create a field that lists the net sales of each of those bundles, which would print to another array.

But I have no way of doing anything with those array values. They’re a dead end.