REF_ROW off email list?

AppSheet AppSheet Q&A
,
1

Greetings All!

Struggling how to make a reference off a list…. I have tried REF_ROWS, FILTER, and SELECT and I keep getting the same error.

FILTER(“central”, ([_THISROW] = [central_execs]))
SELECT(central[central_execs], ([_THISROW] = [central_execs]))
gives:
Cannot compare Email with List in ([_THISROW-1].[exec_email] = [central_execs])

REF_ROWS("central”,”central_execs”)
gives:
Cannot compare List with Email in ([central_execs] = [_THISROW-1].[exec_email])

I have two tables and I am trying to create a virtual ref row so i can see which activities each exec is involved in. Kindly note, an exec can be part of multiple activities and activities can have multiple execs on it (enumlist references).

Exec information (table name: exec)

  • Email = [email] unique ID
  • Display Name = [d_name] (Virtual column for fname & name) Label
  • First Name = [fname]
  • Last Name = [lname]

Activities (table name: central)

  • Activity Title = [activity]
  • Activity Description = [description]
  • Execs Involved = [central_execs] (enum list)

As always - any help and guidance is always appreciated

2

What’s your table schema? What’s your column structure? How your columns have been set up? What’s the relationship between tables and columns?

1 Like
3

Hi @Ms_Margo
Have you checked out references

2 Likes
4

From what i can tell, this works well for a normal reference, but not an enumlist reference

5

What’s your table schema? What’s your column structure? How your columns have been set up?> Exec information (table name: exec)

  • Email = [email] Key (email)
  • Display Name = [d_name] (Virtual column for fname & name) Label
  • First Name = [fname] (text)
  • Last Name = [lname] (text)

Activities (table name: central)

  • Key ID = central_key (uniqueid()) Key
  • Activity Title = [activity] (text)
  • Activity Description = [description] (text)
  • Execs Involved = [central_execs] (enum list reference to exec table)

What’s the relationship between tables and columns?> When users are adding activities they can add multiple executives. So for each activity it is a one-to-many (one activity to multiple execs). For each exec, it is one-to-many (one exec can be part of multiple activities).

6

FILTER() gives me the following error:
Cannot compare List with Email in ([central_execs] = [_THISROW-1].[exec_email])

IN() gives TRUE/FALSE so any guidance on how to this that into returning a list would be epic.

REFROWS & SELECT give the same Cannot compare List with Email.

7

Ms_Margo:

how to make a reference off a list

What does this mean?

8

GOT IT… almost…

SELECT(central[central_name], CONTAINS([central_execs],[_THISROW]))

Gives me a list of activities (comma delimitate) that each exec is involved in:

3X_2_b_2befae6a560ba31f2c5e17418265c61f26c6dae9.png
3X_2_b_2befae6a560ba31f2c5e17418265c61f26c6dae9.png1008×540 44.5 KB

Ideally it would be awesome to see activities listed like a “normal” reference (aka - not an ENUMLIST) so i can click on each activity to go into them. In the example below, the related centrals (2) is a system generated virtual column based on a normal (non-enumlist) reference.

3X_5_b_5b4c40370ca00f55e470bba4fd696be349e457f6.png
3X_5_b_5b4c40370ca00f55e470bba4fd696be349e457f6.png1018×457 48.1 KB

Does anyone know if it is possible to set up an expression that will give me the results of of Activity Reference in image one, but in output format of Related centrals (2) in image two?