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
