4 Replies Latest reply on Jul 5, 2017 11:28 AM by Roman_S

    Populate database from lowest hierarchy

    Roman_S

      Hi Everyone!

       

      I'm just trying out FileMaker and would like to create a relational database with phytotherapeutic drugs.

       

      I've designed the simplest tables (phytotherapeutic drug, plant, plant family) and linked them through a unique ID.

       

      I would now like to populate all tables "on the fly" beginning in the lowest hierarchy in the table with phytotherapeutic drugs ("PflanzenDrogen") and either attribute an already existing entry from the plant table ("PflanzenStamm") or create a new entry if it doesn't exist yet. The same would be the case for the plant family.

       

      I've managed to link the tables and to create value lists from which to choose, but whenever I enter a new drug, it creates a new entry in the Plant table as well and if I choose an already existing entry from the list, I get an error message, since I defined the entries as unique values...

       

      Can anyone help please?

       

      Thanks, Roman

        • 1. Re: Populate database from lowest hierarchy
          planteg

          Hi,

           

          you tables and relationships do not seem to be right. Before getting there, we need to know how the objects relate to each other.

           

          Would it be right to say that:

           

          • a plant belongs to a plant family
          • a drug is made of one or more plant


          ?

          • 2. Re: Populate database from lowest hierarchy
            philmodjunk

            Your relationships look correct. What you describe sounds like a layout design (User Interface) issue.

             

            If you are going to create a new drug record, the two simplest options are:

             

            1. Use a layout based on PflanzenDrogen, format fk_Stamm_ID with a drop down list of Plant ID's and their names using a "use values from field" type value list where pk_Stamm_ID is the source of values for the first field.
            2. Use a layout based on PflanzenStamm with a portal to PflanzenDrogen with "Allow creation of records via this relationship" enabled for PflanzenDrogen. To add a new drug record, you enter data directly into this portal. To add a new plant record, simply select "new Record" from the records menu.

             

            There are other more sophisticated options that you can use for this, including options for creating records in the plant and families tables from the context of a layout based on drugs or that can create a "families" record from the context of "plants", but I would get one of these two options working first before trying something fancier.

            1 of 1 people found this helpful
            • 3. Re: Populate database from lowest hierarchy
              Roman_S

              Sorry I wasn't clear on that - the drug is an extract of the plant, so

              - a plant family would contain several plants

              - a plant could contain several "drugs"

              • 4. Re: Populate database from lowest hierarchy
                Roman_S

                Thanks! That sounds great for a start, I'll try the simple steps right away.

                (I might come back for the fancier solutions once I'll get the hang of the basics )