7 Replies Latest reply on Mar 30, 2011 8:20 AM by philmodjunk

    Yet Another Request for Help on Joining Records

    skineedog

      Title

      Yet Another Request for Help on Joining Records

      Post

      Ultimately I am trying to create a DB for recieps. Starting simple, will expand/extend after I learn a lot more about FM & DBs.

      I have created tables as follows:

      Recipe

      Recipe ID <PK
      Title
      Category
      Servings
      Ingredients <FK
      Instructions
      Notes

      Ingredients

      Ingredient ID <PK
      Ingredient

      Recipe_Ingredient

      Recipe ID <FK
      Ingredient ID <FK

      On the recipe form I have a portal set to show records from the ingredients table. I have poulated the ingredients table yet when I click in a field of the ingredients portal I do not get the drop down list of ingredients I expect. My portal has 12 rows. Although I do not see the ingredients I prepopulated the ingredients table with, I can create a new ingredient via the portal but only in the first row.

      Screen_Capture_2011-03-27_at_8.16.12_PM.png

        • 1. Re: Yet Another Request for Help on Joining Records
          philmodjunk

          Recipe_Ingredients is called a Join Table.

          Enable "Allow creation of records via this relationship" for the join table in the Recipe to Recipe_Ingredients relationship.

          Make your portal a portal to the Join table--not the ingredients table.

          Define your pop up menu's value list to list all Ingredient ID's in column 1 and Ingredient in column 2. Select the "show values only from second field" option so that you are selecting the ingredient ID, but see the Ingredient name displayed in the portal. With this value list, it's a good idea to set the "Unique values" validation rule on the ingredient field in the ingredient's table.

          Once you have all that done, you can add a quantities field and possibly a units field to Recipe_Ingredients so that you can also record the amount of each ingredient used in your portal.

          • 2. Re: Yet Another Request for Help on Joining Records
            skineedog

            Thanks for the assistance.

            I have started over & created another portal. Thus far it seems as though it is working almost as I expect with 2 exceptions:

            I selected to "Allow entry of other items" in the control box's options. I can now add ingredients that are not in the ingredient list but the newly created ingredients do not get added to the ingredient table. When I set up the relationship, I choose to "Allow creation of records in this table via this relationship" in all tables, which is the only option I found that seemed related to this issue.

            Once I get this figured out I give the measurements a go. I'm thisnking the measurements should also be placed into it's own table as well? What are you thoughts?

            • 4. Re: Yet Another Request for Help on Joining Records
              philmodjunk

              The method I've posted only serves to link existing ingredients records to your recipe records. To create a new ingredient record, you'd need an additional method for creating that record. The simplest method is to add buttons that switch you to and from the ingredients layout where you can add new records. There are also ways you can set up a global field or set of global fields, where you enter the data for a new ingredient record, then click a button to "save" it which performs a script that creates a new record in the ingredients table and then populates it with the new data from the global field.

              Here's a sample script, you'd add a field with global storage, gNewIngredient to your current layout.

              Freeze Window
              Set Variable [$RecipeID ; Value: Recipe::Recipe ID]
              #Create new ingredient record
              Go To layout [ingredients]
              New Record/Request
              Set Field [Ingredients::Ingredient ; Recipe::gNewIngredient]
              Set Variable [$IngredientID ; Value: Ingredients::Ingredient ID]
              #Create new Join table record to link it to current recipe record
              Go To Layout [Recipe_Ingredients]
              New Record/Request
              Set Field [Recipe_Ingredients::Ingredient ID ; $IngredientID]
              Set Field [Recipe_Ingredients::Recipe ID ; $RecipeID]
              Go To Layout [original layout]

              • 5. Re: Yet Another Request for Help on Joining Records
                skineedog

                I've managed to create the global field gIngredient. I've figured out where to begin creating the script but I'm stuck on the "Set Field [Ingredients::Ingredient ; Recipe::gNewIngredient]" portion. Attempting to specify the field only allows me to specify one field where it looks as though you're specifying two. "Ingredients::Ingredient & Recipe::gNewIngredient"

                I've tried using "Calculated result:" but obviously ";" isn't an operator.

                Also, when I create the "Go To layout [ingredients]", I end up with "Go To layout["Ingredients]' (Ingredients)]. Is this correct?

                • 6. Re: Yet Another Request for Help on Joining Records
                  skineedog

                  How about it Phil? Don't quit on me now!! :)

                  • 7. Re: Yet Another Request for Help on Joining Records
                    philmodjunk

                    Patience please, we are probably in different time zones.

                    When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Click OK. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.