Ref to many - which solution is better?

[Wojciech_Sz]

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

[EIG]

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.

[Steve]

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

[Wojciech_Sz]

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.

[Steve]

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

[Wojciech_Sz]

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

[Steve]

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

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

[Wojciech_Sz]

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

[Steve · Accepted solution]

Whoops! Try this instead:

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

[Wojciech_Sz]

Thanks! This is it!

[Marc_Dillon]

Create ref_rows based on enumlist Questions

Hi, I have a table with Cars and names of drivers who can drive it (simplified example). The Driver are picked from a a reference list on a separate table used as Enumlist in the app, hence multiple people can be drivers of a certain car. I would like to create a reference where I can see what cars the drivers can drive. I can only make the reference match work if there is one driver per car. Can you help me understand how I can use certain part of the string to make the reference matching. I a…