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.
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!
Here's how to Move data from a set of repeating fields into a related table:
- 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.
- 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.)
- 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.
- 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.)
- 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.
- 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.