List Subtraction within Action Loop

MTKPA · 10-02-2023 12:37 PM

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?

TeeSee1

My two cents...

Grouped actions can sometimes result in unexpected results because data changes are not immediately reflected in the evaluations of next iterations (i.e. one staff member's [AssingedCustomers] updates not seen by other staff members. I actually think selected rows are operated on in parallel and this is why they do not see changes applied to each other ). I suspect it is the case here but not 100% sure. If you manually execute your actions successfully and an 'automated' execution fails, then it is most likely the cause. Fixing this can be complex or impossible and you may need to break up your grouped actions and ask your admin to execute a few additional clicks.

Another approach, although you might already be fed up with tweaking the app, is as follows.

Instead of dynamically assigning customers through action loops, you sort of pre-determine which customers are assigned to which staff member by introducing a few VCs (VCs may be incorporated in expressions to be computed dynamically but having VSs was easier to think through and show my logic).

Here is what might work for you. (not sure if this meets all of your requirements but just to give you an idea)

Assign Count: Calculated based on your List Share
Assigned Count: This is the biggest trick here. This calculates the total number of customers "already" assigned to the staff members listed before this member in the Staff table.
```
SUM(
 SELECT(staff[Assign Count], [_RowNumber] < [_THISROW].[_RowNumber])
)
```
Assign List: Just extracts a list of customers to be assigned to this member
```
TOP(customers[id] - TOP(customers[id], [Assigned Count]),[Assign Count])
```

Now in addition you obviously need to do a few more things such as

Create an action to move the last VC to a persistent column
Take into account the Services info (more VCs I guess, perhaps based on a slice for Service staff..)
Filling the assigned staff back to the Customers table (should be straight forward)
SELECTION criteria based on availability etc.

Well, that's it. Asking your admin to click a few more buttons is probably a lot easier than implementing this new approach if it even made sense to you.

MTKPA

Thank you for your detailed response! I should have mentioned that I did get this to work (enough to tweak, anyway) on my first iteration. It assigned a MinIndex and MaxIndex to each staff, but took a couple VCs to get there. Then a helper table split up the customers, assigned staff, then wrote back into the original table.

The issue is that instead of syncing for every staff member, it synced for every customer, 3 times. So 150 customers turned into an hour long sync watching it count down from 450 rather slowly. That's more time than between when the Admins arrive to work and when they need this done. So I scrapped the whole thing and moved onto setting it up to run the operation from the staff perspective.

Your plan sounds like an "in between" option. I'll see how it feels as I start working on it.

A follow-up question for you, since you were kind enough to help. I'm *okay* with the admin clicking a few more times. The issue is what and when to click. I don't want to introduce user-error possibilities. Is there a way to populate the actions based on available staff? I'm thinking I could implement a "lock staff" button to perform the pre-assignment actions (clearing assignments, calculating shares, etc.).

What if I created an "Staff Action" table that deleted all rows when "lock staff" is clicked, then created a row for each available staff. Then have an action tied to each that Shows If = Staff[Available?]. I feel like I'd need to go in an edit the actions every time there's a staff change.

My other idea was to have 8-10 blank tables named in number order, then just use an Index in staff to reference them to the respective table. Then write a single row in "their" table if they're available with their name in one column, and an enum to house assignments. Then reference that list back in the Staff table. That way the actions are working through each table rather than rows--and it would likely update the customer list assignments before moving onto the next table. It's more sync time, but only marginally compared to the customer count. My concern here is that I'd need some kind of INDIRECT() function to reference specific tables. I guess I could run the actions through all tables, but now we're getting into dozens of actions.

If it weren't for sync times, I'd just query it back and forth.

Thanks again for your help! I'll keep you posted with where I land.

TeeSee1

After thinking more about this, I have found it a LOT more complex than I first anticipated.

Pre-assignment may not work well when handling a mixed list of customers with different service orders and looping may be required.

Definitely more thinking required here...

MTKPA

The issue is that I can think of a dozen ways to accomplish this, but every single one involves either a loop that has to work sequentially for each customer row (which doesn't happen); or a helper table with added clicks in between and syncing would take ages. Or both.

I'm at a loss. I crashed the app now and it's locked in "Duplicate sync" errors. I'll walk away for a bit.

MTKPA

TeeSee,

I had a thought that maybe I should talk to the Admins about a different method for this, entirely. Even if I get it to work, it's going to be messy and I won't ever trust it to run smoothly.

I asked why we couldn't just leave customers "Unassigned" and have staff pick them off of the list one by one. They can "Assign" themselves to as many or few as they want--and then I'd just have running tallies and countdowns in the Admin panel to keep track. Honor system, but not really.

The concern the Admins have is that not all customers are created equal. They don't want someone cherry-picking the "easy" ones to appear to be doing more than the others. Hence, the randomly assigned lists. But at this point, I'd rather show the Admins how to filter the customer list by whatever values they want and assign a bunch to someone via quick edits.

The only feature I'd like to add with this is a "Next Customer" button on the main dashboard to bring up a detail view of the next customer on the top of the list depending on the staff member's "Services?" value. And when that happens, they're automatically assigned. Grabbing a single top customer from a filtered list and assigning should be an easy action to accomplish. I'm already most of the way there.

Thoughts?

TeeSee1

Simple solutions are the best!

TeeSee1

I have pursued the strategy of pre-assignment to see how far I could go and here is what I got. @MTKPA , you seem to have a lot of tricks up your sleeve, so I just stuck with it.

A few things to note:

If service order values are varied (i.e. 1,3,5) then, I could not get it to distribute 'fairly' among the staff members with Service status. Customers are sequentially assigned to the members with Service status in blocks of pre-calculated numbers.
I introduced a status called Temporary Service to upgrade non Service members when the existing members with permanent Service status cannot handle the workload. You can however assign additional high service order customers beyond their original List Share range.
Though member assignment is almost instant, it does take time for the customer records to be written back to the backend storage.
This strategy felt as if working on a spreadsheet with a bunch of cells with a formula. Kind of hard to keep track of what is going on. Certainly does not feel very flexible if you want to enhance it.

Well, this has certainly been a learning experience for me. No further pursuit of this strategy here.

MTKPA

Wow, thank you!

I created an "Admin Assignments" dashboard with two side by side lists. One is a slice of the Customers table with info they'd want to see when assigning (Services, in particular). It has the same +/- icons and is used to populate the "Assignment List" table--which is actually just a slice of the Customers table that has a "Yes" for [AssignList?] that is triggered by the "+" ("-" changes to "N").

Then I created a new table with one row, and one editable columns: Staff. Staff is an Enum/Ref that points to my AvailableStaff slice. I added a "Select Batch Assignment User" primary action for that table that links to a form (split view) that simply presents the Available Staff as options to edit that specific row (the only row). It's basically a button to bring up the Available Staff, and you pick one and submit to change the value of the new table's only field.

The action when submitting the form points to a grouped action that does a LOOKUP in the Customer table slice with all AssignList? = "Y" entries for each row and fills [Assigned Staff] with AssignmentTable[Staff] (the value updated by the form). Then it resets all [AssignList?] to "N". Then performs the other actions required when someone is assigned.

This is the cleanest way I could get it working well. My next step will be to auto-select PER STAFF based on the Service? and List Share values. I think I can set it up the same as above, but clicking a name will filter the Top based on List Share and change the value of Assigned Staff to that name, then clear. So you can at least do one staff at a time. I think that's similar to what you were doing above.

Thanks again!