Sorting Children in to Groups

[alphacp]

I am having the most curious of days with nothing quite working as I expect it to. Rather than detailing the issues I am facing, I thought I would instead pose the problem statement and my approach I am trying to solve in case someone has a better solution.

What I have is a list of Children attending an event. What I am trying to do is to dynamically allocate these children into groups. The number of groups will grow as the number of children increases. But there is a twist. Whilst generally I want to allocate by age, sometimes there are siblings who must stick together so I have an override to specify a group where this is required. 

My app has a table for the Events but there is only one row in that table and only ever will be. The Key column value therefore is well know in the app.  The event defined the maximum group size - let's say 12 for this discussion.

I also have a list of People, the children. I have their age (in months) and whether they have a defined group assignment or not. Lets assume I have 64 children. I therefore will have 6 groups. 2 siblings are going to be in "Group 4"..

So here is what I have done.

1) I created a List column in the Event table that contains all the children in age order, excluding those already assigned to groups

2) I created a new table that is simply a fixed list of groups with slots. E.g., Group01-Slot01, Group01-Slot02...... all the way to Group10-Slot24. I have a VC to indicate if the slot is available. If the group size is 12, then slots 13 to 24 are not available for example. I also reduce slots in accordance to assignments (i.e., the 2 siblings)

3) I created a slice of the slot table filtered to only include slots that can be filled. Let's call this "Available Slots"

4) Now this is where it gets a little crazy. I add another VC to the my table of slots which shows which position in the Available slots slice, the slot is. Each slot therefore, if usable, has a sequential number from 1 to 70 (6 groups of 12 minus the 2 fixed items) - Some slots have no sequence number as they will not be filled

The theory is I can then work out what position a child is in the List created in step 1 and lookup that position up in my slot table and that will return which Group to assign the child.  This is not actually working for me but I cannot figure out why. Group 1 is fine but after that I have problems. But let's not diagnose that here. 

So is there a way I can simplify all this? 

[Joseph_Seddik · Accepted solution]

One of my apps had a similar requirement albeit for something different. Here's how I'd solve your problem based on what I've done.

1. Table Groups. Each group should have:
   1. A fixed number of Total Slots.
   2. Number of Available slots. Initially set to Total and decreases with every slot assignment or Reservation.
   3. Number of Reserved slots, to use for siblings. Initially set to 0 and increments decreasing the number of Available slots. 
      
      
2. Table Children.
   1. Each child should have a "Sibling Of" column, that can either contain one Child Name or empty. Set the column type to Ref that points the same Children table. This will allow you to easily select the brother through a form.
   2. Only one child of the siblings (the eldest or youngest, based on how you order them) will be used as the index for his/her brothers/sisters. This child will have his "Sibling Of" column empty, while his/her siblings will have their "Sibling Of" column set to this child. 
   3. Also, you should have a column "Assigned Group" as a ref to the Groups Table. 
   4. Add a virtual column named "Siblings", type: List, element type: Ref, pointing the Children table with this formula: 
      SELECT(Children[childID], [Sibling Of] = [_THISROW].[childID])

After filling in all the children, you have to create an action that will operate on all rows of the Children Table, sorted by age. Here's how it will operate. 

1. Check if the Child does not have an assigned group: ISBLANK([Assigned Group]). 
2. Ensure that the child does not have a big brother: ISBLANK([Sibling OF]). In this case, do:
   1. Set the "Assigned Group" column value according to the following formula: 
      INDEX( SORT(
         SELECT( Groups[GroupID], 
         [Available Slots] >= COUNT([_THISROW].[Siblings]) + 1)
      ), 1)
      This formula identifies the first group in the sorted group list that would accommodate for this Child and his siblings. 
   2. Decrement "Available Slots" by: COUNT([Siblings] + 1
   3. Increment "Reserved Slots" by: COUNT([Siblings])
      
      
3. Otherwise, if the child has a big brother, then: 
   1. Set "Assigned Group" to: [Sibling Of].[groupID]
   2. Decrement "Reserved Slots" by 1

That's all. Two tables and one workflow and you'll have all your kids seated. 

[alphacp]

Thank you. I will give this a go

[Joseph_Seddik]

By the way, may be you have already concluded it, but just in case. You don’t need an Event table and you don’t need the Slots. Your Groups Table will contain as many rows as there are tables. Group size will be set simply by a number field, not by adding new rows to the table. 

[alphacp]

I selected this as the solution as it works well in the context intended. I was trying to get something more dynamic but not sure that's possible (I had a number of timing issues with slices and VCs) so I am still working on a variation of the solution here that can happen on demand. 

[Joseph_Seddik]

Thank you. Will be glad to help if needed. 

[alphacp]

I did actually solve this in the end so I will try to explain how but I suspect it only really works for smaller numbers. I have about 8 groups of 12 children and I do not see any performance issues.

1) I created a table called 'Groups' That only had two real columns, a key and 'GroupID', and I added 12 rows to it - Group 1, Group 2, etc
2) As suggested I added a column to my 'Person' table called 'Group Override'. This is an Enum with no fixed values but allows other values. The suggested values is set to

LIST("Automatic") + TOP(Groups[GroupID],NUMBER(-the max number of groups I want to allow-))).

I set the inistal value to "Automatic". I am nesting two lists together but the actual Group options is trimmed accordingly.
3) As I add a child to the Person table, I can leave the Group Override as Automatic or set it as I wish.
4) In the Groups table, I added a VC called 'Allocated' the counts how many Persons have that specifc GroupID selected.

COUNT(SELECT(Person[Key],([Group Override]=[_THISROW].[GroupID])))

5) I also added a VC called 'GroupString' that was a List. This list was the GroupID repeated until I reached the maximum Group Size minus the allocated spaces....

TOP(LIST([GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID],[GroupID]),(-the max group size-)-[Allocated]))

6) In my main 'Event' table (the parent table from which I am getting my maximums from, I created a VC that listed all the children for which the Group Override is still set as "Automatic" and I order this list by their age. I called this VC "ChildrenToPlace"
7) I also created a VC called "Spaces" in that Events table that merged all the GroupStrings together in to one long list. For example, if Group 1 has two spaces and Group 2 has three, the Spaces list read ("Group 1", "Group 1", "Group 2", "Group 2", "Group 2")
😎Then, using a nifty expression that @MultiTech_visions posted, I wourked out what position each child was in the "ChildrenToPlace" list, assuming they were in the list and not already allocated manually.
9) The final step was to read from my Spaces list using INDEX and the Position in list from the step above. So if the position in the list was 4, the fourth item in my spaces List is "Group 2"

I should really add some error handling to ensure that I cannot manually allocate too many people to one group but for now I have a totally dynamic allocation.

Thanks for the inspiration!