Ref to many - which solution is better?

Hi!
I want to select many items from ref enumlist (and see this entry on ref table)
I want to add new entry for table A, select many workers from Enumlist and see this entry in worker related entries (table B).

I see three options, which is the best?
1/ create columns [worker 1], [worker 2] and ref one per column
2/ many to many reference as - Many-to-Many Relationships
3/ dereference? Use Enum/EnumList (Base Type: REF) to De-Reference Data & Reduce Reverse-Reference Clutter

Maybe I am not understanding the question completely but I would build 3 tables. Table A is your main table, Table R is your ref table with the list of selectable items, and Table B is a 1-many or many-many table that connects (through storing the keys from table A and R) which items on table R are selected for table A.

1 Like

It sounds like you want the user to be able to make multiple selections and capture those selections in an EnumList. You then want to display those selections in the style of the Related … columns that AppSheet generates automatically for Ref-type columns. To do this:

  1. Have a column of type EnumList with a base type of Ref and source table of the desired table. Give this column a Valid If expression that produces a list of Ref values from which the user may choose.

  2. Have a second column of type List with a source table of the same table referenced in (1) and a App formula expression that simply references the column in (1). For instance, if column (1) is named User Choices, the App formula expression for column (2) would simply be [User Choices].

2 Likes

Look at screenshots. I do something wrong but where?


What I did:

  1. Done
  2. I added new virtual column

I want to see related on this table.

Unfortunately, the system-provided Related … columns don’t handle Ref values found in lists elsewhere, so you’d need to construct your own.

So, construct it via your instructions? So what should I do next? I don’t fully understand second point.

1 Like

In the Pracownicy table, add a virtual column with an App formula expression of:

FILTER(
  "Protokol serwisowy",
  IN([e-mail], [_THISROW].[Serwisant])
)
1 Like

Doesn’t work.
Error in expression ‘[_THISROW].[Serwisant]’ : Unable to find column ‘Serwisant’
I tried to fix it but without effect.

Whoops! Try this instead:

FILTER(
  "Protokol serwisowy",
  IN([_THISROW].[e-mail], [Serwisant])
)
3 Likes

Thanks! This is it!

2 Likes