Jun 22, 2022 09:37 PM
Hello All,
I really hope you can help me with an assignment rule that will run when a record is created in Airtable.
Some threads that come really close to fixing the issue are:
The only issue that these cannot do for us is we have 3 account managers we want to assign to newly created records but with a % rate attached to them instead of just one after the other like a round robin. What we are looking at is:
Account manager 1 will get 37.5% of all new records
Account manager 2 will get 37.5% of all new records
Account manager 3 will get 25% of all new records
My skillset certainly is not great with airtable so greatly appreciate your hand holding with how we can achieve this. Thank you all I really hope we can get this sorted.
Regards,
Peter Igoe
Jun 22, 2022 10:36 PM
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!
Jun 22, 2022 11:24 PM
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:
Jun 23, 2022 12:28 AM
Ha no worries. Glad I could help!
Jun 23, 2022 03:47 PM
Thank you again. I really hope our IT support team can understand your build :slightly_smiling_face: