Help

Fetching lookup field into a formula - is this possible?

Topic Labels: Formulas
Solved
Jump to Solution
1989 4
cancel
Showing results for 
Search instead for 
Did you mean: 
sgrass
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

4 Replies 4
RdMedia_srl
7 - App Architect
7 - App Architect

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}

 


Rodolfo - RdMedia

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
18 - Pluto
18 - Pluto

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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