Hi Peter, I’ve put something together here that should do what you’re looking for with automations. To view the setup (formulas, automations etc), duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button
We can do this with a script too, but the downside is you’ll need to have someone maintain it in the future unless you know Javascript yourself
We first assign the weightages to each account manager, i.e. 37.5% to account manager 1, etc
After that, we calculate the percentage of records each account manager is linked to by dividing a count of the record numbers by the total number of records that exist. To get the total number of records that exist, we link all of them to a single record and use a Count field
Now we know what the percentage is for each account manager, we identify the account manager with the least number of records (Using the Lowest % (from All) field in the Account Managers table) whose actual percentage is lower than their weighted percentage (Using the % Actual lower than % Weightage field in the Account Managers table), and assign the record to that person with an automation.
There’s an edge case where the distribution of records is exactly right, i.e.
Account manager 1 has 37.5% of the records
Account manager 2 has 37.5% of the records
Account manager 3 has 25% of the records
To handle this, I’ve made the system automatically assign it to “Person 1” in this set up, and you’ll have to modify the field Assign? in the Account Managers table to deal with this yourself
Let me know if you have any questions!
Hi Peter, I’ve put something together here that should do what you’re looking for with automations. To view the setup (formulas, automations etc), duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button
We can do this with a script too, but the downside is you’ll need to have someone maintain it in the future unless you know Javascript yourself
We first assign the weightages to each account manager, i.e. 37.5% to account manager 1, etc
After that, we calculate the percentage of records each account manager is linked to by dividing a count of the record numbers by the total number of records that exist. To get the total number of records that exist, we link all of them to a single record and use a Count field
Now we know what the percentage is for each account manager, we identify the account manager with the least number of records (Using the Lowest % (from All) field in the Account Managers table) whose actual percentage is lower than their weighted percentage (Using the % Actual lower than % Weightage field in the Account Managers table), and assign the record to that person with an automation.
There’s an edge case where the distribution of records is exactly right, i.e.
Account manager 1 has 37.5% of the records
Account manager 2 has 37.5% of the records
Account manager 3 has 25% of the records
To handle this, I’ve made the system automatically assign it to “Person 1” in this set up, and you’ll have to modify the field Assign? in the Account Managers table to deal with this yourself
Let me know if you have any questions!
Wow Adam…well that was exceptionally quick I am a bit blown away by your quick and concise response. Also it seems you have in fact built the solution in our instance… I have handed this information over to our IT staff internally to see if they can test it as it is quite a bit over my head. I cant thank you enough as this has been a big struggle for us internally. I will report back with IT’s testing results… :exploding_head:
Wow Adam…well that was exceptionally quick I am a bit blown away by your quick and concise response. Also it seems you have in fact built the solution in our instance… I have handed this information over to our IT staff internally to see if they can test it as it is quite a bit over my head. I cant thank you enough as this has been a big struggle for us internally. I will report back with IT’s testing results… :exploding_head:
Ha no worries. Glad I could help!
Ha no worries. Glad I could help!
Thank you again. I really hope our IT support team can understand your build 
