10 Replies Latest reply on Oct 24, 2013 8:36 AM by philmodjunk

    Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server

    jsalzer

      Summary

      Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server

      Product

      FileMaker Server

      Version

      FMP Server 12 Advanced 12.04.405

      Description of the issue

      Several years ago, there was a release of FileMaker Pro 7, 8, or 9 (I don’t recall which) that contained a bug where searches on unstored calculation fields were falsely returning no found records because the server and the desktop client were not correctly negotiating which was going to perform the calculations (leading to the search being done on empty fields and returning no results).  The issue was discovered and resolved in the next update.

      A variation of this bug seems to have resurfaced in FileMaker Pro 12.  When the server is performing a script on a timer schedule that includes a search on unstored calculation fields, that script is incorrectly returning no results (a found set of 0 records), when the same script run by the desktop client does return results.

      Steps to reproduce the problem

      Create a script that performs a search on an unstored calculation field (such as a Count () performed on a related table).  Include a SendMail script step to let you know how many results are found.  Run the script from the desktop client to discover that there are valid search results (a non-zero found set).  Run the script from the server set on a schedule and discover that the script incorrectly returns no valid results.

      Expected result

      In the specific case that brought up the problem, we expected the script to run on schedule and to send e-mails to staff members who met certain criteria based on unstored calculation fields.  No e-mails were being sent.

      Actual result

      When all other troubleshooting proved fruitless (we initially suspected our SMTP settings as the problem), we included a SendMail script step to inform us how many records were being returned by the search.  This informed us that no records were being returned by the search.  This was not the accurate result, as verified by the fact that the same script, when run by the desktop client, did return the accurate results and functioned as expected.

      Exact text of any error message(s) that appear

      N/A

      Configuration information

      N/A

      Workaround

      N/A

        • 1. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
          philmodjunk

               I can't confirm this one simply due to not having a working copy of Server 12 available to use for testing it.

               But a workaround to test would be to use a robot file run from a system level scheduler such as windows scheduled tasks to do this operation. Since this takes place from a client perspective instead of server's "sort of host" environment, it should avoid this issue and at least provide you with something that you can use right away.

          • 2. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
            Fred(CH)
                 Hi John,
                  
                 Could you provide more details about the calculation formulas and the finding steps ?
                  
                 Because I was interested by your report and did some tests, but couldn't replicate :
                  
                 Created a small test file with two tables and one relationship.
                 Defined 2 unstored calculations from parent table : List (result :Text) and Count.
                 Entered few parents and child records, specifically to get very clear results.
                 Created a script that proceed to several requests and write the results on a field.
                  
                 However, when my script was executed server-side, it provided the exact same results than by a desktop execution.
                  
                 This script tested both the classic way that switch to Search Mode, Define Field and then Execute, but also the "one step way" Execute with defined criterias on it.
                 Searched also the beginning of a word through the 'List' field.
                 Searched both an exact value on the 'Count' field and relative value like >2 .
                 Tried to combine two unstored criterias at a time (AND).
                  
                 Please note I am not saying there isn't a bug, but it may be more malicious that supposed on your first post...
                  
            My Server's config is following :
                 Mac mini Server mid-2010
                 Mac OS X 10.8.4
                 8 Go RAM
                 FileMaker Server Advanced 12.0.4.405 installed
                  
                 Bye, Fred
            • 3. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
              jsalzer

                   Phil - Thank you for the workaround.  Should all else fail, I'll give this a try.

                    

                   Fred - Like Phil, I do not personally have access to a server or to FMPA Server.  I've been troubleshooting this issue with my client via e-mail, so it took us quite a while just to identify the discrepancy in search results.  Without direct access, I just don't have the ability to do the range of test scenarios I would normally do when something isn't working as expected.  So, I greatly appreciate your effort and help identifying the issue.  Feel free to debunk my original theory.  :)

                    

                   The original script was written using the classic method.  We also tried a script placing all of the search criteria within the PerformFind[] command, and received the exact same results both when the script was initiated by the desktop client (the correct results) and scheduled on the server (the incorrect empty results).  The database solution relates to staff activity logs and time off requests, and this script in particular finds users who have subordinates who have submitted logs or time off requests that need to be reviewed and approved.  So, we're looking at:

                    

                   Enter Browse Mode

                   Allow User Abort [Off]

                   Set Error Capture [On]

                   Go to Layout [Appropriate Layout]

                   Enter Find Mode []

                   Set Field [Users::CountOfOverdueActivityLogs_Open_Overdue_MySupervisees; ">0"]

                   New Record/Request

                   Set Field [Users::CountOfTORequests_Modified_MySupervisees; ">0"]

                   New Record/Request

                   Set Field [Users::CountOfTORequests_Requested_MySupervisees; ">0"]

                   New Record/Request

                   Set Field [Users::CountOfTORequests_Withdrawn_MySupervisees; ">0"]

                   New Record/Request

                   Set Field [Users::CountOfWeeklySubs_OvertimeSubmitted_MySupervisees; ">0"]

                   New Record/Request

                   Set Field [Users::CountOfWeeklySubs_Submitted_MySupervisees; ">0"]

                   Perform Find []

                   At this point, no search results are found, but if they were, an e-mail would be sent to each supervisor in the found set telling them that they have records to review.

                    

                   Each of the search fields are unstored calculations that perform Count functions on a table occurrence that is related through two relationships.  For example,

                    

                   Users::CountOfTORequests_Requested_MySupervisees = Count (TORequestsFromUsers_ThruSupervisors_StatusIsRequested::RecordID)

                    

                   In this example, the first relationship has the table Users on the left and the table UsersFromUsers_SupervisorID on the right.  The relationship has criteria:

                   Users::StaffID = UsersFromUsers_SupervisorID::SupervisorID

                    

                   So,that relationship identifies the user's subordinates.  The second relationship would be table UsersFromUsers_SupervisorID on the left and TORequestsFromUsers_ThruSupervisors_StatusIsRequested on the right.  The relationship has criteria:

                   UsersFromUsers_SupervisorID::AlwaysRequested = TORequestsFromUsers_ThruSupervisors_StatusIsRequested::Status

                   AND

                   UsersFromUsers_SupervisorID::StaffID = TORequestsFromUsers_ThruSupervisors_StatusIsRequested::StaffID

                    

                   The field Users::AlwaysRequested is a global calculation field set to equal the text "Requested" (serving as a constant).  All other fields are stored text fields.

                    

                   Probably the only other relevant piece of information I can think of is that they are running the Windows version of FMPA Server.  Please let me know if any other information would be helpful.  I appreciate any insight that you may have.

                    

                   Thanks!

                   John.

              • 4. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                Fred(CH)

                     Thanks for your detailed reply John !

                     When i red your script, i thought the problem may be related to the second level relationship or/and to the New Record/Request step.

                     Unfortunately, after more testing on these directions, i am still unable to replicate crying.

                     If you have opportunity to come back by your users, you could execute a test on a brand new file and see if you can get the same error...

                     Sorry, John...

                     Bye, Fred

                • 5. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                  jsalzer

                       Fred,

                       I greatly appreciate the effort that you put in to this.  I'm sure that it wasn't a quick recreation.  I'm sending gold stars your way.  :)

                       I've sent the client a set of instructions of how to change the global constant fields used in the relationship to stored calculated values to see if that helps - it sounded like the next viable source of problems to me.  If they report back that it doesn't help, I'll try your suggestion of sending them a fresh file.  If that fails, we'll fall back on Phil's idea.

                       At this point, I'm more curious than anything if it would fail with someone else using FMP Server for Windows.

                       Anywho - thanks for providing us with much more information than we had before!

                       John.

                  • 6. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                    Fred(CH)

                         Thanks John for the gold stars, but i think i am a dummy :

                         

                    I've sent the client a set of instructions of how to change the global constant fields used in the relationship to stored calculated values to see if that helps - it sounded like the next viable source of problems to me.

                    I AM NOW ABLE TO REPLICATE !!! laugh

                    This KB article  and FM Help states that the global fields, even calculated, are not stored by a client application. Only the Host is storing the values on globals and only when closing the file.

                         However, the case we are dealing here is a bit different. I modified my test structure to include the global storage element.

                         First, i defined one calculation (result : Number, Global, Do not eval… unchecked) that returned 1 on both tables and modified the relationship accordingly.

                         My records still were related after this modification. And the script still was executed successfully on both sides. I juste noticed that the global calc was updated first, when i defined it from the client the first time, but not after, when tried to modify his formula "just to see". But that was normal and anyways, i noticed that both client and server shared always the same value, even this value was not up to date accordingly to the actual formula. So : perfect world...

                         But then, i modified this schema accordingly to yours :

                           
                    1.           Modified the field on Child table to store it locally instead of globally and typed it from Calc to Number. We will call this field 'Display'.
                    2.      
                    3.           I add a second TO from this Child table specifically allowing entering rows on a second portal.
                    4.      
                    5.           Defined an according new relationship (auto creation enabled) without the Display field as a key.
                    6.      
                    7.           Added the new portal from the new TO on my layout allowing to enter new related record.
                    8.      
                    9.           Defined the Display field as Enterable on the new portal. Thus, It can have 1 or 0 depending if i want to display it on the first original portal.
                    10.      
                    11.           Relaunched the script…

                          

                    Results :

                         Client side, the Child records were always related to the Parent Record when Child::Display was defined to 1.

                         Server side, the Child records were never related to the Parent Record even Child::Display was defined to 1.

                         However, i could notice that the Global value on parent table was correctly red by the server.

                    Summary :

                         When a relationship is using a global calc on the left and a local field on the right, Scripts server side cannot perform the relationship.

                         Bye, Fred

                    • 7. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                      Fred(CH)

                           Grrrrr.

                           But the GTRR works fine !

                           A summary field that totalize the count calc field too !

                           Hard life...

                           However, searches about calc fields with summary functions on parent calc, fail when performed server-side if...

                      ...the relationship is using a global calc on the parent and a local field on the child.

                           Sorry, Fred
                            
                      PS : Go to "Siesta"
                      • 8. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                        philmodjunk

                             A known Issue, for More Information see:     Client/Server inconsistent behavior in record access calculation

                             This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

                             It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

                              

                        • 9. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                          jsalzer

                               Fred - Thanks a ton!  It's great to know I'm not nuts - or horribly incompetent.  :)

                               Phil - The post you referenced (and the predecessor it referenced) discussed the problem revolving around using global variables in find mode and seemed (to my limited technical speak) to point to using global fields as the solution.  We are experiencing the problem with a global field losing its value when in find mode.  Just in case that helps with maintaining the known issues list.

                               Speaking of which, thanks for the database.  I'm sure it will safe me many hours of frustration the next time I run into one of these.

                               John.

                                

                          • 10. Re: Bug - Finds on Unstored Calculations within Scheduled Scripts Run by Server
                            philmodjunk
                                 

                                      Speaking of which, thanks for the database.  I'm sure it will safe me many hours of frustration the next time I run into one of these.

                                 You're welcome. The many folks expressing frustration over having no means to research past bug reports, and thus spending hours documenting a bug only to be told that it's already been reported is what prompted me to create this tool. Please note that it is much better at logging the discovery of a bug than it is at logging the correction of the bug when a future version/update is released.