I’m struggling to execute a “customer distribution” mechanism.
Two tables - customers (outside data) and staff (AppSheet database). At any given time I have 100-200 customers, and 2-6 staff. Customers are exported from our company data (has to be manual) and imported into AppSheet via an Admin dashboard and Import CSV action. This all works great.
There’s also an action (from my AdminActions database – one row with action titles, one column for each action to map into Admin dashboard) that calls together a group of actions to “Assign” customers to staff. This is where I’m struggling.
Each staff member has three parameters to be edited by the Admin before assignment of customers.
- Available? (Y/N. If not available, don’t assign).
- List Share (Enum List of whole numbers between 1 and 10). The Admin can assign a value to each staff member to create a List Share %. Meaning, if all available staff have the same value, then it doesn’t matter if it’s a 1 or a 10, they’re sharing customers evenly. The List Share is setup to give anywhere from an even split to a 10-to-1 ratio.
- Services? (Y/N). Some customers have a [Service Orders] value over 0. If a staff member has a Services?=TRUE value, they can handle these customers. If not, they can’t and won’t be assigned customers unless no available staff has a Services? value of TRUE.
The goal is for the Admin to set these parameters, upload the customer data, and click “Assign.” Then, each customer has a Staff member name attached to it. Sounds easy, right?
I have gotten as far as creating a network of actions that loop the assignments of Service customers, first–based on List Share % of ONLY those who can take Service customers. Then another set of actions to do this for the rest of the customers. I also have actions for the first person on each list (Service/Non-Service) to pick-up any customers dropped by rounding to make sure all customers are assigned regardless of List Share %.
Everything works except one thing–the actual assignments continue to happen from the top of the list even after going to the next staff member. Meaning, if there are 100 customers, 4 staff, and all have equal share and can take Services, then all staff are being assigned to the first 25 customers.
Here’s my current expression for the AssignServiceCustomers action in my Staff table:
[AssignedCustomers] +
TOP(
FILTER(
"Customer_Import",
AND(
[Assigned Staff] = "Unassigned",
[Service Orders] > 0,
NOT(IN([LinkID], [AssignedCustomers]))
)
) - [AssignedCustomers],
FLOOR(
COUNT(
FILTER(
"Customer_Import",
AND(
[Assigned Staff] = "Unassigned",
[Service Orders] > 0
)
)
) * ([List Share]*1)/SUM(SELECT(Staff[List Share], [Services?]=TRUE))
)
)
I have another action in my customer table–AssignStaff (works):
IF(
COUNT(
SELECT(
Staff[AssigneeFirst],
IN([_THISROW].[LinkID], SPLIT([AssignedCustomers], ","))
)
) > 0,
ANY(
SELECT(
Staff[AssigneeFirst],
IN([_THISROW].[LinkID], SPLIT([AssignedCustomers], ","))
)
),
[Assigned Staff]
)
A trigger action in Staff to call that action. Then an “AssignServiceCustomersTrigger” action in my AdminActions table to execute it on ref rows = SELECT(Staff[AssigneeFirst], Services?]); and an “AssignStaffTrigger” action to call that for ref rows =Customers[LinkID]. Then an action to group them together.
Again, when I run it, it assigns from the top of the filtered list for each staff without removing. I’ve tried counter VCs, I’ve tried triggering the AssignStaff in the Staff table, then grouping in Staff so the AssignStaff action happened after each row assignment (with a different expression for AssignServiceCustomers to only take the top). I’ve tried creating a VC for each staff member to house a list of all assigned customers just to see if I could get it to work somehow (it didn’t). I’m at a loss.
I’m really trying to avoid writing a brand new row to a helper table for every customer assigned. I feel like I’m already pushing beyond what I wanted to do originally: only have to sync as many rows as there are staff, and then the Customer sheet just references the staff associated. Quick sync, no mess.
Help?
