Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 21, 2023 03:19 AM
Hi,
I have a table which is sort of a Bill Of Materials containing the all the required parts to assemble a single product, including the required quantity of each part.
It's basically looks like:
|Product|Part|QuantityRequired|
There is another Parts table containing the cost of each part.
I'm trying to calculate the total cost for each part in a single field. So far I was able to do it through two fields, one is lookup for the cost of the part and another field with formula {qty}*{cost from lookup field}
My question is can this be done directly in a single formula field, without needing the extra aux lookup field to fetch the price of a single part? Feels a bit redundant and was wondering if there's a way to fetch that lookup directly through the formula.
(The final calculation of of the product's cost is done in the Products table through a sum rollup on said result)
Thanks.
Solved! Go to Solution.
Dec 21, 2023 08:06 AM - edited Dec 21, 2023 08:07 AM
You can do it all in a single rollup field that rolls up the unit cost from the parts table.
Here is one possible aggregation formula for the rollup
SUM(values) * {quantity}
Keep in mind that if your costs change, it will change for historic records as well if you use this method.
Dec 21, 2023 07:51 AM
Hi, why you have another "Parts table" containing the cost of each part?
Why don't insert the cost directly inside the other table like this:
|Product|Part|QuantityRequired| Cost
and then you can have another formula field
|Product|Part|QuantityRequired|Cost|Calculation
with {QuantityRequired}*{Cost}
Dec 21, 2023 07:54 AM
This is done to keep the base design normalized. Same part might be used in multiple products and I don’t want to have duplicate entries of the part’s cost as this would lead to mistakes.
Dec 21, 2023 08:06 AM - edited Dec 21, 2023 08:07 AM
You can do it all in a single rollup field that rolls up the unit cost from the parts table.
Here is one possible aggregation formula for the rollup
SUM(values) * {quantity}
Keep in mind that if your costs change, it will change for historic records as well if you use this method.
Dec 21, 2023 08:12 AM
works, tx! I didn't know you could use formula in a Rollup field