REF_ROW off email list?

[Ms_Margo]

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

[LeventK]

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?

[Ms_Margo]

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).

[Lynn]

Hi @Ms_Margo
Have you checked out references

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

[Ms_Margo]

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

[Steve]

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

REF_ROWS()

Gather related rows from table or slice

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Ms_Margo]

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.

[Steve]

Ms_Margo:

how to make a reference off a list

What does this mean?

[Ms_Margo]

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:

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.

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?