Formula help with multi-product commission dependencies

[Zak]

Hello,

I wonder if someone could perhaps help me with a formula I’m stuck on.

I have a single select field with a number of products: Product 1; Product 2; Product 3; Product 4 etc.

I also have a Premium (currency) field and a Commission (percentage) field.

I’m looking at adding a Commission (currency) field with a formula that shows me the amount.

However, some products are subject to tax at 12%, whereas others are not, and i want to get the commission amount after the tax has been deducted.

How can I create this?

e.g. IF Product = product 1, product 2, product 3, then (Premium * 0.88) * Commission, IF Product = Product 4, product 5, product 6, then Premium * Commission

Thanks in advance

[augmented]

Hi Zak. My recommendation would be to create a Products table that holds the product id/name/etc and its tax treatment. Then switch out your single select for a link field to Products. Then you can add a lookup field on Products to pull in the tax rate for that product into the table where you are calculating commission.

It will give you easier extensibility in the future. If you need more explanation, let me know.

[Tim_Wilson]

One thing to keep in mind is how you will handle things when the tax rate invariably changes. You probably don’t want your historical records to change when that happens.

[Zak]

Thank you very much for your reply Augmented.

The only potential issue with this method is that there are only 6 products in the stack, and with 15 tables running in the base, is it worth a new table for those few data?

Albeit, thinking out loud, those products do span across a few tables so I guess I could link to the Products table whenever they are being used in another table as opposed to a single select/multi-select.

[Zak]

Thanks for your reply Tim_Wilson.

I have a ‘sales ledger’ table along with a ‘quotes’ table and decided on inputting commission percentages manually as they can change fairly regularly.

All 6 Products are grouped into 2 categories, one subject to tax and the other not. Maybe I’m simply better having a Product Type 1 Commission Amount field, and a Product Type 2 Commission Amount field along with a Total Commission Amount field to get around the issue.

Having views for each Product Type allows me to hide the redundant Commission Amount field for each type.