6 Replies Latest reply on Dec 12, 2016 4:30 PM by dgarmsd63

    Need some script help please.

    dgarmsd63

       

       

      Hello All

       

      I have an old database at my company that has everything dumped into one table. Essentially it's setup as follows.

       

      Stock#

      Company No.

      Company Name

      Product name

      Box_1

      Box_1_Qty

      Box_2

      Box_2_Qty

       

      (There's a total of 20 different box locations for each product)

       

      This database has 9635 records with some products having as many as 18 boxes & box quantities.

       

      I'm trying to create a script that will take each old record and create a new record in a new table called locations and each new record will only contain 1 box location and associated quantity. So there will be multiple records for a single Stock# based upon how many different boxes that item is located in. This new table will later be used as my locations table related to a new product table so I can show all box numbers & quantities in a portal.

       

      In my thought process I believe there will have to be a loop with a counter so that it will cycle through each record as many times as required by the total number of different boxes the item is in.

       

      I tried using a lookup, but that didn't work ( or I did it incorrectly ) and I don't think there's a way to do it using the import function?

       

      This is all in FileMaker Pro 11 for Windows.

       

      Sincerely,

       

      D. Garms

        • 1. Re: Need some script help please.
          philmodjunk

          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

          Loop

              Set Variable [$K ; Value: 1 ]

              Set Variable [$SKU ; OriginalTable;:Stock# ]
               #Loop Thru the fields of one record

               Loop

                   Set Variable [$Qty ; value: GetFIeld ( "OriginalTable::Box_" & $K & "_Qty" ) ]

                   Set Variable [$Box ; value: GetField ( "OriginalTable::Box_" & $K ) ]

                   Go To Layout ["LayoutBasedOnNewTabe" (NewTable) ]

                   New Record/Request

                   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 ]
              End Loop

             Go to Record/Request/Page [Next ; Exit after Last ]
          End Loop

           

          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.

          • 2. Re: Need some script help please.
            dgarmsd63

            Hello Phil

             

            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?

             

            sincerely,

             

            D. Garms

            • 3. Re: Need some script help please.
              philmodjunk

              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.

              • 4. Re: Need some script help please.
                dgarmsd63

                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.

                • 5. Re: Need some script help please.
                  philmodjunk

                  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.

                  • 6. Re: Need some script help please.
                    dgarmsd63

                    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.

                     

                    Sincerely,

                     

                    D. Garms