7 Replies Latest reply on Feb 12, 2016 11:12 AM by DanielShanahan

    Join table to populate numbered ingredient fields?

    hellkay

      Hello,

       

      I'm a filemaker DIY'er, and having trouble creating a join table using data imported from the existing ERP system:

       

      Table A: Recipe Table - each recipe with up to 20 ingredients: ingredient fields are numbered ing1 > ing 20, ingredient qty fields also #1 - #20.

      Table B: Ingredient Master Table with inventory levels (6500 records)

      Table C: Ingredient Log Table with batch dates, date of purchase and current cost

       

      I want a table where the existing recipe and ingredients data can be displayed, along with inventory levels and latest cost date.  I need to be able to create an updated recipe cost based on updated (or projected) ingredient costs.

       

      So far I have a join table between A & B, primary and foreign keys, but can't figure out how to key the ingredient table items to each of the numbered ingredients in each recipe.

       

      help??

        • 1. Re: Join table to populate numbered ingredient fields?
          onefish

          hellkay, it sounds like you should really consider making the recipe ingredients a separate table (your join table) and use a portal on the recipe layout instead of the 1-20 fields you currently have. Use the ingredient master table that you already have to form the value list(s) to enter ingredients into the portal records and ensure that you have a secondary key field that relates to your ingredient master table (primary key relates to recipe). Then you can use a calculation field in recipe ingredients table which basically multiplies the qty (field in recipe ingredients table) by the related cost in the ingredients master table. You can then place a summary field (being sum of the recipe ingredients cost calculation) on the recipe layout and you will have the recipe cost estimate.

           

          Now, that's all pretty easy to do but might require a bit of a migration strategy if your database already includes a lot of recipes.

          • 2. Re: Join table to populate numbered ingredient fields?
            DanielShanahan

            The recipe may also be called a bill of materials (BOM) and should be a separate table with keys linking to the item (aka inventory) table.  This recent thread may be helpful.

             

            This is a manufacturing type of inventory database.  I have found it helpful to google "manufacturing data systems" and look at the images to get a better understanding of how different developers/teams organize and present data - in this case, for manufacturing systems.

            • 3. Re: Join table to populate numbered ingredient fields?
              onefish

              Another thing to consider (slightly left of original question), is grouping records in your ingredients master table into 2 or 3 levels of hierarchy. The reason that you would do this is so that you can more easily find the ingredient you want to add to your recipe ingredients table using value lists.

               

              For instance if you have 1000 ingredients to choose from, then selecting the right one from a value list is ridiculously unwieldy. However, you could add 2 new tables to create a hierarchy to group by. Think of them as 2 levels of grouping for your ingredients master table. Lets say that in new table 1 you broadly group the ingredients into 10 categories at the highest level (spices, grains, etc), this table only needs to have 2 fields, ID and Description and 10 records.  In new table 2 you create 100 records with each 10 being a sub-grouping of the first table (spices group = savory, sweet, curries, etc), this table contains 3 fields, ID, Table1ID, Description. Now create 2 new fields to the ingredients master table and assign the correct values to group them.

               

              Now by creating a few new relationships and value lists, you will be able to use 3 fields with related value lists in your recipe ingredients table/portal to rapidly find the ingredient you want. You see field 1 will contain a value list of the new table 1 highest level grouping (only 10 items). Once selected then field 2 shows only related values from new table 2 records based on the value in the first field (which is only 10 items remember). Then the third field is your ingredients master list but showing only related values from both the preceding field values (so again only 10 items).

               

              Anyway, food for thought (pun intended :-)

              • 4. Re: Join table to populate numbered ingredient fields?
                hellkay

                Thanks so much for your input.  I love the idea of grouping the ingredient items into a couple of table layers, which will be step #2 since some of the 'ingredients' are recipes in themselves....

                 

                I'm kind of stuck on the migration strategy problem right now, since the first step is to review the existing recipe records.

                 

                In order to populate the join table with all the ingredients I assume I should import from the existing recipe table so that each record in the join will have a field for A: the recipe code, and B: all the ingredient codes, and C the ingredient quantity. I'm thinking that each ingredient record in the join table needs to also have the recipe ID#, right?  I've created the primary and foreign keys for both recipe and item master tables, with the foreign keys in the join (ingredient, BOM) table.

                 

                I would want to script the importation sequence so that I can update the table with new data daily.  Is there a script step that can analyze whether the ingredient field is empty and if so not perform the import?  I've tried this already and ended up with tons of formula records with blank ingredient fields...(some recipes have more ingredients than others).

                 

                As you suggest, I would create a portal in the recipe table to list the ingredients, and summarize the cost.  Since we're working on different platforms I would need to save the updated recipe data in excel format to send to production department...do summary field export?

                 

                 

                thanks sooo much for your help...

                • 5. Re: Join table to populate numbered ingredient fields?
                  onefish

                  hellkay, I think you're on the right track. In order to migrate into your new Recipe_Ingredients table you'll need the following fields and relationships as a minimum:

                  1. UID > it's own unique ID "cereal number". This can be auto entered at import.
                  2. RecipeID > this needs to match the UID of the recipe that the ingredient relates to. There needs to be a = relationship between these table:: fields and allow creation of records in Recipe_Ingredients via the relationship.
                  3. IngMasterID > this needs to match the UID of the Ingredients_Master table for that particular ingredient. Again there will need to be a relationship between the two table:: fields but don't allow creation of new records in either table.
                  4. IngDescription > Temporarily make this field a text field to assist with importing. You may delete it or change it to some other form of field after the import is done.
                  5. Qty > the quantity of the ingredient in the particular recipe.

                   

                  You will want more fields (i.e. cost calculation, etc) but just to keep it simple, the above fields are the important ones for import. I have assumed that you already have UID fields in both your Recipe and Ingredients_Master tables. I have also assumed that the Ingredients_Master table is populated.

                   

                  In effect, what you currently have is 21 records in each recipe. 1x parent record in the Recipe table and 20 child records in the Recipe_Ingredients table. So you will have to use the import records script step 21 times, each time importing all records but only select fields.

                   

                  The first import should be to your Recipe table. Import recipe UID, description, and whatever else is associated with the recipe directly but not any of the ingredients,

                   

                  Then import from the same source into the Recipe_Ingredients table. Import the recipe UID to the related field in the Recipe_Ingredients table. Also import the IngDescription and Qty from only the first ingredient in original recipe table (that you're importing from). So essentially what you've done here is create new child records in your Recipe_Ingredients table that will relate to the parent Recipe table records you created in the first import. These child records won't yet relate to the Recipe_Master table, we'll do that last.

                   

                  Now repeat the above import for the remaining 19 ingredients in the original recipe table. As you pointed out, there will be several empty records as not all recipes have 20 ingredients. Don't worry about that, just include a couple steps in your import script which finds all the records without any value in the IngDescription and delete the found set (then show all records again).

                   

                  Now to get the relationship working between the newly created Recipe_Ingredients records and Ingredients_Master records. This will probably not go smoothly unless the ingredient description in both tables matches. If they were both created using free text then it's unlikely that many of them will match but we may as well match as many of them as possible this way to reduce the amount of manual work that has to be done.

                   

                  Sort records in both the Ingredients_Master table and the Recipe_Ingredients table by ingredient description and perform another import. This time set it to update matching records (not create new records) based on the ingredient description fields in both tables. The only value that you want to import is the UID from the Ingredients_Master table (this will complete the relationship) into the IngMasterID field in the Recipe_Ingredients table.

                   

                  I'm not 100% sure if Filemaker will update all matching records with the Ingredients_Master UID or only update the first matching record. Try it and see, if it does all then great, if it only does the first then you will need to enter the rest. You could create another script that loops through a found set (matching IngDescription) and enters the Ingredients_Master UID. Or, alternatively I would probably just change the auto enter settings for the IngMasterID field to enter the ingredient description but don't replace existing and then perform a replace (then turn off autoenter). That way you can do all matching records at once without a looping script.

                   

                  Now the fun begins, matching all the Recipe_Ingredients records to the Ingredients_Master records where the description doesn't match. There's a number of tricks that could be used to semi-automate this but they depend a lot upon the actual data you are dealing with. If there's not too many just do it manually.

                   

                  Sorry for the thesis

                  • 6. Re: Join table to populate numbered ingredient fields?
                    hellkay

                    Wow!  So many "aha" moments!!!


                    THANKYOU for the thesis...I will let you know how the cookie crumbles....

                    • 7. Re: Join table to populate numbered ingredient fields?
                      DanielShanahan

                      Have you taken a look at BakeSmart?  It is an off-the-shelf solution that is built in FMP.

                       

                      (I'm not affiliated with BakeSmart, nor do I profit from referring).