I suggest upgrading from FileMaker 11. The suggestion that I am making would not be improved in FMP 15, but it has a lot of nice new features plus better security design so an upgrade is warranted if you can afford to do so.
A Looping script can use GetFIeld to calculate the name of one of the "box" fields and then transfer the data to a new related record. You can do this via the MagicKey type relationship or your script can set variables to the needed values, change layouts, create the new record, set the values from variables to the new new record, return to the original layout and repeat. You'll also need nested loops, the outer loop loops through your records and the inner loops through these fields.
Here's a rough outline:
Go to Layout ["layoutbasedonOriginalTable" (OriginalTable) ]
Show All Records
Go to Record/Request/Page [First]
#Loop thru the records
Set Variable [$K ; Value: 1 ]
Set Variable [$SKU ; OriginalTable;:Stock# ]
#Loop Thru the fields of one record
Set Variable [$Qty ; value: GetFIeld ( "OriginalTable::Box_" & $K & "_Qty" ) ]
Set Variable [$Box ; value: GetField ( "OriginalTable::Box_" & $K ) ]
Go To Layout ["LayoutBasedOnNewTabe" (NewTable) ]
Set Field [NewTable::Stock# ; $SKU ]
Set Field [NewTable::Box ; $Box ]
Set Field [NewTable::Qty ; $Qty ]
Go to Layout [ Original Layout ]
Set Variable [ $K ; value: $K + 1 ]
Exit Loop if [ $K > 20 ]
Go to Record/Request/Page [Next ; Exit after Last ]
This is just a rough draft, I did not set it up to transfer data from every field, some of those do not look like fields that should exist in your new table but rather in your products table.
An alternative approach would be to manually use Import Records to import this data from the old table into the new. This avoids the need for a script, but would require you to do 20 different imports, one for each set of Box fields where you map a different set of those fields in the old table to the same set of fields in the new table.
thanks so much for the excellent rough draft! I created a test script based upon your outline and it's almost working 100% the way I wanted. I ran it on a few test records and it's giving me the correct number of new records per Stock#, but it's not stepping through the different box #'s or qty values. It's using the very first box number and corresponding box qty and repeating it "x" number of times. I've double checked my variables as well as my spelling and haven't come across my error yet. Any thoughts on your end sir?
IMG_9963.JPG 684.4 K
Doesn't sound like you are using GetField correctly
I suggest posting your script.
You can use the picture icon above to upload a screen shot captured from the scripts work space
You can paste the script as text from a database design report (using FMP advanced)
You can print a script as a PDF and copy/paste the text from the PDF.
Sadly no internet at our warehouse location. I just added a pic of my script to my previous post. You'll notice I added a count variable at the start to give an exact count of box numbers used.
You put the set variable steps for getting the box data outside of the inner loop. If you go back and check my example, they are part of the inner loop. Putting them there will always be using a value of 0 for $K and thus you don't get values from the other fields as you never reference them.
UGH!!! I'm such an idiot! I knew it was something I screwed up! Thank you so much for all of your help today.
Have a great evening.