I would like to automatically populate the commission rate based on the profit margin. I have set this up in MS Excel and also Airtable.com, but not sure how to go about this with quickbase .
For example(see screenshot): In the attached example has a 20.46% profit margin and would pay a 2% commission.
Here is the commission scale:
Profit Margin 00.00% to 28.99% profit margin pays 2% commission rate
Profit Margin 29.00%30.99 pays a 2.5% commission rate
Profit Margin 31.0032.99% pays a 3.5% commission rate
Etc, etc.....
For example(see screenshot): In the attached example has a 20.46% profit margin and would pay a 2% commission.
Here is the commission scale:
Profit Margin 00.00% to 28.99% profit margin pays 2% commission rate
Profit Margin 29.00%30.99 pays a 2.5% commission rate
Profit Margin 31.0032.99% pays a 3.5% commission rate
Etc, etc.....
 124 Points
Posted 5 months ago
 872 Points
Commision rate needs to be a formula  numeric field with a formula similar to the one below.
if
(
[Profit Margin]<.29,.02
,[Profit Margin]>=.29 and [Profit Margin]<31,.025
,[Profit Margin]>=.31 and [Profit Margin]<33,.035
,etc.
,etc.
,etc.
)
QuickBaseCoach App Dev./Training, Champion
 62,478 Points
The proper way to do this is to have a table of the commission rates. You could have fields for the break points and also the commission rates. These can be entered in a table with a single record with a bunch of fields. The [Record ID#] of that single record will be 1.
Then make a formula numeric field on the details table with a formula of 1 and make a relationship to the Commission rates table using that reference field.
Then look up all those values down to the details table. I suggest a naming convention such as [Break Point 1 lookup], and [Commission Rate lookup]
The magic is the next step. Then you will make a snapshot https://help.quickbase.com/userassistance/setting_up_snapshot_fields.html
equivalent of each of those fields.
That way the commission rates will be locked in and frozen at the time the detail record is created and they will not change retroactively.
Then make a formula numeric field on the details table with a formula of 1 and make a relationship to the Commission rates table using that reference field.
Then look up all those values down to the details table. I suggest a naming convention such as [Break Point 1 lookup], and [Commission Rate lookup]
The magic is the next step. Then you will make a snapshot https://help.quickbase.com/userassistance/setting_up_snapshot_fields.html
equivalent of each of those fields.
That way the commission rates will be locked in and frozen at the time the detail record is created and they will not change retroactively.
 124 Points
Excellent!! Will give this a try. Thanks for pointing me in the right direction.
 124 Points
Took me a little bit to wrap my head around this, but finally got it. :) At first, I was trying to make my lookup field a snapshot field. I didn't realize that a snapshot field is an additional field.
Thank You!
Thank You!
(Edited)
QuickBaseCoach App Dev./Training, Champion
 62,478 Points
Good for you. This stuff is always easy in hindsight, but the very first time, you have to trip on your face a few times.
Related Categories

App builders
 880 Conversations
 44 Followers

Tables & fields
 7163 Conversations
 170 Followers

Ask about Quick Base functionality
 922 Conversations
 64 Followers
William Bedingfield
I've attached screenshot my airtable Commission rate table to help clarify what I'm asking.
Thank you for your help.