9 Replies Latest reply on Feb 14, 2013 11:46 AM by Oliver_Reid

    Two Tables, One Portal, Repeating entries :/

    micheleclark

      Hello!

       

      I have two Tables in one portal. I am not sure if I have the relationship right because whenever I fill in the first field (table one) and then go to the second and third field (table two), with every new record, the second and third fields change across records.

       

      This is my Portal:

      Screen shot 2013-02-14 at 12.54.37 PM.png

       

      & My relationship gets a little tricky in that I am producing a recipe using from a pool of ingredients, and with each new recipe, the pounds and process type change. I used to have a recipe table but it had no information in it.

       

      This then leads into a production and work log.

       

      Screen shot 2013-02-14 at 12.55.48 PM.png

      Can anyone help me understand why these repeats are occuring??

       

      Screen shot 2013-02-14 at 1.04.46 PM.png

       

      Thanks! Michele

        • 1. Re: Two Tables, One Portal, Repeating entries :/
          Mike_Mitchell

          Michele -

           

          What you have here is what's called a "join table". It's a way (probably the preferable way) of creating a many-to-many relationship. This is where each occurrence of entity A can have many occurrences of entity B, and each occurrence of entity B can have many occurrences of entity A (hence, "many-to-many").

           

          In this case, each recipe can have many ingredients, and each ingredient can belong to many recipes. Depending on how "ingredient" is defined, this is perfectly OK.

           

          Your confusion comes from what each record in the join table ("rel_productions_i...") represents. Each record in that table represents a unique combination of recipe and ingredient (or should). So you have to be careful when you create records there, and you have to be careful what fields you show in the portal. Otherwise, you'll get a lot of confusing display results.

           

          Based on the portal you have above, I'd say you need to display Ingredient Name from the ingredients table, and Pounds and Processed from the join table. This should solve the problem of which table holds what data. You also need to make sure that each combination of recipe_id and ingredient_id is unique. In most setups like this, it's customary to script record creation to avoid duplicate entries instead of relying on the standard "allow record creation" option (although, strictly speaking, you can do it if you're careful about allowing users access to the key fields).

           

          HTH

           

          Mike

          • 2. Re: Two Tables, One Portal, Repeating entries :/
            micheleclark

            Hi Mike,

             

            Essentially rel_productions_ingredients represents a recipe. I have the parents recipe_id in productions.

             

            I am currently displaying the ingredient name from the ingredients table and pounds and processed from  rel_productions_ingredients, thus my confuision of the repition. 

             

            Any other reason this may be occuring?

             

            Thanks!

             

             

            Edit: Perhaps amount and process_type need to be records of the ingredients as oppose to the recipe in rel_productions_ingredients...

            • 3. Re: Two Tables, One Portal, Repeating entries :/
              Oliver_Reid

              The Portal is bases on the join table and not the ingredients table - right? If it is based onte Ingredients table you would see exactly what is happening as the first join record related to teh recipe would appear in every ingredient row.

              • 4. Re: Two Tables, One Portal, Repeating entries :/
                Mike_Mitchell

                Michele -

                 

                Make sure the combination of recipe_id and ingredient_id is unique in rel_productions_ingredients. You shouldn't have more than one record that has the same recipe_id AND ingredient_id. It's perfectly okay to have more than one occurrence of the same recipe_id OR ingredient_id, just not both on the same record.

                 

                (Although, strictly speaking, when I look at your screen shot, you don't have a duplicate. You have ingredient A with c pounds processed d way, ingredient B with c pounds processed d way, and an unknown ingredient with c pounds processed d way. Those aren't duplicates, because they're different ingredients.)

                 

                And no, amount and process_type are entirely appropriate for a join table. They apply to the particular use of that ingredient in that recipe. You use 1 cup of flour in this recipe, but you use 1.5 cups of flour in that recipe over there.

                 

                Mike

                • 5. Re: Two Tables, One Portal, Repeating entries :/
                  Mike_Mitchell

                  Good catch, Oliver. I missed that.

                   

                  Michele, Oliver is right. Base your portal on the join table, not the ingredients table. This will give you the correct results.

                   

                  Mike

                  • 6. Re: Two Tables, One Portal, Repeating entries :/
                    micheleclark

                    Oh! But I mean I put the first record:

                     

                    Ingredient: A     Amount: 10.3    Process: Milled

                     

                    The second record I would like to be say:

                     

                    Ingredient: B    Amount: 5         Process: Premilled

                     

                    HOWEVER, now the amount and the process for my first record, Ingredient A, become 5 and premilled too!  It is not allowing me to have unique amounts and process_types with different ingredients

                    • 7. Re: Two Tables, One Portal, Repeating entries :/
                      micheleclark

                      oh like: 5lbs milled wheat as oppose to Wheat, 5 lbs, milled!!

                      • 8. Re: Two Tables, One Portal, Repeating entries :/
                        micheleclark

                        Sigh.

                        So the portal is more logical now, basing it first off the amount and process_type and then the ingredient from the ingredient table....

                         

                        However, now each new record overrides all the other records of the portal, creating duplicates.

                         

                        Is there some sort of way to allow unique records within this portal?

                         

                         

                         

                        EDIT: No, even still the amount and process become same across the records... how can I base the portal off the relationship table specifically?

                         

                         

                        Edit #2: When I put ingredient_name in the relationship table the portal works fine, but now I have my ingredient_id table floating. Does this ruin the logic?

                         

                        Screen shot 2013-02-14 at 2.34.04 PM.png

                         

                        Thanks!!

                        • 9. Re: Two Tables, One Portal, Repeating entries :/
                          Oliver_Reid

                          No that is not what I meant: Double click on the portal in layout mod and make sure the dialog says:

                           

                          "Show related records from    rel_productions_ingredients"