14 Replies Latest reply on Dec 1, 2011 11:27 AM by CarstenLevin

    Working in "Manage ..." windows with active database

    RPaulH

      Does anyone know where I can find an explanation of what capabilities are available or prohibited when I have either the "Manage Scripts" or "Manage Database" window open. Or if I am performing an activity in one of those areas what can I do and not do? Also, what can my users do and not do. We have some scripts that do not run correctly if I am working on the DB schema. I know the simple stuff as far as two users can't edit the same record, can't edit a layout when user is in it. Is there a reference to all the rules. I called FileMaker and they could not find anything either.

        • 1. Re: Working in "Manage ..." windows with active database
          Mike_Mitchell

          Hello, rpaulh (I'd address you by name, but you didn't indicate one - is it Paul?).

           

          As far as what you can and cannot do in Manage Scripts and Manage Database, I believe that the only restrictions are that only one user at a time can edit a given script, and only one user at a time can edit the schema (i.e. be in Manage Database, actively editing) at a time. Those are the only restrictions I'm aware of (someone will correct me if I am).

           

          I'd be very curious to know about these scripts that "do not run correctly" if you're editing the schema. What exactly are these scripts doing, and how do they malfunction? The reason I ask is that changes to the schema aren't committed until you close the Manage Database window. Hence, being in the process of editing the schema should have no effect on scripts that are run while you're in the process of making your edits.

           

          That said, however, I would be careful about making changes to your scripts, and especially to your schema, on a live database. I have learned from bitter experience (including damaged, corrupted, and otherwise hosed up databases) that you can create issues for yourself without intending to. This is especially true when you're dealing with auto-enter values, lookups, and calculation fields. When the database has to make large scale changes while users are in the process of editing, you can sometimes really mess yourself up, so ... be careful. I would recommend that you make your changes in an offline copy (unless they are very minor), test them, and then import your data into the revised version.

           

          HTH

           

          Mike

          • 2. Re: Working in "Manage ..." windows with active database
            AlanStirling

            Hi there (rpaulh)

             

            As far as I know, there is no definitive document that describes the different issues that can occur when a developer is working within the 'Manage Scripts' or 'Manage Database' dialogue boxes on a live system.

             

            Perhaps this forum is the place to start to document these pitfalls and I would like to kick off by describing an issue that can allow an unsuspecting developer to cause a (possibly) unnoticed change to the data in a live database.

             

            Whilst working on a multi-user database recently, using FileMaker Pro 11.0v4, I decided that I needed to add a new calculation field.  The database had about five active users, in a medical environment and it was not very busy. I had the list of today's visits showing on my screen at the time that I entered 'Manage Database'. I then added a new calculation field. I hesitated as I thought about how I should construct the formula I needed and then noticed the top line of data on my screen be overwritten by new data, probably being entered by one of the staff.  Aaagh!

             

            I cancelled out of 'Manage Database' and took steps to retrieve the lost data (it was early in the day and the staff still remembered the original contents of the overwritten record).

             

            So then I looked at what had happened and realised that the 'New Visit' script had been run by a member of staff (as usual), but the 'New Record' script step had been ignored!  The 3 'Set Field' script steps which followed the 'New Record' command had been applied to the current record, rather than the new record, which my script had expected to be dealing with. This had been followed by data entered by the user.

             

            So the result of all this - If you open a field calculation dialogue box in FileMaker 11, any scripts which try to run the 'New Record' script step at the same time will cause that step to be skipped. An error code is generated, but up to then, I had never expected to have to test for a failed 'New Record' script step - which I do now!

             

            I'm convinced that this hadn't happened previously, so I have to suggest that this is an issue with FileMaker Pro 11 and not with previous versions - although I might be wrong.  I hope others can benefit from this information.

             

            As regards 'Manage Scripts', I am still under the impression that nothing changes in a FileMaker system until the moment a modified script is saved and up to that point all the scripts including any that are currently being edited, run in their last saved state.

             

            Best wishes - Alan Stirling, London UK

             

            Two other unconnected notes:

            1. I ask all fmdev posters to please add their name to the bottom of each post, as it helps us identify who you all are and we can then address you properly at the top of our reply.

            2. Where was it announced that the reply-to email address of emails received from this system was changed on 14th Nov, so that when replying to an email, it is now sent correctly to the list and not just to the original poster as happened previously?

            • 3. Re: Working in "Manage ..." windows with active database
              RPaulH

              Mike and Alan,

              Thanks for the replies. Yes, it is Paul. Alan's answer seems to hit on what my problem is. With Define Database open (don't know if a calculation was also open) we will get (1)new records created in the wrong table; (2)record data changed in the wrong table; (3)record data changed on incorrect records in the correct table, usually when with a script trying to create new records. It may have nothing to do with the Define Scripts being open. While working I often am going back and forth between both "Define" windows and someone will come in with a problem and I don't know which one I was in, but it always seems to be when I am developing in one of the tables referenced by a script being run.

               

              So I probably will have to start working some hours away from the office when no one is working in the DB.

               

              Thanks again,

              Paul

              • 4. Re: Working in "Manage ..." windows with active database
                Stephen Huston

                Paul and Alan,

                 

                I have seen the problem of a script which is supposed to create a new record and then populate fields in the new record doing what you described -- writing the "new" values into an existing record without creating the new record.

                 

                The first time I had to troubleshoot that one was in FM8, 3 or 4 years ago. One client with a slow local/Wi-Fi connection ran into an issue (clearly pre FMv11) where the script continued to execute without having received a new record from the server.

                 

                More recently, I have seen this same issue with a WAN connection failing to produce the "new record" before continuing with script steps which follow the New Record script step.

                 

                In both cases, I had to add looping tests to the script to check that the primary key of the active record was not the same as the primary key captured at the start of the script (before the New Record step). This allowed me to verify that the new record was active before the field values were set by the script.

                 

                On the slow Wi-Fi connection in FM8 this required up to 15 seconds using timed loops. However, on the WAN in FM11, It would fail and time-out with a warning to the user that it couldn't get a new record until I set it to allow 90-seconds! (I  set the time-out message with Exit Script as part of the looping tests.) That solved the problem, but clearly illustrates the issue that a new record can take quite some time to be cached from the server over WAN, and the script won't necessarily wait for it unless you make it run a looping test to verify the new record exists.

                 

                I have not made a practice of adding such tests to all New Record scripts unless I know they may called via a WAN-type connection, in which case they are quite important.

                 

                This is completly undocumented AFAIK, but it has been an issue with slow client connections for several years.

                 

                Stephen Huston

                • 6. Re: Working in "Manage ..." windows with active database

                  Hi Alan,

                   

                  Your observation is intriguing as I often work in the Define Database Window on our live .fp7 file using FMP 11 but have yet to see this issue.

                   

                  Which error code is generated when the New Record script step fails?

                   

                  I've looked through the list of Error codes but could not see one for that situation.

                   

                  Cheers,

                   

                  John

                  • 7. Re: Working in "Manage ..." windows with active database
                    AlanStirling

                    Hi John

                     

                    Thanks for your interest in this problem.

                     

                    I've just logged into the database system in question again, using both FMP10 Advanced and FMP11 Advanced, and tested this again to see which error code is returned.

                     

                    My current scripts just check for 'Get(LastError)'  .

                     

                    However, if I open the Manage Database window of the 'Visits' file in one version of FileMaker Pro, showing a list of fields, the 'New Record' script step works without generating an error, when run in the same file from the other version of FileMaker Pro. 

                     

                    But if I open a Calculation Dialogue box for one of the fields in the 'Visits' File, then when the 'New Visit' script is run in the other version of FMP, the 'New Record' script step fails and triggers an error code of 303 ('Database schema is in use by another user').

                     

                    This occurs whether I'm running the 'New Visit' script in FMP10 and the 'Manage Database - Calculation Field' dialogue is open in FMP11 or vice-versa.

                     

                    The system is Mac based, with iMacs running FileMaker Pro 10/11 and a Mac Mini 2.53 GHz server with 4 GB RAM with Mac OS 10.6.7 running FileMaker Server 10.0.2.206.

                     

                    In searching for the script that I had previously modified with the 'New Record' test, I used 2EmpowerFM Toolbox, looking for 'New Record' - I came across many other scripts that also contain that script step - I will now have to go though all the files and add an error check to protect each 'New Record' step! 

                     

                    And quite often there may not be a simple way to abort the script if an error is detected, because the error may occur in the middle of a series of 'New Record' steps …

                     

                    This is the first time I've found a real show-stopper for developing on live databases (which I've been doing without issue since 1991!). It is also in direct contradiction to the information provided to all developers when FileMaker Pro 7 was released in 2004, when we were told that only when we click the 'OK' button of the 'Manage Database' dialogue box would our changes be saved to the database and prior to that we could work within the 'Manage Database' area without affecting the operation of the file in question.

                     

                    I may decide to add a 'Stop New Records' Global flag to systems that I work on regularly, so that I can control when users would be able to enter new records - so that I can continue to work uninterrupted when necessary.

                     

                    I will register a bug report with FileMaker so as to try to find out more information about this.

                     

                    Best wishes - Alan Stirling, London UK

                     

                    (Hope to meet you again next year, John)

                    • 8. Re: Working in "Manage ..." windows with active database
                      Mike_Mitchell

                      Alan -

                       

                      This is most curious. I've never seen an issue of the sort you're describing - ever. (I've successfully corrupted databases before by working in the Manage Database dialog with active users, but that's a different story.) Wow.

                       

                      Just out of curiosity - when you are working on these calculations, what kinds of fields are being affected? Are they unstored calculations, stored calculations, auto-enters? Because this would be really good information to have ...

                       

                      Thanks for the input!

                       

                      Mike

                      • 9. Re: Working in "Manage ..." windows with active database
                        AlanStirling

                        Hi Mike

                         

                        I've just retested this issue and I'm convinced that the type of calculation is not important, it's caused by just having the 'Specify Calculation' dialog open at the same time as a script tries to run a 'New Record' script step in the same table.

                         

                        If the script does not have an 'Error Capture On' script step, then FileMaker will display a dialog box, explaining that a 'User' has the 'Manage Database' window open and records cannot be created. (My apologies, but this is not exactly what the dialog box states, I've had to guess, since I didn't write down the full text.)

                         

                        I've entered a full bug report, so let us see what happens now ...

                         

                        Best wishes - Alan Stirling

                        • 10. Re: Working in "Manage ..." windows with active database
                          Mike_Mitchell

                          Let's hope that bug report goes somewhere.

                           

                          In the meantime, I guess we need to be really careful about scripting record creation with error capture set to On, huh?

                           

                          Thanks for this revelation.

                           

                          Mike

                          • 11. Re: Working in "Manage ..." windows with active database
                            RPaulH

                            Alan, thanks for the input and doing the legwork on testing this. I am going to take a copy of my file and test on it today, but I'm sure it will yield the same results. BTW did you test with both v10 & v11? I am only running 11 here and I have never experienced this until the last 4 months on any version.

                             

                            We will hope the bug report generates some response soon.

                            Paul

                            • 12. Re: Working in "Manage ..." windows with active database
                              CarstenLevin

                              Once upon a time a naive developer went into the Relational Diagramme, using FileMaker Pro 8, and FileMaker Server 8.

                              The client computer crashed while editing just a little bit in the RD. It was the FireWall with the VPN connection that broke down.

                              On that sad day there was more than 40 users active in that system.

                              At first everything seemed fine. But then we got a call when some users had locked out during lunch. After eating turkey sandwich and french cheese they where unable to log in again.

                              The naive developer went into the system ... well, he could not.

                              All the other users was still working - those who did not log out for lunch.

                              They where asked to log out and the server was closed down.

                              After filerecovery we succeded reopening the data file ... it looked relatively OK, data could be viewed/browsed, if I recall right. But when we opened the RD it was INTERELY EMPTY.

                              Lesson learned: Do not edit the RD or probably also fields/tables while the system is in use.

                               

                              And yes: We had a backup that was only 40-50 minutes old.

                               

                              My best advice:

                              • Develop in a system that is stored on server, backed up very often, never crashed.
                              • Develop in a system thats not in use.
                              • Go back to the previous backup if anything happens in the running version.
                              • Keep the users in an production system and import data into a copy of the development version when you want to deploy a new version.

                               

                              Well ... any better advice or am I "to consequent"?

                              • 13. Re: Working in "Manage ..." windows with active database
                                Stephen Huston

                                It sounds like the "Once upon a time" event was using a VPN connection while doing development, which brings in another level of risk.

                                 

                                Fortunately for most of us "In-House" developers, we're generally running on more reliable networks, and most of us MUST work on llive systems.

                                 

                                While I prefer to work offline if its possible, in fact, I've been in-house on a 24/7/366 live system with anywhere from 25 to 75 clients in the system all the time, and no issues in my 2 years of that (so far).

                                 

                                Yep, we do scheduled backups throughout the day anyway.

                                • 14. Re: Working in "Manage ..." windows with active database
                                  CarstenLevin

                                  Stephen Huston wrote:

                                   

                                  It sounds like the "Once upon a time" event was using a VPN connection while doing development, which brings in another level of risk.

                                   

                                   

                                   

                                  How could you guess:-)