Relationship with multiple criteria

User 259df256ea

19-06-2008 14:10:26

Is there a simple way to set up a many-to-one relationship where multiple field values need to all match? In this case, we have an HTS program organized by microplate barcode, row, and column. I'd like to be able to bring up a unique structure from a separate table based on a match to these three criteria.



ChemAxon fa971619eb

20-06-2008 12:01:42

So you would also have those 3 columns in the structure table?

This is not possible as relationships are currently restricted to a single field/column. However that column can be a text column (I just tested this and it seems to work OK), so potentially you could concatenate the 3 values into a single column for both tables at the database level (e.g. using triggers) and then use that value for the relationship.

Simpler through would be if you could include the compound ID as an additional column in your table, then you could use this directly.

Longer term we do plan to support fields that use multiple columns (this was one of the key reasons for the Field abstraction).


User 259df256ea

20-06-2008 13:50:48

Thanks Tim,
So you would also have those 3 columns in the structure table?
Not exactly. Plates are produced in replicate, so structures would be heavily repeated if stored in the same table as plate information. We have STRUCTURE -> PLATE-MAP -> ASSAY-RESULTS. Assay results would be reported in reference to plate#/ROW/COLUMN. This would be used to identify the compound ID, which would then be used to look up structure from a third table.

I did end up making a concatenated fields (using integers), which works fine for the time being.