RoxanneZiegler
4 months ago

# Commissions table

Our business can be complicated. as of now everything is done manually.

There are multiple commissions based on about 7 different things.

Location, Manager, Supervisor, sales person plus what type of products which are 4.

We have 12 separate Locations with each different Manager, Supervisor and sales person.  Oh and they each have a different %  and then on top of it all those % sometimes don't stay static. The owner comes in and makes changes once in awhile.

Would I build a table that a drop down menu with the Locations in - once picked then the manager, supervisor and sales person would show ( using dynamic form rules)  then a numbers field where the person can add the % and then a formula field to calculate?

I really don't know where to begin - thanks for any input

• QuickBase formula are quite robust and are capable of taking quite an abuse :D

You can use an excel to list down all the conditions and build 1 single giant formula. Your current idea of using dropdown in 1 single field should work fine.

It will goes like

If (
[Location] = "X" and  [Supervisor] = "Mr. A" and ["Manager"] = "Uno" and [Product] = "12", 1.24%*[Sales Commission],
[Location] = "X" and  [Supervisor] = "Mr. B" and ["Manager"] = "Uno" and [Product] = "12", 1.22%*[Sales Commission],
[Location] = "X" and  [Supervisor] = "Mr. C" and ["Manager"] = "Uno" and [Product] = "12", 1.04%*[Sales Commission])

You can continue to build like above in 1 formula with 50+ combos easily , it's easy to manage and you can make changes .

--

Another way would be list down all the combinations in a child table and then doing a parent child relationship to manually select the right combination. If there is a business case to have history of previous commissions or changes , this would be a right approach .

• Thatms excellent - I think the child table would be the right way to go since we do have lots of changes. How would one star?

Create table > Create relationship and in the child table use a drop-down for all the data so that the person using it would be able to pick each scenario.

• A solution with commissions set in Table is definitely the way to go. Aside from providing transparency into how the commissions are set, it means that a non-admin can adjust the commissions and also most importantly you will want to bring down the commission rates into your sales detail file as look up fields and then create a snapshot field for each of the look up field so they are frozen when the sales detail is created. That way when the commission rates changed won't be changing the historical commission payments.

I will try to describe the gist of the solution here, but if you need more help, we can do it slowly hear back-and-forth or you may want to book me for two hours of one-on-one consulting time to get this working.

You will set up a table of commissions where the key field is a concatenation of the various factors that you want to pay commission on, delimited by say a colon : of the location and the products and depending on your set up, maybe the manager supervisor and the sales person. It wasn't clear to me from your post if those commission rates are fixed for each position.

The Key field of the commissions would be populated by a form rule which would take the value of the concatenated formula field and populated into a scalar data entry field.

So the result might be Atlanta:Widgets

And if that location has a fixed commission for each of the three positions, then you would have three fields for the commission rates.

Then you would build a corresponding formula field on the sales detail table, which would calculate that same; concatenated string and then look up the commission rates and then you would have three Snapshot fields to freeze the value of those look up fields at the time the sales detail got created. Like I said above, this is important because when the commission exchange you don't want to change history.

So basically, you would continue creating different commission records for different types of situations and the lookout fields and snapshot them.

Then on the item detail table, you would need to have a formula field to decide which commission rate to use as there could be multiple commission  rates that might apply. might take the highest score the lowest or perhaps some kind of hierarchy to take the first one that is not blank.

Feel free to post back with questions and I won't answer the Best Buy or else if you want some more assistance, you can contact me directly at mark.shnier@gmail.com

• Thank you for your response. I would love to hire you but the owner won't give me a budget. btw I am not a builder by any means. How I have managed over 13 yrs is through tech support at QB. I have managed to put together 20 tables with relationships by reading and tech support lol so having said that I don't understand exactly what you said. I can approach the boss again if you would provide a rate and how many hours it would take to do this, otherwise I will try to figure it out a little at a time.