6 Replies Latest reply on Oct 30, 2013 9:45 AM by philmodjunk

    I'm having trouble figuring out how to relate my tables

    SkippDink

      Title

      I'm having trouble figuring out how to relate my tables

      Post

           I have a price list which has calculation fields to determine (as much as possible) unit prices for each item in fluid ounces kilograms etc. some of them are of course empty because there is no consistent way to get a volumetric measurement from say, 50lbs of flour.

           That being said, I also have a recipe table with recipe name, total yield, 20 each ingredient fields, with accompanying unit, amount, unit price and prep fields. I assumed that I would need to have a separate table with fields that do the do the calculating parts like yield % and recipe scaling options. That will all be easy though after I figure out how to get the first part down.

           First, I want to require each ingredient field in a record to be a value from the item field in the price list which I'm pretty sure I've got

           Then, I want the unit price field to look at the unit field and display the info from the appropriate per unit price fields in the price list table that corresponds with the ingredient chosen... Make sense? In a spreadsheet application this is accomplished with nested "IF" Formulas that contain "vlookup". so basically 

           if(Unit="ea", vlookup(Ingredient, price list, ea, False), If(unit="fl oz", vlookup(ingredient,price list,fl oz,False) Etc etc...

           Does this require a many to many relationship? Do I need some kind of intermediary table? I literally just started using Filemaker yesterday and I could definitely use some help.

        • 1. Re: I'm having trouble figuring out how to relate my tables
          philmodjunk

               Instead of those separate fields for each ingredient, you need a separate related table with one record for each ingredient.

               Recipe----<Recipe_Ingredient>-----Ingredients.

               Ingredients would be the table with your unit pricing

               A portal to Recipe_ingredient would be used to list ingredients and quantities required for a given recipe.

               The match fields:

               Recipe::__pkRecipeID = Recipe_Ingredient::_fkRecipeID
               Ingredients::__pkIngredientID = Recipe_Ingredient::_fkIngredientID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               And this then gives you a simple relationship to access those unit prices for each ingredient so that you can do cost calculations for each recipe with calculation fields defined in the Recipes table. A field, cCost, in Recipe_Ingredient can be defined as Quantity * Ingredients::UnitPrice. A field in recipe can then be defined as Sum ( Recipe_Ingredient::cCost ) to compute the cost figure for the recipe.

          • 2. Re: I'm having trouble figuring out how to relate my tables
            SkippDink

                 Thanks for the reply.

                 ok wow, so I have made the tables and the relationships. I am beginning to understand how a separate table will hold the ingredients listed as records rather than fields. But I have no idea how to equate fields across tables based on the input of one. like if Recipe ingredients :: Ingredient  = "Almonds, whole" then lookup record for "Almonds, whole" in Price list :: items and display Price list :: ID. I am also assuming that "recipe ID" field or something like it should be included in the recipe ingredients table so that I can use one table for many recipes. won't this make my recipe ingredients table much larger than my price list table pretty quickly? Also, I am looking to use this set up as a means to a simple graphical interface for adding, updating and Printing standardized recipes with costing information. I can not visualize (as I am a visual learner) a method to input new records across both tables while keeping the new entries in the Recipe ingredients table associated with a recipe simultaneously. Could you provide a quick example of how it all fits together? or take a look at what I'm working with?

                 https://www.dropbox.com/s/7n316c16m2prgoz/In%20the%20works.fmp12 

                  

            • 3. Re: I'm having trouble figuring out how to relate my tables
              philmodjunk
                   

                        Could you provide a quick example of how it all fits together?

                   Take a look at the Invoices starter solution that comes with Filemaker 12. Think of your Recipe table as the invoices table in this starter solution with the portal to Invoice Data corresponding to a portal to Recipe_Ingredient and the Products table then corresponds to your ingredients table. The only real difference is the names used. You can even find examples of how the invoice cost is totaled up that is quite similar to what I have recommended for cost calculations on your recipe records.

                   You can also find method for printing out invoices that could be used to print your recipes.

                   

                        I am also assuming that "recipe ID" field or something like it should be included in the recipe ingredients table

                   The Recipe_Ingredient table would be used for all of your recipes and a Recipe_ID field would be one of the fields in Recipe_Ingredient. If you take another look at my first post, you'll see that I have such a field described there: _fkRecipeID

                   

                        won't this make my recipe ingredients table much larger than my price list table pretty quickly?

                   Most likely, but it's needed to store the data that you need for your recipes and why is this a problem?

              • 4. Re: I'm having trouble figuring out how to relate my tables
                davidanders

                     Some of these links are useful - attempts to distill relational database design.
                http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

                     Some of the free open templates are good guidelines, some use naming conventions that are confusing
                     The youtube videos are also a grab bag.

                     A final note - searching on Google can be limited to one (or more) sites.

                     Google general search "ingredients filemaker" is limited to these forums by "ingredients site:forums.filemaker.com"
                https://www.google.com/search?q=ingredients+site%3Aforums.filemaker.com

                • 5. Re: I'm having trouble figuring out how to relate my tables
                  SkippDink

                       Thanks guys! looks like I've got some reading to do. 

                       In the invoices solution in "Invoices :: CUSTOMER ID MATCH FIELD" there is the calculation "=$$CURRENT_CUSTOMER_ID" where is that defined?

                       Oh wait I got it. Scripts... Thanks again! pretty sure I got it from here.

                  • 6. Re: I'm having trouble figuring out how to relate my tables
                    philmodjunk

                         It's for use on the iPhone and iPad layouts. If I remember correctly, an OnRecordLoad trigger on the customer layout performs a script to assign a value to that global variable. That allows you to have a layout where a new record on the Invoices layout is automatically linked to the customer from whose record you just came when you tapped a button to go to the invoices layout and create a new invoice.