I'm new to IJChem and relatively new to database handling, so please excuse my perhaps very simple question.
I have a very basic setup - two tables. Table1 with structures, compund IDs and some other identifiers. Table2 contains results from various assays organised in the following way: Compound ID; Assay Name; Result.
The two tables are connected in a many-to-one relationship with the "one" being the Table1 (unique Compound ID) and "many" Table2 (for each unique Compound ID there are many assays done).
I have created a form view with the structure widget (from table1), a table widget for the CompoundID(table1) and a separate table for the assay data from table2. When I search for the structure or compound ID, the query obviously returns all the corresponding assays.
What I would like to be able to do is to set up a form that limits the obtained results to only one predefined assay from table2 and not just all rows corresponding to the unique compound ID. I have not found a way to do it so far.
I thought that maybe setting up a query where I search for the specific compound ID AND for the assay name would do the trick, but surprisingly it gives back again all data. Also when I try to search just for the assay name in Table 2 it gives back all data. It seems like when I run a query like that it searches for all IDs from Table1 that have corresponding assay data in Table2 (which actually means all of them) and then gives back all data associated with these records, not limiting it to the original query.
Is there something I can do about it? Or am I approaching this in the wrong way from the start?