Split accounts evenly

[wattmhite]

Good Afternoon,

I have a table with 8 users in the table and a separate main table with all of my customer files.

Currently each file is assigned to one of 4 users (I recently hire 4 new agents)

I am looking to create a script that 1. Finds out how many current users there are. 2. Then takes the number of users 8, divided by total customer files (say 800) and evenly assigns the files to each agent. IE it balances out each agents workload to make sure each user has 100 accounts.

Is there a function built in to do this in Filemaker?

 

Thank you all in advance,

[comment]

Do you have a value list of user IDs? If so, define a variable $userIDs as =

ValueListItems ( "" ; "YourValueListName" )

Next, go to the files table, show all records and replace the contents of the field you want to use as the foreign key to the users table with a calculated result =

Let ( [
n = ValueCount ( $userIDs ) ;
i = Mod ( Get ( RecordNumber ) - 1  ; n ) + 1
] ;
GetValue ( $userIDs ; i )
)

 

[LaRetta]

Hi wattmhite, 

What do you do in case the number doesn't divide equally?  It does now but might not in future; in fact, your first new customer could go to your newest rep if your UserID is UUID (since value list would sort alpha).  Your script might instead sort your Users in order of seniority (hire date?) then set your variable with that list of UserIDs so those at the top get any resulting (odd) remainder. 

 Nice calc, Comment! 😁

[comment]

1 hour ago, LaRetta said:

Your script might instead sort your Users in order of seniority

I suppose the order of seniority would be the same as order of creation - so you could populate the $userIDs variable using =

ExecuteSQL ( "SELECT UserID FROM Users" ; "" ; "" )

 

[LaRetta]

Or if you already have a summary field in Users table, which is 'List Of' User IDs, just include Users which should be assigned (only from Sales Department) in your found set or include WHERE in Michael's eSQL() calc to further fine-tune the restriction, such as skipping Admin staff.