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.
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.
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.
