4 Replies Latest reply on Aug 16, 2013 11:35 AM by scottk

    After choosing value from a record, how to automatically pull-in another field from same record?

    scottk

      Title

      After choosing value from a record, how to automatically pull-in another field from same record?

      Post

            

           I am using FileMaker Pro 12, coming over from Bento. A database newbie to be sure.

           I have an Ingredients table which is keyed by IngredientName. Each ingredient has a corresponding IngredientCost field.

           I also have a Recipe table. So far I have created 15 fields of Ingredient01 ... Ingredient15 to correspond to ingredients in a particular recipe record, e.g. Bread.

           I have figured out how to create a Values List to contain all the Ingredients, and a drop-down list in Recipe for each ingredient field using this Value List.

           But how do I pull in the corresponding IngredientCost from the Ingredients table for the selected ingredient.

           For example, if I have already input that flour is $.25 per ounce in the Ingredients table, and have selected "flour" as Ingredient01 for the Bread record in Recipe table, how do I pull in the corrpesonding $.25 in the IngredientCost01 field in Bread record ? 

           Thanks for any help.

            

        • 1. Re: After choosing value from a record, how to automatically pull-in another field from same record?
          philmodjunk
               

                    So far I have created 15 fields of Ingredient01 ... Ingredient15

               Instead of separate fields, define a related table with each ingredient listed in a different related record--all linked to a specific recipe record. This then allows you to link each record in this table to your ingredients table in order to access the correct cost value for that ingredient.

               Start with these relationships:

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

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

               You can place a portal to Recipe_Ingredient on the Recipes layout to list and select a Ingredients record for each given Recipes record. Fields from Ingredients can be included in the Portal to show additional info about each selected Ingredients record and the _fkIngredientID field can be set up with a value list for selecting Ingredients records by their ID field.

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

          • 2. Re: After choosing value from a record, how to automatically pull-in another field from same record?
            scottk

                 Thanks for the tips. That makes it more clear.

                 See attached screenshot.

                 I have setup the table :: field relationhips as you suggested. And I added the portal to Recipe_Items on the Recipe layout.

                 And I have the corresponding fields from Recipe_Items.

                 But when I click in the portal, nothing happens. How do I add a record? I set the Item as a drop down list using the value list I created to lookup values from __pkIngredientID.

                 I tried the Add Item script based on a FM template. But I couldn't get that to work either.

                 Any suggestions how to get the portal to add items to the list?

                 thanks.

            • 3. Re: After choosing value from a record, how to automatically pull-in another field from same record?
              philmodjunk

                   If you go to Manage | Database | Relationships, find and double click the line linking Recipes to Recipe_Items, you'll get a dialog where you can select "Allow creation of records via this relationship" for Recipe Items. (You don't need to select this option for Recipes.)

                   The Add line item button accomplishes the same thing in a script. You'd need to set up the script to create the new record and link it to the current record in Recipe Items. A script for this typically captures the current Recipe::__pkRecipeID value in a variable using set variable, changes layouts to a layout based on Recipe_Items, creates a new record, uses set field to copy the value from the variable to Recipe_Items::_fkRecipeID and then returns to the original layout. You probably do not need both methods.

              • 4. Re: After choosing value from a record, how to automatically pull-in another field from same record?
                scottk

                     Wonderful! That worked.

                     I had spent several hours on trying to resolve this issue myself. 

                     Thank you for your help.