Get next record from list, loop

[Connor_Mooney]

Hi all,

In one of my apps, I need a method of automatically assigning the a task to the next user in a queue. The app includes:

• Records table: houses all of the data for a record being processed thru multiple steps.
• Users table: each user has different responsibilities as columns, like 'CanDoTaskA', 'CanDoTaskB', etc.
• History table: records each action, who was assigned to handle TaskA, TaskB, etc.

To get the next user to be assigned to a specific task based on their permissions, I think I need the following:

1. Get a list of the users from the Users table with a specific responsibility (ie. 'CanDoTaskA'=TRUE).
2. Get the last user from the History table who was assigned to perform TaskA for any record.
3. Get the next user in the list after that last assigned user (and if the end of the list is reached, loop back to the beginning to get the next assigned user).

Part 1 and 2 are simple enough, but part 3 is where I'm stuck. Any thoughts on how to best handle this?

[Suvrutt_Gurjar · Accepted solution]

You could get the next user by the expression 

INDEX({LIST OF USERS THAT CAN DO A SPECIFIC TASK},
COUNT(
  SPLIT(
    ANY(
      SPLIT(
        (" , " & {LIST OF USERS THAT CAN DO A SPECIFIC TASK} & " , "),
        (" , " & [LAST USER FROM HISTORY TABLE] & " , ")
      )
    ),
    " , "
  )
), 1
)

You have mentioned that you already managed to get the {LIST OF USERS THAT CAN DO A SPECIFIC TASK} and [LAST USER FROM HISTORY TABLE]

Please insert those list and last user expressions in the suggested places and you should get the next user.

The solution is based on the principle that the part of the suggested expression , that is 

COUNT(
  SPLIT(
    ANY(
      SPLIT(
        (" , " & {LIST OF USERS THAT CAN DO A SPECIFIC TASK} & " , "),
        (" , " & [LAST USER FROM HISTORY TABLE] & " , ")
      )
    ),
    " , "
  )
)

 finds the INDEX() of the last user in the user's table list. This expression is based on the excellent tip by @Steve to find INDEX of an element in a list.

INDEXOF() - Google Cloud Community

The additional wrapping by outer INDEX() in the suggested expression highlighted in blue adds 1 to the found index of the last user in the History table that in turn points to the next user.

I have tested it at basic level. You will need some adjustments possibly in sorting the list etc.

However the looping part solution remains and will post if and when I come across any approach. Looping in AppSheet is in general complex.

Edit: For looping you could try the below expression 

IF(
COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & {LIST OF USERS THAT CAN DO A SPECIFIC TASK} & " , "),
(" , " & [LAST USER FROM HISTORY TABLE] & " , ")
)
),
" , "
)
)= COUNT({LIST OF USERS THAT CAN DO A SPECIFIC TASK}),

INDEX({LIST OF USERS THAT CAN DO A SPECIFIC TASK},1),

INDEX({LIST OF USERS THAT CAN DO A SPECIFIC TASK},
COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & {LIST OF USERS THAT CAN DO A SPECIFIC TASK}& " , "),
(" , " &[LAST USER FROM HISTORY TABLE] & " , ")
)
),
" , "
)
)+1
)
)

The red part of code finds if it is the last user in the list, if so it suggests the first user from the users list with the expression part suggested in magenta, or else the normal loop continues.

Again basic test done and it works. 🙂

[Connor_Mooney]

@Suvrutt_Gurjar This worked beautifully! I'm using this in the Initial Value formula to pre-select the next assignee in queue for a specific task, but I can see this method working well in other areas too. Thank you!