How to: Multiple Prices for a product

My first inclination is to use a [Products] table, with fields to accommodate the various prices you want to use. In another table you would link to the product, and use something like a {Price Type} Single Select field to specify what type of price should be applied. Lookup fields (hidden) would bring in all prices for the selected product from the [Products] table, and a formula field would apply the correct price based on the Single Select choice.

My first inclination is to use a [Products] table, with fields to accommodate the various prices you want to use. In another table you would link to the product, and use something like a {Price Type} Single Select field to specify what type of price should be applied. Lookup fields (hidden) would bring in all prices for the selected product from the [Products] table, and a formula field would apply the correct price based on the Single Select choice.

I have my product table made with another table for the Sales Orders… how do I “hide” the Lookup fields?

Right-click (or click the downward arrow on the right end of) the header of a field to bring up its contextual menu, and choose “Hide Field” near the bottom. You could also use the Hidden Fields tool in the table toolbar (between the view name and the Filter tool)

My first inclination is to use a [Products] table, with fields to accommodate the various prices you want to use. In another table you would link to the product, and use something like a {Price Type} Single Select field to specify what type of price should be applied. Lookup fields (hidden) would bring in all prices for the selected product from the [Products] table, and a formula field would apply the correct price based on the Single Select choice.

Can’t seem to make that work… )’:

Can you be more specific? Describe the exact steps you’re following, and the exact results you get when doing so.

Is there a way you can show me through a link?

I’m tied up today, but will try to walk through the steps for this tomorrow (unless someone else beats me to it)

You’re too kind :grinning_face_with_big_eyes: Thanks a bunch!

I don’t have time to build a screenshot demo, but here’s the best verbal rundown I can offer in the meantime

Long story short: you don’t put a formula in a Single Select field. You add a formula field that reads the value in the Single Select field, and outputs the values from one of the Lookup fields based on what’s in the Single Select.

Slightly more detailed: your Single Select field contains text representing the type of price you want to apply for a given product in a given order; i.e. Retail, Wholesale 1, Wholesale 2, etc. I’ll call this field {Price Type}.

You have a {Product} link field where you link to a product from your [Products] table.

You then have lookup fields that pull in those different prices based on what product is linked; i.e. you’ll have a lookup field named {Retail} that looks up the retail price for the linked product, another one named {Wholesale 1} to pull in that price, etc. Once created, all of these fields would be hidden, and only referenced via the formula.

In your formula field (named something like {Unit Price}), you have this formula:

SWITCH(
    {Price Type},
    "Retail", Retail,
    "Wholesale 1", {Wholesale 1},
    "Wholesale 2", {Wholesale 2}
)

If you have more than three options, this would obviously be a larger formula. But in the end, your choice in the {Price Type} field would determine the correct unit price applied, which could then be multiplied by the desired quantity to get the total price for that selected product.

Does that make sense?