5 Replies Latest reply on Aug 15, 2016 7:52 AM by Mike_Mitchell

    Help with data relations


      hello all, I'm looking for a bit of guidance on how to relate my data. Im building a database of experimental data for compounds Iv made. I have one table which contains information about each compound. I then have another table which describes an experiment. For each experiment I test three different compounds, so I have three different sets of fields in the second table (compound 1...3, results 1...3 etc.).

      What I want to do is to be able to pull all the data for a specific compound, i.e. generate a new table that pulls the experimental data for a specific compound from all records in the second table.

      I hope this makes sense, Im still a newbie with filemaker and maybe not sure how to explain conceptually what I am trying to do.

      many thanks!

        • 1. Re: Help with data relations


          can you add a screenshot with relationship and table?

          • 2. Re: Help with data relations

            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.

            • 3. Re: Help with data relations

              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.





              2 of 2 people found this helpful
              • 4. Re: Help with data relations

                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!





                • 5. Re: Help with data relations

                  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.)