Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Fetching lookup field into a formula - is this...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

1
1799
1

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 03:19 AM

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.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 08:06 AM - edited Dec 21, 2023 08:07 AM

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.

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 07:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 07:54 AM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 08:06 AM - edited Dec 21, 2023 08:07 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2023 08:12 AM

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