Querying in a relationship

Hello,

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?

Well, if anyone is interested, I found a workaround.

Setting up a query for both the cpd ID (or structure) as well as the name of the assay will bring back only the relevant assays results when the "filter search results" is marked.

This already helped a lot, but unfortuantely it does not solve the original problem: is there a way to set up a form to be able to search for a Cpd ID and predefine a field to always only limit the results to specific assay? I know now I can search for this assay while building a query each time, but what I actually want to do is set up a form view where for each cpd id separate fields will show me results from different assays.

Since I might not be clear: the child table may look like that:

CPD ID    Assay Name    Results

1                    assay1               x

1                    assay2               y

1                    assay3               z

 

Now when I search for cpd id '1', I want to see a form where:

PredefinedField1               PredefinedField2                        PredefinedField3

assay1     results                assay2     results                        assay3     results

 

At the moment this seems to be impossible since you can only search for one assay in any of the fields linked to a single table.