Disclaimer: I’m moderately new to Airtables.
I am working on a base that tracks commission earned for our sales department. Essentially there are two separate commissions rates that are earned: a pre-threshold rate and a post-threshold rate, where the threshold is an amount of commission earned (for example, the threshold could be $100,000, and the salesperson earns at a commission rate of 5% before they reach the threshold and at a rate of 3% after the threshold). I’m trying to write a formula that determines which commission rate to use based on the amount of total commission earned, but I keep getting a circular reference error, which I believe is caused by the fact that the total commission depends on which interest rate is used, causing the loop since both of these fields are dependent on each other. Does anyone have any advice on how to fix this? Sorry if the description is too vague, I can provide more information if need be. Thanks!
Need help removing circular reference
Best answer by kuovonne
Airtable does not like circular references. You must create a gap in the circular logic, and then bridge that gap either manually or with an automation. For example, manually or with an automation copy the result of a formula into an editable field.
Note that if you go the automation route, getting the trigger right can be tricky as every keystroke is considered an update. Also, due to the nature of the updates, you could have a cascade of automations running, which can quickly eat through your automation runs.
+27I may have misunderstood your setup, so here’s the names of fields I assume you have to help contextualize my answer to your question:
- {Amount Sold} = the amount the salesperson sold
- {Threshold} = the amount from {Amount Sold} before the higher rate kicks in
- {Pre-threshold Rate} = commission rate for less than or equal to the {Threshold}
- {Post-threshold Rate} = commission rate for amounts greater than the {Threshold}
- {Commission} = the total calculated commission earned
^ With those field assumptions in mind, {Commission} should be a Formula like so:
(({Amount Sold} - {Threshold}) * {Post-threshold Rate}) +
(MIN({Amount Sold}, {Threshold}) * {Pre-threshold Rate})
I may have misunderstood your setup, so here’s the names of fields I assume you have to help contextualize my answer to your question:
- {Amount Sold} = the amount the salesperson sold
- {Threshold} = the amount from {Amount Sold} before the higher rate kicks in
- {Pre-threshold Rate} = commission rate for less than or equal to the {Threshold}
- {Post-threshold Rate} = commission rate for amounts greater than the {Threshold}
- {Commission} = the total calculated commission earned
^ With those field assumptions in mind, {Commission} should be a Formula like so:
(({Amount Sold} - {Threshold}) * {Post-threshold Rate}) +
(MIN({Amount Sold}, {Threshold}) * {Pre-threshold Rate})
First off, thanks for your help! The formula you provided works when the amount sold is greater than the threshold. However, this is not always the case, as instances arise where the amount sold is less than the threshold, since the threshold is more of a yearly cap (i.e. $75,000) which is usually only met towards the end of the year. When the amount sold doesn’t reach the threshold, the formula doesn’t do what I want it to do. I tried to add an “If-else” statement to fix this, but it resulted in a circular reference error, since Amount Sold was being used as the logical and in the return conditions. Any other advice? Sorry I didn’t specify in my initial post, since this information was crucial to solving the problem.
+29Airtable does not like circular references. You must create a gap in the circular logic, and then bridge that gap either manually or with an automation. For example, manually or with an automation copy the result of a formula into an editable field.
Note that if you go the automation route, getting the trigger right can be tricky as every keystroke is considered an update. Also, due to the nature of the updates, you could have a cascade of automations running, which can quickly eat through your automation runs.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.