Iterating over records in a formula - is this possible?

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

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.

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.

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.

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.

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.

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.

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.

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.

So I’ve thought about the problem as if I needed to solve it in C# (my language of choice for other work) and this is what the function would look like. SUPER SUPER simple.

	foreach(var bundle in myParentBundles)
	{
		float proportionalValue = this.msrp / bundle.sumMSRP;
		float totalProportionalSalesFromBundle = proportionalValue * bundle.annualSales;
		myAggregateSales += totalProportionalSalesFromBundle;
	}

AirTable already contains ALL of this data:

• myParentBundles
• msrp
• sumMSRP
• annualSales

The logic here is 4 measly lines of code… :sob:

got it. Aside from scripting for simple automations that pull together data you could use Rollup fields. However, the functionality of these is somewhat limited.
I think there are huge advantages in using Airtable scripting instead of writing your new program from scratch, first of which that while the data aggregation/processing has to happen through the script, the visualisation and input of it can leverage Airtable’s functionality (forms, views, interfaces). So, yes, programming it from scratch would be an option but then you would also need to setup everything around the script which Airtable provides natively.
Hope this helps!

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

OK, I am happy to pay you to create this script for me if you can port that exact logic to AirTable.

hi @Andrew_Aversa of course. Let me share my calendly link so you can book a time of your convenience to go over your current base and scope the work: Calendly - Alessio Monino
Looking forward to talking with you about this

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

	foreach(var bundle in myParentBundles)
	{
		float proportionalValue = this.msrp / bundle.sumMSRP;
		float totalProportionalSalesFromBundle = proportionalValue * bundle.annualSales;
		myAggregateSales += totalProportionalSalesFromBundle;
	}

And here is part of the crux of the matter. Airtable formulas do not have anything like forEach.

That’s a shame. It would be a great feature to execute logic like this.

You can, but it takes a script. Not a formula.