1 2 Previous Next 19 Replies Latest reply on Oct 3, 2016 10:59 AM by klawsure

    Exit Loop If Logic Not Working

    klawsure

      Hello - I have a script that will not Exit Loop If as expected (screen shot below). Any help would be greatly appreciated!!!

       

      What I am trying to do...

      Go to table 1 and get the 1st record ID

      Go to table 2 and get the 1st record ID

      Go to table 3, create a new record, and set the table 1 and table 2 1st IDs for that record.

      Then loop through and for each table 1 and table 2 ID create pairs in table 3.

      So, if table 1 has 3 records with IDs 1, 2, and 3, and table 2 has 3 records with IDs A, B, and C. I want to create 9 new records in table 3.

      Table 3
      T1IDT2ID
      1A
      1B
      1C
      2A
      2B
      2C
      3A
      3B
      3C

        • 1. Re: Exit Loop If Logic Not Working
          scottworld

          It doesn't look like you are advancing to the next record by using "Go To Record [Next]".

          • 2. Re: Exit Loop If Logic Not Working
            beverly

            There is a thread on this:

             

            so you would do this

            Let (

            [ _q = "

            SELECT table1.ID, table2.ID

            FROM table1, table2

            "

            ; _r = ExecuteSQL( _q

            ; Char(9) ; Char(13)

            )

             

            ]; _r

            )

             

            If you place into a field the result and copy. Paste into a text editor then import as tab-delimited. All your records will be created!

             

            beverly

            • 3. Re: Exit Loop If Logic Not Working
              klawsure

              Thank you for the quick response. I am not sure where I would insert this into the script. I have tried many combinations using the record request, but none have worked except to advance only from the first record to the next. So I end up with only the first two IDs from table 1 and table 2. Every time a layout is entered it goes to record 1.

              • 4. Re: Exit Loop If Logic Not Working
                klawsure

                Thank you for your reply. I had a solution that used SQL and a series of import/export scripts. However, I need to deploy this solution in WebDirect and as a runtime. WebDirect will not support the import/export scripts, and Execute SQL is not supported in the runtime. The looping script is the only other solution that I can think of.

                • 5. Re: Exit Loop If Logic Not Working
                  scottworld

                  For me, and this is just my own individual personal opinion, I'm not a fan of the trend in these forums to try to convince new FileMaker developers to use SQL. Almost all of the FileMaker developers that I know specifically chose FileMaker because it is easy, intuitive, is as familiar as speaking the English language, and so that they could AVOID things like SQL.

                  • 6. Re: Exit Loop If Logic Not Working
                    scottworld

                    I would recommend using FileMaker Pro Advanced. In FileMaker Pro Advanced, you can use the built-in Script Debugger to run through your script one line at a time, so you can see specifically where things are failing for you and where you should insert the "Go To Record [Next]" script step.

                    • 7. Re: Exit Loop If Logic Not Working
                      beverly

                      this is ExecuteSQL() - a function and should be able to be used everywhere (NO ODBC needed). But you are correct in the import/export limitations.

                       

                      Looping will also do just fine, it's what we used before ExecuteSQL().

                      beverly

                      • 8. Re: Exit Loop If Logic Not Working
                        beverly

                        for a quick way (one time) the ExecuteSQL() was supplied, so nothing to "learn". As this is not a one-time usage, it doesn't matter. However, teaching new things is never a bad idea. It can always be ignored.

                        • 9. Re: Exit Loop If Logic Not Working
                          klawsure

                          I have used the debugger and it appears that it is failing at exit loop if step. It s moving past both exit loop if statement and going to end loop. I have a functional script using SQL to set the counts and variable, but under a different data structure. I cant figure out why it is not working here.

                          • 10. Re: Exit Loop If Logic Not Working
                            mw777rcc

                            Let's answer the man's question rather than debate the pros and cons of SQL. lol

                             

                            Ok add the following lines:

                             

                            1) After line 8 “Go to Layout”

                            If [ $i ]

                              Go to record/request [next exit after last]

                            End If

                             

                            2) After line 12 “Go to Layout”

                            If [ $j ]

                              Go to record/request [next exit after last]

                            else

                                 Go to record/request [first]

                            End If

                             

                            Edits explained:

                            The if statement is to keep this from happening on the first run through each time.  Since you are setting/resetting your counter variables to zero (0) each time then you know that your first run through then your counter is zero (also meaning boolean false) the rest of the time they will have a number (boolean true) which will run the code inside the if statement.

                             

                            Go to next record is getting you out of the loop you are in.

                             

                            Go to first record is to reset your context in that table (your inner loop) so that you match each of those records with the second and so on.  Without this line you will get the following:

                            1 A, 1 B, 1 C, 2 C, 3 C, etc..

                             

                            Right now from this code you are getting a table full of 1 A, 1 A, I would assume

                             

                            Edited: added else go to first record and explination.

                            • 11. Re: Exit Loop If Logic Not Working
                              klawsure

                              I don't see any reason why the loop script would not work as a solution. It seems like a straight forward process, but I have tried over a dozen combinations and none of them work. I am at a loss for what to try next.

                              • 12. Re: Exit Loop If Logic Not Working
                                scottworld

                                Oh yes, I totally agree that learning new things should always be a part of our daily rituals! I try to learn 3 new things every day, and I make a conscious effort to re-learn things that I thought I once new because I probably didn't learn it right the first time around. That's why I keep re-reading the FileMaker 15 documentation over & over again. It's just my personal preference that I don't bring up SQL to brand new developers because I'm worried that I may scare them away from our beautiful, simple, dead-simple and dead-easy platform of intuitiveness. That's just my own way of doing things.

                                • 13. Re: Exit Loop If Logic Not Working
                                  scottworld

                                  It's because you're not advancing to the next record, so you're getting yourself stuck in your own loop because it keeps operating on the exact same record over & over again.

                                  • 14. Re: Exit Loop If Logic Not Working
                                    klawsure

                                    OK. So this gives me 1A, 1B, 1B. When I step through the script each time I enter the criteria (table 2) layout it is on the 1st record. It never goes back to the KPI layout (table 1). It terminates once it hits the record count for criteria (table 2). I did change how it was counting records. The value count from the script in the original post only resulted in 1A.

                                    1 2 Previous Next