5 Replies Latest reply on Feb 24, 2015 8:22 AM by philmodjunk

    Adding Portal Record with SQL Source



      Adding Portal Record with SQL Source


      I am using FMP to create layouts for a SQL data source. Each entry is a recipe of chemicals. There are 3 tables: recipe info (date made, serial number), recipe ingredients (ingredient #, chemical ID, and amount), and a chemicals list (name, part number, ID, use type). There are more fields for each this is just the basics.

      I setup a recipe entry layout that displays records from the recipe info table. My header shows the date made, serial number, technician, etc..

      I then have a portal in the body of the layout that shows the recipe ingredients table.

      I can add/delete records through the portal without a problem, but I need to be able to do it by selecting the chemical name. This is proving difficult because the portal is setup to display values from the recipe ingredients tables, but the chemical name is not stored in this table.

      Any ideas as to how this can be done?

        • 1. Re: Adding Portal Record with SQL Source

          A value list need not draw values from the same table as the field formatted to use it. A "use values from field" value list should be able to list values from whatever table stores the chemical names of your possible ingredients.

          • 2. Re: Adding Portal Record with SQL Source

            If I understand, I think you are saying that in my recipe ingredients table portal I can use a value list to show the chemical names from the chemicals list table. I think this might be a different problem than the one I am trying to solve. Unless I am missing something.

            Recipe ingredients table has these fields: ingredient code, amount of ingredient, lot number, etc.

            Chemicals list table has these fields: Ingredient code, name of ingredient, supplier, etc.

            The recipe ingredients tables is displayed through a portal, to allow users to enter their recipe. I would like them to be able to use a drop down list of chemical names.

            My problem is that the recipe ingredients table does not store the chemical names, just the chemical codes, which don't mean anything to the user. Does this make sense? Sorry if this is unclear.

            • 3. Re: Adding Portal Record with SQL Source

              From what I see, what I am suggesting is exactly what you need for what you are asking.

              Open Manage | Value Lists and create a new "use values from field" value list.

              For field 1 of this value list, select the Ingredient code of your chemicals list table. For field 2, specify the ingredient name of the same table.

              Now format your ingredient code field in your portal row (use field from recipe ingredients table for this value list), with the above value list and you can select an ingredient by name, but the value list enters the ingredient code.

              What I am describing is the "beginner level" approach. It's a good starting point, but is best suited for relatively small value lists. There are a number of approaches that can be set up once you have the "beginner version" working, that are more user friendly.

              Conditional value lists can shorten the value list by having the user select a category and then the value list of ingredients limits itself to only those ingredients that are a member of that category.

              And there are ways to set up a name based value list that "auto completes" (type in a few letters and the list filters down to just value starting with those letters), but which still links records by ID numbers.

              And there are other, more sophisticated options than that you can set up.

              You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

              Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
              Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Adding Portal Record with SQL Source

                Thanks a lot for your help. I am having some problems with the SQL data source so sometimes it is hard to tell why something doesn't work. I went back and reestablished my tables and I got this working.

                My next question is is it possible to display only the chemical names this way? My field is chemical ID, and I have a drop down list that allows you to select from the chemical names. When you select a chemical name it enters the appropriate chemical ID # in the field, but the chemical name is not displayed anywhere. I would like the user to not see the chemical ID # at all.

                Thanks again for your help!

                • 5. Re: Adding Portal Record with SQL Source

                  I suggest that you download the Adventure files that I recommended.

                  If you use the pop up menu format instead of drop down list, the pop up menu format will still show the "visible field" specified for the value list even when you exit the field.

                  You can also add the name field from your Ingredients table as a separate field if you have defined a relationship linking the Ingredient ID fields of the two tables. When you select an ID in the drop down list field, this field updates to show the name.

                  And if you refer to some of the "side topic" examples in the two files that I recommended, you'll see some discussion of ways to hide the drop down list field back behind the "name" field from the related table, but also ways you can use to make this list an auto-enter value list based on the name.