6 Replies Latest reply on May 1, 2014 9:41 AM by philmodjunk

    Scripting "Set Field" Loop in Related Table

    Robbie_1

      Title

      Scripting "Set Field" Loop in Related Table

      Post

           Hi All,

           I'm trying to create a script that will take information entered during the first part of the script, go to a related table, and run a loop to fill that information into a the related table's set of fields using variables established earlier in the script. Thus far everything seems to work okay, except for when the script get to the related record it is only changing the fields in one record, instead of looping through the whole found set. 

           The loop aspect of the script looks like this:

           *******************

           Go to Related Record [Show only related records; From table: "Example_Test"; External; Using layout: "Example_Test" (Example_Test)]

           Go to Record/Request/Page [First]

           Loop

           Set Field [Example_Test::Example_Test_Field; "Scan Pulled"]

           Set Field [Example_Test::Example_Test_Field2; $variable]

           Go to Record/Request/Page [Next; Exit after last]

           End Loop

           Go to Layout [original layout]

           *******************

           What am I doing wrong?

           Thanks for the help. 

             

        • 1. Re: Scripting "Set Field" Loop in Related Table
          FentonJones

                

               Are you sure that the relationship is not just going to 1 record in the other table? Because I don't really see anything wrong other than that (though I've been known to miss things). If you have FileMaker Pro Advanced you could watch this happen. If you don't, you could look at the other table. If it only shows a found set of 1 after this script (and you know the relationship should have matched more), that would show the problem is the relationship itself.
          • 2. Re: Scripting "Set Field" Loop in Related Table
            philmodjunk

                 Like Fenton, I don't see any obvious errors in your loop. You might put a pause step immediately after the Go to Related records step to check and see what records are actually in your found set at the time the script executes. IT's possible that either your relationship is not what you thought or that you have the wrong target Tutorial: What are Table Occurrences? specified in the Go To Related Records step.

                 Other observations not directly tied to the immediate problem:

                 This script looks dangerous. If there are no related records, the Go To Related Records step does nothing but silently return an error code. The following script steps will execute nonetheless, but now they are on the wrong layout and with the wrong set of records. The results could be catastrophic for your data. If there is even the slightest chance that this script could be executed when there are no related records, the script should be modified to first check for the existence of related records or use Get (LastError) in an if step immediately after the Go To Related Records step to keep the rest of the script from executing if there are no related records.

            • 3. Re: Scripting "Set Field" Loop in Related Table
              Robbie_1

                   Okay, so I added the script step Pause/Resume Script [Indefinitely] to attempt to narrow down how many related records the script was pulling up, except now my new issue is that it will not pull up any related records at all. Nothing else in my script has changed, except that I disabled my loop and added the pause. 

                   New script steps look like this....

                   *******

                   Go to Related Record [Show only related records; From table: "Example_Test"; External; Using layout: "Example_Test" (Example_Test)]

                   Go to Record/Request/Page [First]

                   Pause/Resume Script [Indefinitely]

                   *******

                   The result is that my layout does not change, and it just selects the first record in that layout, where before it at least went to the appropriate related table. If I wasn't confused before....

                   So I pulled up the relationship, triple checked the relationship criteria, and everything seems in order/has not changed from before. I'm wondering if the problem could be stemming from the fact that the TO I'm relating to is an external data source from a remote file?? Otherwise I'm at a bit of a loss.

                   Any insight would be helpful. In the meantime I'll try to create a demo FMDB to help me better demonstrate the issue.  

                   Thanks!

                    

              • 4. Re: Scripting "Set Field" Loop in Related Table
                philmodjunk

                     That indicates that there are no related records to go to. You'll need to check your relationship definition and the values in your match fields. Either that or the "table" you specified for the Go to Related Records step is the wrong table occurrence.

                • 5. Re: Scripting "Set Field" Loop in Related Table
                  Robbie_1

                       Did some digging, discovered the issue. We changed the security settings to require a username and password on the remote DB. Once that was removed and the DB was opened up it allowed the script to pull up the related records. 

                       So, back on track. Now...

                       Used the "Pause/Resume Script [Indefinitely]" script step to establish the GTRR script step is in fact pulling up both of the related test records, and going to the first related record in the found set. However, it is still only changing the fields in one record instead of looping through the whole found set.

                       Thoughts?

                       Thanks again, and sorry for the hiccup!

                  • 6. Re: Scripting "Set Field" Loop in Related Table
                    philmodjunk

                         I don't see how that is possible from the script shown here.

                         Move the pause to be just after the Loop step.

                         Now the script will pause with each iteration of the loop and you can click "continue" to see the next go round of the looped part of your script and you'll be able to watch the current record and the values in its fields change as the script loops.