Fetching lookup field into a formula - is this possible?

[sgrass]

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.

[RdMedia_srl]

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}

[sgrass]

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. 

[kuovonne · Accepted solution]

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.

[sgrass]

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