I've been banging my head on a wall for the past week, trying to do something in filemaker and i haven't been able to succeed. So if you guys could give some of your marvelous ideas, it would be awesome! I'm very new to databases so it's probably a simple/stupid problem and I don't know the right terms to search for a solution (i tried, believe me!).
So here's the problem:
I'm currently creating a DB that will store and organize all the data (including files) for my experiments at work (chemistry).
There is one main table called Reactions that contains the records for each individual experiment and other tables that contain data related to that one (for example, a single reaction record can be associated to multiple HPLC or TLC records). Each experiment (reaction) also has a list of things (reagents) that were used in it, in different amounts (in terms of grams or mililiters).
So i created 2 tables, Reagents (list of reagents and amounts used in that particular reaction) and Reagent_detail (full details of the referenced reagent, including batch, chemical identification, etc). The problem is that the full details for the reagent take a lot of work to enter, so i thought to make so that you can enter it once per reagent and use the same data in other records (hence the separate table for the details). I tried to do this by defining a unique reagent in the Reagents table as a combination of common_name and Lab_reference and relate those 2 to the ForeignKey of reagent_detail table. To display the details on the layout i used the new table occurrence script technique (changing field in the Reactions table).
My problem is that if i enter the information for a reagent using common_name and unique lab_references that data is not displayed on other Reaction records.
What am i doing wrong?
Summary of layout (picture in attachment)
Portal(Reagents) ------ Data(Reagent_detail)
Sorry for the wall of text, i tried to explain the best i could.