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!

[Andrew_Aversa]

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:

[Alessio_Monino]

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

[Andrew_Aversa]

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

[Alessio_Monino]

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

[kuovonne]

Andrew_Aversa:

foreach

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

[Andrew_Aversa]

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

[kuovonne]

Andrew_Aversa:

It would be a great feature to execute logic like this

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