I’m thinking you would have a relation for model, where you pull back the price for model, and a relation for options, where you pull back the price for options. Then you add them together in a math column.
Oh you’re right. Then what I would do is have a structure like this.
Model |Options | Price
Super Lift | (Empty) | $100,000
Super Lift | Boom | $100,200
Super Tower | (Empty) | $50,000
Super Tower | Boom | $50,300
Super Tower | Gated | $50,050
Super Tower | Foot Brake | $50,450
Then join the model and options together in a template column. Let’s say it would be:
Super Lift & (Empty)
Super Lift & Boom…
Join the user’s model choice and options together in another template column, then make a relation between the two templates column and bring back the total price.
You would still have to create two relations if you want to keep the current structure, I believe you’re having 2 sheets.
First relation points the user’s model choice back to the Models sheet, returns the price of the model.
Second relation uses the template as described above to the Options sheet with another template column, also described above, to return the price of the option.
I need to do the process on the first sheet. ( Model and Price for Model)
The problem is , I make the model relation and option relation but how can I make a lookup as two condition (model and options) is filtered.
Why can’t you use the template method? It’s only a couple of columns and it automatically populates with data. It doesn’t matter how many rows of data you have. Your second option relation will use the template columns.
The issue, as I see it, is that your options are unique to each Model.
If the Options were the same price you could do it with a lookup to an options table, however with each option being a unique $value you are essentially creating individual products.
You can still filter the product choices and the options, but that is done one the design side within the choice dropdown (or however you decide to access each product).