Fetching lookup field into a formula - is this possible?

Question

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.

kuovonne · Accepted Answer

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

RdMedia_srl · Answer

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

with {QuantityRequired}*{Cost}

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded