14 Replies Latest reply on Aug 9, 2016 10:57 AM by betharitzert

    Problem with Script - Looping through Found Set and Losing Currency After Adding Record

    betharitzert

      I am having a problem with a script that I am running.  This script first finds a found set of records in a table named Reviews.  A Loop is created to loop through each of the records in the found set and for each record in the found set, create a new Reviews record using script step "New Record" and storing the foreign key, and a couple other field values.  I see the first new record getting created, but I believe when my script goes to read the next record in the found set, it has lost its currency due to the new record being created. 

        • 1. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
          jbrown

          Hi.

          When the script creates a new record it creates that at the end, so there is no next record to go to. You're right: you lose the record you were on.

           

          You have a couple of options:

          1. In between the go to record step and the create new record script, add a new window script step. You're opening up a new instance of this found set. After all the fields have been set, close that window. You'll still be on the same record you were on. Windows are record-independent: you can be on record 4 in Window 1, and create a new record (at the bottom) in a new window. Creating a record in the 2nd window does not effect where you're at in the first window.

          Then go to the next record and so forth:

           

           

          Go to record [first]

          Loop

          //get all your data from this record

          New window //same layout

          new record

          //set all fields

          close window

          Go to record [Next, exit after last]

          End loop

           

          Something like that. Be sure to use the script debugger in FM pro Advanced to watch this happening.

           

          Other options include gathering all the data from the found set and then looping through the gathered data to create new records. Let us know if you need advice on that.

          2 of 2 people found this helpful
          • 2. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
            betharitzert

            Hello Jeremy - I had seen a similar post and the New Window option was suggested.  I don't really understand what this New Window command does but I will look it up.

             

            Unfortunately the version of FMP I am doing this on is FMP 11 - NOT advanced - so I cannot run the script debugger.  On my personal computer, a MAC, I have FMP 12 advanced.  Do you know if I can make a copy of a FMP file and email it so I can open it up in FMP 12 Advanced to run the script debugger?  Or if not, I can recreate the issue in a simple little sample database in FMP 12 Advanced so I can run script debugger. 

             

            I will try your suggestion - thank you SOOO much!!

            • 3. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
              betharitzert

              Hi Jeremy -  you mention:

               

              Other options include gathering all the data from the found set and then looping through the gathered data to create new records. Let us know if you need advice on that.

               

              I would like advice on this, as well.  Thanks!!  I have been out of FMP development work for about 3 years so I am rusty!! 

               

              • 4. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                jbrown

                Hi.

                FM 11 and 12 have a file format change between them, so you can send yourself a copy of the FM 11 file and run it in 12 after you converted it.

                To convert it, simply drag/drop the fm11 file (<<filename>>.fp7) onto the FM 12 icon and it will convert it for you. (it will make a new copy of it, so you don't lose your .fp7 file.


                once you've done that you cannot open the .fmp12 file in FM 11.

                 

                As far as the New Window, here's the documentation: New Window

                 

                It simply opens up a new window, the same command you do in the "window" menu of most applications. In this case, it is opening up a copy of the current window you're on. This could cause problems if the tables are wide (many fields) and FM has to render the data again in the 2nd view. There are some workarounds for this.

                Here you still have your found set and you see the same records. When you create a new one, the active record is no longer the 1st (or whatever) record you were on during the loop. Instead the active record is the new one.

                 

                I use this often to duplicate records. If I'm copying a record about student school year information from 15/16 to 16/17, I'll start by opening up a new window. In that new window I'm still on the correct record. Then i'll duplicate the record in that 2nd window and change some data. Then I'll close this window and move on to the next record. It is a useful way to duplicate records.

                1 of 1 people found this helpful
                • 6. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                  jbrown

                  I have to open up FM 11 again to see how I'd do this in that version Its been a long time since I worked in 11. There's so many new features and functions to do things like this in the current version. Let me see what specifics I can come up with.

                   

                  The basic premise is that you:

                  1. Find your set.

                  2. Gather the required data from all the records. If it is just a foreign key, and all foreign keys are the same in that found set, then you can set it into one variable.

                  3. Get a count of the found records. Set the count into a variable.

                  4. Create a loop. It should loop for the number of records you want to create.

                  5. In each record, set the fields as you need them.

                   

                  Can you give a little more detail about what data you're collecting from each record? Why are you creating a new record for each record in the found set?

                  • 7. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                    alangodfrey

                    "Why are you creating a new record for each record in the found set?" - a very important question.

                    Another handy way to achieve the duplication of a set of records is to find them, export them, and re-import them.  The new found set will be the imported (ie: only the duplicated) records.

                    • 8. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                      jbrown

                      I love that method. Not sure why, but it is a good one. I recently realized I could do that and ReplaceFieldContents to change all the year 15/16 to 16/17.

                      • 9. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                        betharitzert

                        This database is a Vendor Samples database.  It is for a company that has products manufactured by factories overseas, from various vendors and factories.  The samples get several different review rounds based on what the sample the vendor sends back looks like and when the sample is not approved a new review round must be created. 

                         

                        The basic relationship is Item -> Component -> Review.  One Item can have many Components, and each Component can have many Reviews.

                         

                        Each day, the client executes a script that creates a report identifying all Reviews that have ReviewerApprovalStatus = "Resubmit."  For each of these records that shows on the report, the client manually goes to the Components form, and a portal exists with the one or more Reviews.  They manually add a new Review by creating a new entry in the portal, they populate the following fields:

                        Review Type = the same value as the previous Review

                        Review Round Number = the next number

                        Date Art Sent = the Approval Received Date from the previous review round.

                         

                        The client wants this automated - so for each record in the report, a new record will be created.

                        • 10. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                          betharitzert

                          Each day, the client executes a script that creates a report identifying all Reviews that have ReviewerApprovalStatus = "Resubmit."  For each of these records that shows on the report, the client manually goes to the Components form, and a portal exists with the one or more Reviews.  They manually add a new Review by creating a new entry in the portal, they populate the following fields:

                          Review Type = the same value as the previous Review

                          Review Round Number = the next number

                          Date Art Sent = the Approval Received Date from the previous review round.

                           

                          The client wants this automated - so for each record in the report, a new record will be created.

                          • 11. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                            betharitzert

                            What method do you love? I am confused by that reply. 

                            • 12. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                              jbrown

                              The method is to export the records that you found and then import them back in. That creates the records you need, in your case, one for each in the found set, and then allows you to loop through them to update any data.

                               

                              You would:

                              1. Get your found set.

                              2. Export records - exporting all fields in the table. ANd export this to a temporary folder.

                              3. Import the records back in from that temporary folder, Adding new records, matching fields based on 'matching names'

                              --At this point the records are created. Now you need to loop through them and reset any values.

                              • 13. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                                jbrown

                                Hi there. Sorry for the delay, I was considering the options.

                                You have three (but there may be more):

                                1. Do as your original question and my original response indicated: Create a new window and duplicate the record, change data, and then close the window. This can be expensive in terms of processing power as FM has to generate a new window and all the data each time. It will work, but may slow things down.

                                2. Export the found set to a temporary path. Then re import the records. Finally go to each record and change data as needed (up the round number by 1, etc). This works but you're going outside FileMaker and then coming back into FileMaker.

                                3. Create a list in a variable of the records you intended to duplicate. Then loop through this list while on the Review layout and distribute the data from each row in the list to each new record. To gather the data, you could loop through each record and set a variable called $List to append the current record's fields onto the existing data in the variable. Then create new records and populate each record with data from the $List variable. This is a bit more complicated but it stays within FileMaker.

                                 

                                So there's a lot of solutions Your original question was about new records and losing context. Try that out. If it works and the new records are created in a reasonable time, you've got your solution. If not, there's other ways to try.

                                1 of 1 people found this helpful
                                • 14. Re: Problem with Script - Looping through Found Set and Losing Currency After Adding Record
                                  betharitzert

                                  Hi Jeremy - I will try the first solution you proposed this evening when I am working on this project.  Thank you so much!