2 Replies Latest reply on Apr 16, 2012 6:09 AM by philmodjunk

    Repeating Fileds or Portal for Ingredients List

    shronk

      Title

      Repeating Fileds or Portal for Ingredients List

      Post

      Hey there,

       

      I'm working on a recipe database and just got stuck.

      For my recipes I have various ingredients that I'd like to link to their Ingredient Table so I can see all the recipes that use this ingredient and get some more information.

      Should I just create 3 tables (recipe, ingredient, details) and add a portal into my recipe or is there a better way to do this? Would repeating fields work?

      Wouldn't I make my database just too big for nothing if I created another table that would list a measurement and an ingredient for every single recipe?

        • 1. Re: Repeating Fileds or Portal for Ingredients List
          erolst

          Size is not really an issue when you're dealing with IDs and some text in a (I assume) single-user solution.

          Repeating fields in FileMaker are a relic from pre-relational times, and are to be avoided for storing real data

          (as opposed to runtime data, meta-data (layout graphics) etc.). Any kind of reporting with repeating fields is

          bound to become a nightmare for all but the simplest applications. A proper setup relational system, on the

          other hand, is powerful and flexible tool, and is easy to implement.

           

          The normalized relational structure for your solution uses three tables:

          Ingredient – each record is an ingredient

          Recipe – each record is a recipe

          Recipe_Ingredients (or “Details”, as you put it) - each record is (an occurrence of ) a specific ingredient for a specific recipe,

          as indicated by two foreign keys (IDs from Recipe and Ingredients table); define additional fields here to add measurements,

          comments, or a sort field to put the ingredients in the proper order for preparing the recipe.

           

          Now in a Recipe layout you could use a cartesian portal to choose from the list of available ingredients to add to your recipe

          and see the ingredients used in this recipe in a second portal, where you can comment and sort them.

          In an Ingredients layout, you can see which ingredients are used in which recipe(s), compare a sum of measurements used

          with the stock in hand and have you give an alert when it's time to go shopping.

           

          You see, this relational structure allows for a lot of flexibility, and the reporting features as described are hard to impossible

          to realize with repeating fields.

          • 2. Re: Repeating Fileds or Portal for Ingredients List
            philmodjunk

            What Oliver Stroh is suggesting is called a many to many relationship and definitely, it is the way to go. You may want to look over this demo file for ideas in how to implement a many to many relationship. The "check boxes" layout in this demo is vary similar to what he describes with a cartesian join relationship (X operator instead of =). You'd just add a portal to the join table to list the selected ingredients and fill in the additional details on how they are used.

            http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html