3 Replies Latest reply on Nov 21, 2013 7:07 PM by philmodjunk

    Repeating Fields and Lookups



      Repeating Fields and Lookups


           I am trying to help a friend out with a FM problem. They had a FM database in v6 and I helped them upgrade to v12. Now they're experiencing a problem. I haven't used FM since v8, so please have patience with me :-)

           Here's the problem: There is an Invoices table and an Inventory table. There is a relationship established between them using the ItemID field. The Invoices table has three repeating fields for itemizing the things to be purchased: ItemID, ItemDescription and UnitPrice. Both ItemDescription and UnitPrice are defined to be lookups to the Inventory table. When I enter an ItemID (in the Invoices table) in the first repetition of the ItemID field, then ItemDescription and UnitPrice are automatically filled in from the Inventory table, just the way they should be. But if I enter an ItemID into the second repetition, then no information is pulled over from the Inventory table. I cannot figure out why not; it worked in FM 6. The behavior is the same whether I am using FileMaker Pro 12 for Mac or Windows.

           Please don't suggest that I convert the repeating fields into a LineItems table; I know this is the right way to go, but I can't re-design the solution at this time (believe me, I didn't design it in the first place! :-)). I did a Google search and have found this same question asked more than once, but no one ever answers (except to tell the questioner that they should use a LineItems table rather than repeating fields).

           Thank you very much in advance for your assistance!

        • 1. Re: Repeating Fields and Lookups

               I'm surprised this even worked in version 6. I had a solution in 5.5 where I tried this many years ago and it didn't work in 5.5--I ended up using multiple non repeating fields, before changing it into a portal to a line items table when I upgraded it to version 10.

               The reason you keep hearing that you should use a related table is that your lookups won't work with a repeating field and it's fairly easy to use Import records to convert data in a set of such repeating fields into a set of related records linked to the primary key of the original table.

               The only work around that I can think of is about as much work as redesigning the data model to use a related line items table.

               A script trigger on the ItemID field could use Get ( ActiveRepetition ) and a scripted find on the inventory table to find the inventory record with the same ID, copy the needed values into a set of variables and then return to the original layout where a series of set field steps updated the correct repetiions with the data from the variables.

          • 2. Re: Repeating Fields and Lookups

                 Yes, I see what you mean, PhilModJunk.  I think your answer is the best there is, and my friend is just going to have to accept that.  Clearly, a redesign of the structure is the right approach. Either that, or not upgrade to v12!  That's what I suspected, but I just wanted to be sure I hadn't missed anything. Many thanks!

            • 3. Re: Repeating Fields and Lookups

                   Here's how to Move data from a set of repeating fields into a related table:

              1.           Make sure that your original table has a serial number field. If you don't, add one and use replace field contents to populate this field with serial numbers.
              3.           Go to Manage | Database | Tables and define a new table. Use the fields tab to add corresponding fields in this new table. You need one table for the serial number and one non repeating field for each of your group of repeating field. (In FileMaker Advanced, you can copy and paste these fields from your original table and then change them from repeating to nonrepeating.)
              5.           Click on Relationships and link the serial number field of the original table to the match field of your new table. There's a very good chance that you should enable both the Delete and the "allow creation..." options for the new table in this new relationship.
              7.           Click Ok to Leave Manage | Database. Do a Show All Records on the layout of your original table and go to your new layout for your new table. (This new layout is added automatically when you add the new table.)
              9.           Select Import records | File from the file menu. Select the very file you are already working in as the file from which to import data and select your original table as the source table. Drag fields from your new table to align the fields for import and click the Import button. A small dialog box pops up that asks you if you want to split the repetitions into individual records. Select this option and import your data.
              11.           That's it. It takes longer to type this up than it does to do. Once you have your data moved into the new table, you can go to the layout for your original table and use a portal to replace the repeating fields.