1 2 3 4 Previous Next 47 Replies Latest reply on Aug 15, 2013 3:32 PM by Stephen Huston

    Loop and If condition


      New to FileMaker scripting and need some help troubleshooting the logic :-)

      We have a script to loop through all records to determine whether the new username (which is a calculation of first name and last name) matches with existing records. Here is our script but the logic isn't working out correctly. When it hits the Else statement, rather than looping through the remaining records, it exists the If statement. Any help is appreciate.


      Set variable ($temp, value:volunteer::username)

      GO to record/page/request [first]



      IF(volunteer::username = $temp)

      SHow custom dialog


      GO to record/request/page [next, exit after last]

      GO to layout[print]

      ENd if

      ENd loop

        • 1. Re: Loop and If condition
          Stephen Huston

          You need to move your Set Variable step inside the loop so it is reset for each record to be tested, otherwise it will only be set to the initial record value for the entire script.

          • 2. Re: Loop and If condition

            Adding to Stephen's note, I generally don't like to use the "exit after last" method. Rather I count how many records I'm about to process, set a variable with the count, and exit when I reached the count. It's more reliable. 





            agnes b. riley . filemaker and web development

            FileMaker Business Alliance . FileMaker Technical Network

            www.zerobluetech.com (http://www.zerobluetech.com)



            T 201-299-6223 (NJ) .

            212-842-8830 (NY)






























            917-660-7221 (C)






























            FileMaker Certified in 10 and 11

            people + products + events + todos + invoices + documents = productivity

            • 3. Re: Loop and If condition



              Your Go To Record/Request[] should be after the End If[] but why not use a find instead of looping all records in a table  :-)

              • 4. Re: Loop and If condition
                Stephen Huston

                And to add to Agi's note:


                Placing the Go to Next record step inside one of the If/Else areas of the test loop can risk getting you stuck in the loop without an exit. Either place the Go to Next Record step after the End If, or place an Exit Loop step inside the If Else section which lacks the Go To Next Record step so you don't risk being stuck in the loop endlessing processing the same record.

                • 5. Re: Loop and If condition

                  I'm using a device that only allows me add and not edit?  Strange. I'm on iPad.


                  Anyway I wanted to add that, even if you decide to loop, you aren't changing layouts nor does the variable value change so you do not need to set the variable at all ...  Whether you compare username to record or variable to record, evaluation would be the same.  The only time setting a variable would be beneficial would be if the variable result needed to be calculated or if the value needs to be brought from another table occurrence.

                  • 6. Re: Loop and If condition

                    It doesn't quite make sense, comparing username per field ... What is the purpose of this script please? Each time it matches to the prior record it displays a dialog?  You are getting different responses because the purpose is not clear.  :-)

                    • 7. Re: Loop and If condition

                      I'm not clear as to your desired result in the end, but if you are using FM12 the fastest method would be to use and ExecuteSQL in an if statement. See Below


                      If [ValueCount ( ExecuteSQL ( "SELECT username from volunteer WHERE username = ?" ; "" ; "" ; value:volunteer::username ) ) > 0]

                           Show Dialog


                           Do Something

                      End If


                      Note: username is a reserved key word so the field name would need to be changed to uname or user_name or something.


                      Otherwise I would in pre12 I would just use a find script over looping.

                      • 8. Re: Loop and If condition

                        ariley wrote:


                        I generally don't like to use the "exit after last" method. Rather I count how many records I'm about to process, set a variable with the count, and exit when I reached the count. It's more reliable. 


                        I don't know of any reason why 'Exit after last' would not be reliable.

                        • 9. Re: Loop and If condition

                          As the others have stated, I don't see how "Exit after last" would be unreliable.


                          And it can be easily proven that there are instances when your recommended method would not be reliable. For instance - if another user has deleted one of the records in your set.

                          • 10. Re: Loop and If condition

                            I am assuming that you are checking if the username has any duplicates upon a new volunteer entry and then "doing" a different process for them?


                            In this case, you would be best to create a record identifier (unique ID) in your volunteers table , and a self join where

                            Volunteer::User_Name = Volunteer::User_Name


                            Volunteer::_u_RecID [does not equal] Volunteer::_u_RecID

                            At any time to check of your record is unique, evaluate (or even better, script trigger on the last name field)


                            If (not isValid(your_selfjoin_TO_name::User_Name))


                            If this condition is true, your record is unique and you can then perorm a script to open your custom dialogue.

                            If it is false, you can do your other process (goto your print layout?)

                            Just remember that your new record must be committed before you can evaluate this condition.

                            This may seem like the longwinded way of doing it now, but when you start to work with large numbers of records it is far quicker than evaluating using ExecuteSql , so it's a good habit to learn when performance becomes an issue.


                            On another note, using the goto next record [exit after last] places annoying error messages on your FileMaker server log.

                            To get rid of this, use "ExitLoopIf[Get(RecordNumber) = Get(FoundCount)] instead.

                            • 11. Re: Loop and If condition

                              Wouldn't it be infinitely simpler to put a validation on the field in question of 'Unique" / do not allow user override... as soon as the field is exited, if the name is a duplicate (not unique) the validation kicks in forces a revert of the field to allow another [different] entry.

                              • 12. Re: Loop and If condition

                                A self join on the full name or just validationing that the name is unique will be MUCH faster then looping through all your records to accomplish this.

                                • 13. Re: Loop and If condition

                                  Agreed. Using the op's method will use the standard FileMaker ui for when validation fails.

                                  Using the above technique will allow a quick way to open the related record for details that may already be stored. Some standard FileMaker UI's can get users stuck in some awkward places, so it's best to use your own error trapping and 'take' the user directly to the right places within your solution if something goes wrong.

                                  • 14. Re: Loop and If condition
                                    Stephen Huston

                                    Exit After Last is always reliable, IF it's placed where it has to be executed. So it should not be placed inside only one of the IF/ELSE statements, or it may not get tested at the time of the last record if that record's results don't match the result which triggers it.


                                    I have seen loops get stuck due to a misplaced Go To Next Record / Exit After Last statement not being triggered.

                                    1 2 3 4 Previous Next