12 Replies Latest reply on Dec 12, 2011 4:52 PM by philmodjunk

    Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open

    alan.stirling

      Summary

      Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open

      Product

      FileMaker Pro

      Version

      Tested on FileMaker Pro 10 and 11 with FileMaker Server 10 and 11

      Operating system version

      Mac OS 10.6.8 and 10.7.2

      Description of the issue

      If I open the 'Manage Database' dialog box and then open the 'Specify Calculation' dialog box for any calculation field in a particular table, other users on the same system, running scripts containing the 'New Record' script step in the same table, fail to generate New Records.

      Instead, if my script starts with an 'Error Capture On' step, the script skips the 'New Record' step and then continues, overwriting data in the currently selected record. An error code 303 is generated, but there is no warning dialog box.

      Even if the script doesn't have an 'Error Capture On' step, the warning dialog shows at the time the 'New Record' Script step is encountered, which can be after a long script has started, which has been designed to complete without encountering a fatal error halfway through.

      This clandestine data loss is unacceptable!

      Steps to reproduce the problem

      On two machines, open a multi-user database system which is being served using FileMaker Server 10 or 11.

      On one machine open the 'Manage Database' window and then the 'Specify Calculation' dialog box for a calculated field in that table.  Now on the other machine run a script that has been designed to add records to the same table. 

      If the script starts with an 'Error Capture On' step, there will be no sign that the 'New Record' step has been skipped. If there is no 'Error Capture On' step, a dialog box appears, saying that the record cannot be generated because 'User Name' has the 'Manage Database' window open.

      Expected result

      In 2004, when FileMaker Pro 7 was released, all developers were told that we could work within the 'Manage Database' window, without affecting the operation of the specific database for other users.  This was a much flaunted at the time as a benefit of the change from FileMaker 6 (.fp5) to FileMaker 7 (.fp7).

      It is obvious that this situation has now changed - just where is this documented and which other script steps can also be affected - we need to see a list - particularly if the list has more than two entries ('New Record' and "Duplicate Record')

      Actual result

      DATA LOSS!

      In the worst case, this issue will cause record data to be overwritten, with no warnings for the user - what can be worse that that for a database product?

      Exact text of any error message(s) that appear

      With 'Error Capture On' this issue produces an error code of 303.

      With 'Error Capture Off', the issue shows a dialog box explaining that the script step cannot be carried out as a 'User' is working in the 'Manage Database' window (I'm sorry, I didn't write down the actual text of this dialog box).

      Configuration information

      This has been tested on FileMaker Pro 10.0v3 and 11.0v4, with FileMaker Server 10.0.2.206 and 11.0.3.309, on Macintosh machines running Mac OS 10.6.8 and 10.7.2.

      Workaround

      Every instance of the 'New Record' or 'Duplicate Record' script step must be prefixed with  'Error Capture On' and followed by an error test If(Get(LastError) and then a carefully designed script exit procedure.

      This could occur in any script at any time, so all instances of the offending script steps need to be covered.

      Perhaps new systems now have to be designed with a 'Stop New Records' Global Flag, so that any script that might be susceptible to this issue can be halted as soon as it starts once the developer has set this flag -before opening the 'Manage Database' window.

      We urgently need to know if this issue affects other script steps apart from 'New Record' and 'Duplicate Record' and where we were supposed to learn about this initially when the situation was first changed.

        • 1. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
          philmodjunk

          This isn't what you want to hear, but:

          Making such design changes to the structure of a database while others use it is not a good idea.

          A network glitch while you do this could corrupt the database and shut everyone out of your database until you replace with a back up copy.

          I can't agree with you that this is "clandestine data loss". After all, if you don't suppress error messages with a set error capture step, you do get notified that a problem occurred. Can't call that clandestine. It's not what you want to happen, but you did get an error message.

          Here's what's happening. Like any decent database, the system locks other users out of a specific record once a user opens it for editing. In the case of FileMaker, this is usually due to one user putting the cursor into a field of that record. In the case of FileMaker, when you open manage database and then start to add or modify a field definition, fileMaker locks the entire table to prevent edits to records in it. Not only will scripts with new record fail, steps that modify data, such as set field will also fail and users will get error messages popping up if they try to edit data in the table.

          Best practice is to make changes on a separate copy of the file and then deploy an update during a time when you take the server down to prevent edits to the data while deploying the update. This can require data imports and with large tables with many records and many indexed fields, this can take quite a bit of time. I sometimes use a script and run an import overnight to get the job done. You can minimize the need for such lengthy imports with a Convert to Seperation Model--though imports are still needed--just less often.

          Second best practice is to make design changes while no one else is accessing the database. This can require night and week end work, but sometimes you can sneak changes in just before they open for business or just after they close. Make sure your system makes frequent backups if you try this though so you can recover from trouble in the unlikely event that you get a network glitch at just the wrong time.

          • 2. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
            Markus Schneider

            I don't agree with You, Phil.

            It happens when somebody just _watches_ a field definition, means: Nothing changed. And it happens, when a table contains a field with an auto enter SerialNr. - option.

            I catch this using a 'get last error' script-step, but IMHO this is definitely a big bug. Today, we are working over remote connections, etc. - and it happens sooner or later that a developer wants just to check a field - and somebody runs a script that creates a new record...

            I would be very happy when this issue goes into the list of bugs - of FileMaker


            (-:


            markus

            • 3. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
              philmodjunk

              In terms of user actions, how does "just watches" differ from "making changes"? Either way you have the specify calculation dialog open. That said, I'd prefer to not see the table(s) lock until I click OK to dismiss Manage | Database, but don't know if that's really possible or not.

              "Bug", as always, is a subjective term. I include issues in my personal Known Bugs List database that I share here in the forum if it is acknowledged as an issue by a Filemaker Rep so we'll have to wait and see what response you get.

              • 4. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                Markus Schneider

                no need to have the calc' box open - even 'field options' will lock a record. IMHO, it's the 'serialNo' - option that causes this. FileMaker just can't create a new SN

                 

                (-:

                • 5. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                  philmodjunk

                  Either way, you are opening a dialog box to modify the field definition and this appears to be the action that locks the table. Even if this did not happen, FileMaker would still have to lock the table when you click OK to dismiss Manage | Database and apply the changes to the table. While this is a shorter time interval, it still makes this an action that should not be done when others are using the database to edit data or run scripts that do so. Even a split second lock to apply one small change could trigger a problem with a script being run.

                  • 6. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                    RPaulH

                    Alan, thanks for posting the issue we discussed on FM Forums (I think). I posted earlier as well as got my hand slapped so I will comment here.

                    Phil, really? you do not see this as a very problematic issue? The fact that this could happen without any warning or alert at the user end is frightening. What if one of my users runs a looping script while I am looking at the definitions of some fields. We could have any number of incorrect entries and that is not something that I should have to build special mechanism for. Yes error capture is good but not for something that recently cropped up in FMP. I am pretty much in tune with my users as far a making changes around their work but most of the time it is just not possible. Your line "Making such design changes to the structure of a database while others use it is not a good idea." just makes me go 'duh, of course it's not, but that is not the reality of managing a system that has users constantly editing and creating in it, and me needing to make changes & additions to the structure. Previously I had to allow for this infrequently, now I have to stop and think what I am affecting before I deal with any issue or new request.

                    Sorry for some extra ranting, I have too much going on to spend any time trying to get FM to address this.

                    Paul

                    • 7. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                      philmodjunk

                      Please note that I am not an employee of FileMaker Inc. these opinions are my own.

                      I do not see it as a major issue because we should not be making such changes to the database while others are using it in the first place. This risks damaging the databse file and also can lock users out in any number of ways when the change is applied. It'd be nice to check the details of a field's options by opening the dialogs, but since I have access to multiple backup copies that I can safely open and examine, this is a nuisance issue only for me.

                      but that is not the reality of managing a system that has users constantly editing and creating in it, and me needing to make changes & additions to the structure.

                      But is a reality that we have to deal with. The Convert to Seperation Model helps by reducing the down time needed to deploy updates, but we still need to make structural changes to the DB at times when no one is using the system. If that requires updating a copy and using scripted data imports while the system is taken down to avoid additional data changes, then that's what we have to do with the current software. I don't have access to the source code or anything, but find it difficult to image how one would modify the design of filemaker so that structural changes to the database while it is in use did not risk significant issues for your user base. Even if you could do it without locking tables and records even for a moment, the structural change itself could modify the results of executing scripts in unexpected ways--just to name one possible issue.

                      • 8. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                        Markus Schneider

                        well.. let's keep it simple - and let's call it a bug. A big BUG, because it will destroy data.

                        Anybody from FileMaker around, here?

                        :-)

                        • 9. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                          TSGal

                          All:

                          This was originally reported on the forum in 2009 (I'm having trouble locating the original post).  In essence, FileMaker Pro will not allow users to create records while another user is modifying the table schema, especially if a field has the options for auto-enter serial (which was in the original post).  FileMaker Pro will return the correct alert and error code unless Set Error Capture is turned on.  Even if you don't plan to make changes to the schema (or, in your case, the calculation), the potential is there.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                            philmodjunk

                            Folks, after privately discussing this one with TSGal, she suggested that I make a new feature request on the Feature Suggestion Form. I am going to suggest the following right after posting this message:

                            1) When a user action in Manage | Database locks a table or record, FileMaker should check and if the file is in multi-user status, pop up a warning to the developer that their action has locked a table or record. This will warn new users of the potentially disastrous consequences to their database.

                            2) That opening a dialog in Manage | Database NOT lock any tables or records. The lock should occur, if needed, only when the user click OK and this action commits a schema or field change. This would enable to at least "look" at field settings without locking the table in question.

                            3) FileMaker really needs better error handling. Script errors generated during script execution are not handled uniformly, developers cannot use set error capture to suppress all error dialogs and we really need an "on Error" trap like you find in Visual Basic that allows you to build an error handler for all errors of all script steps in a single part of the script instead of checking for an error after every script step.

                            On to the suggestion form.....

                            • 11. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                              Markus Schneider

                              Thank You very much, Phil!

                               

                              When supporting foreign FM-Databases i.e. over remote, this one is a nightmare

                              • 12. Re: Script Step Failure when developer has 'Manage Database - Specify Calculation' Dialog box open
                                philmodjunk

                                Please note that even if FileMaker Inc implemented every suggesting I made, making design changes to fields/tables/relationships in a database while it is in current use by others will still not be a good idea--risking serious loss of data integrity when a table locks out other users at an inopportune moment.