3 Replies Latest reply on Sep 5, 2014 9:16 AM by philmodjunk

    Repeating field with drop down menu list

    DanielGarcia

      Title

      Repeating field with drop down menu list

      Post

      HI,

      I am working from an existing layout for a basic ordering form.  It was setup with a repeating field for Item number, one for item description, one for pricing.  I want to make the item number repeating field into a drop down list, so I can select an item#, and then relate it to the item description so it automatically fills in the description.  I manged to relate a a new table I created will all the vendor, item#, description information to my main order table.  I can select an item number and the first field in the repeating description gets auto-filled,but when I select the next item# field, either 1) it changes the first description, and not the second one, or 2) it does nothing.

      Can someone let me know what I am doing wrong, and if there is a better way to get this?  I tried playing around with portals, but could not get anywhere with it.

      Thanks in advance.

       

      -Daniel

        • 1. Re: Repeating field with drop down menu list
          philmodjunk

          There is a much better way to do this. In fact, I don't think that you can set up a repeating field to do this with out doing some pretty convoluted scripting.

          What you need to do is replace your repeating field with a portal to a related table of "line items". Since you now have a non-repeating field for each item# field, a simple, single relationship can be used to look up or link to a description from a related products table.

          The typical ordering, invoicing or purchase order system looks like this in most relational databases:

          Orders---<LineItems>-----Products|Services

          Orders::__pkOrderID = LineItems::_fkOrderID
          Products|Services::__pkProductID = LineItems::_fkProductID

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

          You'd setup your Orders layout with a portal to LIneItems and either add a description field from Products to the portal row or set up a description field in LineItems to "lookup" the description from Products. (If you look up the data, future changes to the description in products won't change what is shown in past orders. Some businesses prefer a look up for the description, others do not.) Prices and hourly rates can also be looked up from a Products|Services table.

          The starter solutions that come with current versions of FileMaker have Invoices starter solutions set up on this design, but with different table names. You may find it helpful to examine the design of such a starter solution as a source of ideas to incorporate into your own solution.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Repeating field with drop down menu list
            DanielGarcia

            Thank you for this solution! I will look into it. I did read about using portals, but I was so confused.  I will look over the information and links you provided.  My only question/concern now is, layout with repeating fields was setup before I took over, and past orders are setup this way, If I change the layout to portals, will I loose the past orders? Would I have to start a new database going forward?

            -Daniel

            • 3. Re: Repeating field with drop down menu list
              philmodjunk

              You'll need to move the data from the repeating fields into the new related table that you'd use with your portal.

              FileMaker makes this very easy to do, fortunately.

              You can use Import Records to import the data from your current table into the new table. You'd "map" your repeating field to import into the non-repeating field in the new table. Once you click "import", you are asked if you want to split the repeating field data into separate records and you would select the option for that form of import. You can split multiple parallel repeating fields into individual records during this import.

              The only other key detail to look out for is to have a Primary Key field in place in your original table with a value assigned to that field in every record. When you import your data, you'd map this field to a corresponding foreign key match field in the new table along with mapping all you repeating fields. You'd leave all other fields out of the import process.