Multiplying a linked record

[Herbshirt]

Hi there,

See screenshot attached. Components have a cost, multiple Components make up a Panel, multiple panels make up a Humpy. BUT - a Humpy may have, for example, 6 x P6/24’s and/or 3 x P9/24.

How can I multiple each of the panels by a number in order to calculate the PanelsCost?

Thankyou.

[Justin_Barrett]

You’ll need a join table to make this happen. I’ll call this [Panel Use], but rename it if you wish.

Aside from the primary field (which I’ll get to later), you’ll have four fields in this table:

• {Panel} - a link to a single panel record from the [Panels] table
• {Humpy} - a link to a record from the [Humpys] table that this panel will be used in
• {Quantity} - How many of this panel will be needed in the assigned Humpy
• {Cost} - The panel cost multiplied by the quantity. This can actually be a rollup field pulling cost values from the linked panels, and using the aggregation formula: values * Quantity

For the primary field, I suggest making a formula that concatenates the panel, Humpy, and quantity so you know at a glance how it’s assigned. Something like this:

Humpy & " - " & Panel & " x" & Quantity

Finally, the rollup in the [Humpys] table would add all the totals from the linked [Panel Use] records. Here’s how my setup looks after this is done. First the [Panels] table:

Next the new [Panel Use] table:

And finally the [Humpys] table:

What’s nice is that all the linking is done in [Panel Use], and the calculation in [Humpys] happens automatically. Any time you change quantities or add/remove links in [Panel Use], the total cost for the related Humpy updates instantly.

[Herbshirt]

Thanks for your comprehensive response there Justin. I’m pretty new, I’ve read your answer a few times and it’s probably going to take me a couple of go’s to get my head around it. I’ll let you know if I have any difficulty.

Again, much appreciated.

[Herbshirt]

Justin_Barrett:

Humpy & " - " & Panel & " x" & Quantity

Sorry, are you able to elaborate on this line? I’m not sure what that does or what the result should look like.

[Herbshirt]

Here’s a shot of my other tabs in question in case it helps.

[Justin_Barrett]

Herbshirt:

Sorry, are you able to elaborate on this line?

That line does just what the description above it indicates. It takes the values from the {Humpy}, {Panel}, and {Quantity} fields and uses them to build a string, which acts as a convenient descriptor for that record. The & operator concatenates the pieces around it and outputs a string.

The {Name} field in the screenshot from the [Panel Use] table shows what that formula outputs. Here it is again:

It provides a quick view of the Humpy the panel is assigned to, the panel name, and how many of that panel are assigned in that record.

[Herbshirt]

In this case I would need a new table [Panel Use] table for each Humpy?

[Justin_Barrett]

No. There would only be a single [Panel Use] table. When you’re done adding records connecting one Humpy to its related panels, continue adding more records connecting the next Humpy to its related panels. In other words, that table will eventually link all Humpys to their respective panels. The links keep all of the Humpy-panel connections separate from each other.

[Herbshirt]

Just dropped in to say Thanks @Justin_Barrett. It’s working a treat.

[Herbshirt]

Not sure if this is pushing the limits of Airtable but…

Each Humpy is made up X number of P26 and X number of P5 etc etc.

Am I able to create a user-friendly view/form where we could customise how many of the various panels are used to make up a Humpy to create a quote?