Skip to main content

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.

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}

 

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}

 


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. 

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.

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.


works, tx! I didn't know you could use formula in a Rollup field

Reply


Terms & Conditions