can you add a screenshot with relationship and table?
i would suggest that you google data modeling and/or normalization to learn how to properly structure your data.
Their is a wealth of information on how to think about data and optimize it for fitting into an RDBMS.
IMHO this is vital foundation knowledge for any FM developer.
It starts out with taking the statement you wrote and extracting the nouns as a starting point for entities and entities can become tables.
So far you have compounds, experiments, and results.
2 of 2 people found this helpful
Following on what coherentkris has said, your basic data model is flawed. By duplicating the three compounds on the same record, you create reporting headaches for yourself. (You have to find the compound - either relationally or via Find - across three fields every time you do anything.)
What you likely need is what's referred to as a "join" table. Relationships come in three basic types: one-to-one, one-to-many, many-to-many. In the case of experiments and compounds, you have a many-to-many - each compound can be related to many experiments, and each experiment is related to multiple compounds. (Three now, but you want to make it flexible.) So you create a table that sits "in between" the two parent tables, where each record represents the unique combination of compound and experiment.
Besides online sources, the FileMaker Training Series has good sections on normalization. As coherentkris mentioned, this is a vital skill for any database developer.
Hi Mike, thanks for your reply. Ill look into a 'join table'.
I appreciate that having a table with three compounds on it is not easy to manage, these do however represent different compounds tested at the same time on the same experiment. It is therefore important that the data are related together and their relative positions (1,2,3) in the experiment recorded as if one compound shows an unexpected result it casts doubt on the whole plate.
As you can tell, I'm not a database professional and was hoping that i could throw something together without too much experience. I think Ill have to do more reading!
You can easily include a sort field in your join table to indicate which compound goes first, second, third, etc. But if you want to be able to pull data about a single compound, you'll hate yourself if they're listed in separate fields. Makes it really difficult to isolate.
(Note: There are some other ways around it, but the accepted best practice for this situation is a join table.)