Can I change the order of the rows in my source table (Google Sheets)

[Laurens_Swart]

One of the functions of my app is to keep a list of people in a certain order (to determine who’s turn it is to do a certain shift).
When someone has done the shift, they need to move down to the bottom of the list.
So basically, have a list of names, and a number behind their name from 1 to 25 (there are 25 people). I need something like an option to move people in this list (and make it save in the database). It can be a button that moves the top person to the bottom, or a drag/drop kind of thing if that’s at all possible in the app. Does anyone have any suggestions on:

a) how to save the order of people in the database (I thought of using a column with the “number” 1-25, but if there’s a way to re-order the table rows, I could also use _RowNumber)
b) make it possible for app users to move rows around (at least it should be possible to move the top row to the bottom, making number 1 -> 25, 25 -> 24, 24 -> 23, … and number 2 -> 1) .

Any help would be greatly appreciated!

[MultiTech]

This is actually a little more complicated than you would first think.

You could go your route and create a column to track the order, then create a series of actions that modify the numbers.

But you run into the problem of actions only modifying one record at a time.

For example: Let’s say you create the column to store the order, and everyone starts with their appropriate place in order 1 through 25. If we create an action to change the number to 25 (thus putting them at the bottom) what’s really happening here is we’re changing a number to 25 - so now we have two people with the number 25, and everyone else’s number hasn’t changed.

Sure, we could create a set of background updating actions that watches for changes in numbers, then re-assigns everyone in the list their new appropriate number. But this means every time you press the button to move someone down, you’ve now got 25 edits that your app has to push to the cloud.

---

Methods like this work (I’ve done it several times before), but they’re cumbersome and full of holes.

Allow me to suggest another method:

Create a column that tracks how many times someone has completed whatever task would move them to the bottom of the list. Then, you only need one action (to add one to the counter for that person) and you can sort your list by the count number then by rownumber (provided the records are in the correct order in the sheet).

For Example: Let’s say everyone starts at 0 (or blank) for their count; if we order them by count then by rownumber, we’ll end up with all the 0’s at the top then ordered like in the sheet.

After the list has cycled through a few times (let’s say you’ve completed the 4th and you’re working on the 5th cycle), you would have all the 4’s at the top and the 5’s at the bottom - and in each of those groups the people would be sorted according to their rownumber.

This should accomplish what you’re wanting but in a simpler manner. Lemme know if any of this didn’t make sense. (^_^)

[Laurens_Swart]

Thank you so much for your suggestion. I think it might work, the only question is, what happens if I add a person to the list (i.e. a 26th person). Their counter would start on 0, which means -in your scenario after 4-5 cycles- for the next 5 shifts, they would remain on top of the list, until they have done the same amount of shifts as people who may have been working at my company for a couple of years. Do you understand what I’m getting at?
So if a new employee joins the company, their counter would have to start at the highest number in the table, so that they get added ‘to the bottom of the list’
Any suggestions on this?
Thanks again for your help.

Edit: I’m now looking at the option of a hidden Date field, sort the people by the date of their last shift (ascending), therefore, people who most recently did a shift automatically get put on the bottom of the list. When adding new people, I just put the date of their last shift as the date they were added to the list (so that new people get added on the bottom). All I would have to do is make a button that sets the ‘Date of last shift’ to TODAY when I click my ‘Move to bottom’ button. I’ll let you know how it goes.

[MultiTech]

Laurens_Swart:

So if a new employee joins the company, their counter would have to start at the highest number in the table, so that they get added ‘to the bottom of the list’

try making an initial value formula like this:

max(Employee_Table[Count])

If you need to apply a condition, so you can exclude some records (say only include employees on a specific crew), you could try this:

max(select(Employee_Table[Count], [Crew] = [_thisrow].[Crew]))