The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# Extracting information from Table and performing equations

352 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hello All,

I'm somewhat stuck when it comes to transitioning to Airtable, so any help would be appreciated.

I have a Table, called {Pricing} and another called {Quote}.

My intention is to extract multiple entities from {Pricing}, perform conditional formulas and return a final price in {Quote}. The values in {Pricing} will never change, however, the requirements for what will be extracted from it will have to change.

I have created the above in Excel, where I was relying on the FILTER formula, and VLOOKUP, I'm just looking to perform a similar task in Airtable.

For more context, my products are Gates. And in {Pricing} I have the gate listed in a Matrix, height increments of 250mm and Width Increments of 250mm, with a price in each field. I'll try my best to illustrate.

Model&Height    - 1m          - 1.25m     - 1.5m      - 1.75m     - 2m     - 2.25m     - etc
MODEL1-1m         - £1000   - £1250    - £1500   - etc
MODEL1-1.25m    - £2000  - £2250   - £2500   - etc
MODEL1-1.5m      - £3000  - £3250   - £3500   - etc
MODEL1-1.75m
MODEL1-2m
MODEL1-Extras    - £150     -£200     -£250     - etc
MODEL1-Handles - £50      -£100     -£200     - etc

And then the entire process repeats for MODEL2, MODEL3, MODEL4, etc

In {Quote}, I have several Single Select fields, like:

MODEL: (MODEL, MODEL2, MODEL3, etc)
HEIGHT: (1m, 1.25m, 1.5m, 1.75m, 2m)
WIDTH: (1m, 1.25m, etc)
EXTRAS: (With, Without)
HANDLES: (With, Without)

What I want is a formula or VLOOKUP to look into the {Pricing} table and pick the coresponding model and with a formula add up the prices together.

An example:
MODEL:      MODEL1
HEIGHT:      1.5m
WIDTH:        1.5m
EXTRAS:      With
HANDLES:   With

Would result in a formula similar to: £3500 + £250 + £200 and return the total price of: £3950.

I know how to do it for just the MODEL, however, when I try to link with 2 or more records, the values stack and I'm at a loss on how to manipulate it.

If I need to create 2-3 additional tables for this process, I wouldn't mind at all, however, we have 250 models, each with up to 10 different unique options on top, so it would end up creating a significant amount of entries in {Pricing} that I don't want disturbed.

Additionally, we produce on average 300-400 quotes a month and we expect that to double in the next couple of months (hence the transition to airtable), so I would like the process to be automatic once the Form in {Quote} has been filled in order to streamline the process.

Do I create custom automations?

Any help or advice regarding this would be greatly appreciated.

I can share images if required.

Thank you

0 Replies 0