Assignment of Account Manager on new record creation based on weight

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

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

auto assign

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:

Ha no worries. Glad I could help!

Thank you again. I really hope our IT support team can understand your build :slight_smile: