3 Replies Latest reply on Oct 23, 2013 9:10 AM by philmodjunk

    Delete data from a Looked Field

    brzasa

      Title

      Delete data from a Looked Field

      Post

           I have a relational DB's Recipes, Ingredients. In the Recipe Card DB I have the Fields - Ingredient and Ingredient_Shortname.

           When creating a new recipe, I add ingredients in repeating fields in the Recipe Card DB from a value list of ingredients in the Ingredient DB. The Ingredient_Shortname field is also a related Look-up which fills with an abreviation. This all works. I'm having trouble reversing this process. Let's say I have one too many spices and want to remove one. If I delete the value from "Ingredient", the previous Looked up value in "Ingredient_Shortname" remains. How to I get data in the related field to delete automatically?

        • 1. Re: Delete data from a Looked Field
          philmodjunk

               You could use a script trigger on field 1 that updates field 2 when field 1 is changed.

               I add ingredients in repeating fields in the Recipe Card DB

               Really? a repeating field? If that's the case and not a set of related records, ouch! A related table of records would be a much more flexible a way to work with this data!

          • 2. Re: Delete data from a Looked Field
            brzasa

                 "a repeating field?" I'm beginning to agree, but what are my options? I have an Ingredient DB, but the ingredient names are the vendor names, "Casserole Cheesebrgr Mac 4 Pasta/4 Seasoning Pkgs". The other DB is the Recipe Card DB. I was using a column of repeating fields to load the Ingredients which would be looked up from the Ingredient DB. The problem is, the cooks won't recognize the trade name. On the Recipe Card creation, I have two tabs, one for the production manager to enter the ingredients. The other tab would be for the "Cook's Term" matching the trade name field also in the Ingredient DB. That view would print. 

                 I tried this with a portal, but the ingredients kept duplicating for some reason. What would be a better option than stacking a bunch of repeating fields?

            • 3. Re: Delete data from a Looked Field
              philmodjunk

                   A related table and a portal would be the better way to go. Take a look at the invoices starter solution and note how an invoice can list multiple products in a portal, then imagine the invoice is your recipe and the portal is showing your list of ingredients.

                   Just by renaming the tables used in that starter solution, you'd get:

                   Recipes----<Recipe_Ingredient>-----Ingredients

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

                   You'd use a portal to Recipe_Ingredient for listing the ingredients for a specific recipe and Recipe_Ingredient::_fkIngredientID could be placed in the portal formatted with a value list for selecting ingredients. With "allow creation of records via this relationship" enabled for Recipe_Ingredient in the Recipe to Recipe_Ingredient relationship, you can add ingredients simply by selecting one from the value list in a blank portal row. Deleting a portal row deletes a record from Recipe_Ingredient which removes it from the list for a given record in Recipes, but the Ingredient record in Ingredients remains so that it can be listed in other recipes.

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