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:
=filter(ExperimentObservationMapping,"ExperimentObservationMapping[Observation]=%",THISROW()[Observation])
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:
=filter(Experiments,"Experiments[Experiment]=%",filter(ExperimentObservationMapping,"ExperimentObservationMapping[Observation]=%",THISROW()[Observation])[Experiment])
But since I can't get the query below to work, I'm at an impasse and can't debug what is happening wrong:
=filter(ExperimentObservationMapping,"ExperimentObservationMapping[Observation]=%",THISROW()[Observation])[Experiment]
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.
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.
