Turn on suggestions

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

Showing results for

- 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

0
1545
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