1 2 Previous Next 22 Replies Latest reply on May 15, 2017 9:13 AM by KelticGuy

    Code gives spasmodic results

    KelticGuy

      Does anyone know why this piece of code works sometimes but not others? I am using FileMaker Pro13 Advanced.

      It is as if there is a bug associated with the ≤ action.  The problem is spasmodic and happens like this , the code runs when I open the database, I can open it in the morning and it doesn't work and then when I open it in the afternoon it does even although nothing has changed i.e. neither the order date nor the current date are different.  So in the morning the DueFlag will be set to "No" but in the afternoon it will be set to "Yes" (although not always sometimes it works as expected).

       

       

      #Check if order not due yet

      Go to Layout [ “Orders” (SODD) ] Go to Record/Request/Page [ First ]

      Loop

      If [ SODD::OrderDate ≤ Get ( CurrentDate ) ]

      Set Field [ SODD::DueFlag; "Yes" ]

      Else

      Set Field [ SODD::DueFlag; "No" ]

      End If

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

      End Loop

        • 1. Re: Code gives spasmodic results
          Jason Wood

          Are you certain that the field type of SODD::OrderDate is DATE?

           

          I don't see anything to control the found set. Maybe it should have a "Show All Records" at the top, or something else to find the records you're looking at?

           

          You might want to move this post to Discussions-English and always post there in the future. This area seems to be a black hole for new user questions that will never get seen!

          • 2. Re: Code gives spasmodic results
            TSPigeon

            KelticGuy:

             

            Thank you for your post!

             

            As quarfie suggested, I'm also going to move this thread from the FileMaker Developer Challenge 2016 Place to the Discussions Space where you should receive more views and potentially more feedback on this topic!

             

            TSPigeon

            FileMaker, Inc.

            • 3. Re: Code gives spasmodic results
              coherentkris

              Small improvement

               

              #Check if order not due yet

              Set Variable [ $c_date ] = Get ( CurrentDate )

              Go to Layout [ “Orders” (SODD) ] Go to Record/Request/Page [ First ]

              Loop

              If [ SODD::OrderDate ≤ $c_date ]

              Set Field [ SODD::DueFlag; "Yes" ]

              Else

              Set Field [ SODD::DueFlag; "No" ]

              End If

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

              End Loop

              • 4. Re: Code gives spasmodic results
                philmodjunk

                Other options:

                 

                Make "due flag" an unstored calculation and then you don't need to update via a script at all

                 

                perform a find for only those records where OrderDate <= Get ( CurrentDate ) to get a smaller found set to loop through or update with Replace Field Contents.

                 

                But none of that would address why this is not working all of the time. That's most likely due to not having the correct found set of records as this script does nothing to pull up any particular group of records and thus works on whatever found set is "there" at the time it executes.

                • 5. Re: Code gives spasmodic results
                  howardh

                  in your if statement use a variable instead.

                   

                  If the date field SOD in question is part of a relationship it can complicate things especially on the web. using horsepower

                   

                  So set the sod date field as a variable and the other date too. compare them less than or equal to.

                  Build your loop that each time it goes to the next record it resets the variables.

                  so when they are compared there are no relationships behind the scenes slowing things down.

                   

                  ty

                   

                  Howard

                  • 6. Re: Code gives spasmodic results
                    KelticGuy

                    Thanks for taking the time to reply.  I am not working on a found set, I am looking at each record in turn in the whole table.  OrderDate is a date field.

                    Because it sometime works and other times it doesn't, I need a FileMaker Pro developer to look at it, do you know how I get it to them?  Also I am new to the forum how do I move the question to Discussions-English?

                    Thanks again for you help.

                     

                    Regards Gavin

                    • 7. Re: Code gives spasmodic results
                      KelticGuy

                      Thank you for doing that, I'm sure there is a bug here, can you tell me how to report it to FileMaker Pro?

                       

                      Regards Gavin

                      • 8. Re: Code gives spasmodic results
                        howardh

                        hi KelticGuy,

                        Yes, I would be interested in taking a look at the file for you.

                        howard@efficiencyanalysts.com

                        • 9. Re: Code gives spasmodic results
                          philmodjunk

                          "I am not working on a found set,"

                           

                          Well, yes you are. Your found set would be all the records in the table. But there is nothing in your script to insure that you have that set of all records. You need a Show All Records step at the beginning of your script.

                           

                          But looping thru all records is very inefficient. Perform a Find to get just the records that need updating!

                          • 10. Re: Code gives spasmodic results
                            KelticGuy

                            Ok thanks, I will explain in a bit more detail,  I tried to replicate piece of code I wrote in Access which looks at standing orders or direct debits in a table and then automatically updates accounts if they are due.  Unfortunately Filemaker Pro scripts doesn’t have an equivalent to the Goto statement.  To get round this I created several flags in the SODD table, my script then sets these flags sequentially and finally if they are all set to yes the orders are applied to the accounts.  It all works extremely neatly apart from the part where it checks Orderdate against current date, then sometimes it sets the flag as it should and sometimes it doesn’t.  I don’t know how to cut and paste from scripts so I have attached the code as a pdf.

                            Thanks

                             

                            enc. as stated

                            • 11. Re: Code gives spasmodic results
                              philmodjunk

                              This only confirms what I just said moments ago. In access, you recall that every query has a Record Set object?

                               

                              Every layout has an associated found set that is somewhat similar to a record set. Your scripts do nothing to produce a found set of all records and thus there might not be a found set of all records on that layout. There might be found set of only a few or even zero records at the time this script is performed.

                               

                              Not sure what you mean by "there isn't an equivalent go to statement", but what you've written is horribly inefficient. See my previous comments for more on that.

                              • 12. Re: Code gives spasmodic results
                                KelticGuy

                                Ok I will try that

                                • 13. Re: Code gives spasmodic results
                                  KelticGuy

                                  That seem to work, but as it is sporadic I will keep an eye on it and let you know.  Thanks for your help

                                  • 14. Re: Code gives spasmodic results
                                    Programs101

                                    If you don't want to do a normal Find Request or Show All Records and then loop through records ... you could use a global Date field in a relationship to your OrderDate field.  In a script ... set the global field with Get ( CurrentDate ) ... then Go To Related Record with Show Only Related Records / Match Current Record Only and show using your SODD layout.  Then Replace Field Contents using your calculation ... then Show All Records step.  The global Date field can be in any table and you can set it from any layout.

                                    1 2 Previous Next