Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Fetching lookup field into a formula - is this possible?

Topic Labels: Formulas
Solved
Jump to Solution
2248 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
sgrass
5 - Automation Enthusiast
5 - Automation Enthusiast

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