Setting up a sliding commission calculation
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...
Forum Discussion
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/user-assistance/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/user-assistance/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.