Multiplying a linked record

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?

Screen Shot 2020-01-24 at 2.18.08 pm

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:

Screen Shot 2020-01-24 at 7.27.34 AM

Next the new [Panel Use] table:

And finally the [Humpys] table:

Screen Shot 2020-01-24 at 7.28.52 AM

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.

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.

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

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

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.

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

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.

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

1 Like

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?

Off the top of my head (seconds after I read the post), I can’t think of a way to build such a system natively within Airtable. It would require multiple iterations of:

  • Pick a panel
  • Set the quantity for that panel

…all in a form that wouldn’t care if you wanted to add one panel or several dozen. Airtable forms aren’t designed for that kind of flexibility. This would require integration with a third-party form tool like Jotform, Typeform, etc.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.