How to create a virtual column that displays a random column

AppSheet AppSheet Q&A
,
1

I table with rows that look like
TEAM NAME | TEAM MEMBER 1 | TEAM MEMBER 2 | …

I created an appsheet app that randomly chooses a team row, and now I’m trying to randomly select a team member from that row. I created a virtual column and tried this first

[_THISROW].[INDEX({“Team member 1”, “Team member 2”}, RANDBETWEEN(1, 2))]

That resulted in this error:
Error in expression ‘[_THISROW].[INDEX({“Team member 1”, “Team member 2”}, RANDBETWEEN(1, 2))]’ : Unable to find column ‘INDEX({“Team member 1”, “Team member 2”}, RANDBETWEEN(1, 2))’

I also tried this

LOOKUP([_THISROW].[Team Name], “tech puzzle 1 data1”, “Team Name”, INDEX({“Team member 1”, “Team member 2”}, RANDBETWEEN(1, 2)))

But I get this error:

Function ‘LOOKUP’ must be passed constant values for its tableName, lookupColumn, and returnColumn parameters: LOOKUP(lookupValue, tableName, lookupColumn, returnColumn). Consider wrapping the constant parameters in double quotes to ensure correct parsing.

Any ideas what I can try next?

2

A column reference must contain only the column name, not an expression, which is why this doesn’t work:

dwatrous:

[_THISROW].[INDEX({“Team member 1”, “Team member 2”}, RANDBETWEEN(1, 2))]

In a LOOKUP() expression, only the first argument may be an expression; the remaining three must be literal values.

Maybe this?

INDEX(
  LIST(
    [Team member 1],
    [Team member 2],
    ...,
    [Team member N]
  ),
  RANDBETWEEN(1, N)
)
2 Likes