Trying the route of using a joining table. But I can't get the syntax right

Have been trying the route of using a joining table. But I can't get the syntax right.

I have tables for Experiments[Experiment,...], Observations[Observation,...], and ExperimentObservationMapping[Experiment,Observation]

For any given Experiment I want to look up all the Observations it is mapped to and vice versa. So in the Observations table I added a column to try and get the list of appropriate experiments. The query:
It works and returns the table. Now my understanding is on this table I want to dereference the Experiments column and make it a parameter of my next filter. But when I try to dereference the Experiments column I just get a Value Error from Honeycode.

The query I thought I wanted was:

But since I can't get the query below to work, I'm at an impasse and can't debug what is happening wrong:

Hi @AliH-1cc2, welcome to the community! I put together an example of what might work out for your case, as I understand it, using similar tables as you've provided.

  1. Observations table

  2. Experiments table

  3. ExperimentObservationMapping table (the many to many)

  4. Screen 1

List (=FILTER(ExperimentObservationMapping,"ExperimentObservationMapping[Observation]=%",$[Picklist]))

Data cells

  1. Screen 2

List (=FILTER(ExperimentObservationMapping,"ExperimentObservationMapping[Experiment]=%",$[Picklist]))

Data cells

  1. In action

Hope this helps!

Thank you AJ. That helps. Actually I was able to get something like that working already. One thing I was trying to do was create the respective columns in the Observations and Experiments tables. But I changed my mind thinking it's not great data management to have too many derived quantities.

The other thing that I found confusing was that even though when you do a PickList in the UI it's only showing the first column, it is the full row so I had to add some dereferences of columns. Thank you for for your help.

Glad to hear you got it figured out! Keep us posted and stop in any time for additional help or guidance.