9 Replies Latest reply on Feb 6, 2016 7:09 AM by rbickings

    Record Locking Error

    rbickings

      I am a pastor at a church in Bethlehem, PA and have been working on updating an antiquated DB with FMP 14.  Things have been going well until I implemented a children's check-in module for security reasons, along with emergency procedures, allergy info, and parent location.  Each teacher is given an iPad mini in which to check-in their class, and have access to the above information.  I use 6 different iPads with 6 different user names and passwords.  The class roster is a Class/Grade tab containing two filtered portals, one for checked-in, and one for checked-out, that are related to the main "Individuals" table through unique ID's, and class grade.  The check-in is a radial checkbox for "in" or "out".  When the check box is clicked "in", the child moves from the check-out filtered portal, to the check-in filtered portal, and their ID, and EventID is recorded in a table entitled "Attendance".  Thus, at the end of the day, when all the children are picked up and checked out, attendance for that day can be pulled up in an overview tab which provides a portal into this attendance table for that specific date.  I run that most difficult scripts on the server (our DB is hosted by FMP Hosts), allowing the performance to be excellent, with 6 teachers checking in simultaneously.  However, I cannot figure out why when there is heavy use at check in, the teachers will experience record locking errors, stating that the current record is locked because iPad xyz is using it.  How can that be, each one is working on a separate tab, checking in separate children.  I have investigated, tested, and retested the scripts which work great with one or two people, but locks records with multiple users.  The only possibility I could think of is one subroutine that performs a find in the attendance table before saving the child's credentials so there are no duplicates.  But I believe that find mode will not lock the record.

      Sorry so long, but I am desperate to find the answer...any help anyone can give would be great appreciated.

       

      Blessings,

      Pastor Dick Bickings

        • 1. Re: Record Locking Error
          coherentkris

          in a networked solution with multiple users the first user to click into a field obtains record lock and any subsequent users asking for record lock on the same record will be denied. The user who has record lock maintains it until they are done editing the record. You can be done with editing by exiting the control and NOT next entering another control that allows edit of data.This is a data layer feature that is independent of the presentation layer ( layouts and controls). This is how a DBMS is supposed to behave.. you would not want to have two users edit the same record at the same time.

          • 2. Re: Record Locking Error
            ninja

            Howdy,

             

            One thing to check on (basing the suggestion just on your working...)

             

            Each one is working on a separate TAB...but are they on separate RECORDS?  Tabs are a graphical way to present the data...but they may all be on the same record...

             

            If you are setting things via script anyway, have you tried ending the script with "Commit Record" ?

            It won't solve the root issue, but it may lessen it a bit by decreasing dwell time on the record being "shared"...and clearly there is a record being shared.

             

            For troubleshooting the real root cause...get three of the teachers together and start entering attendance for a fake event...when IPad 2 says IPad1 is using the record...look at exactly what IPad1 was doing right then.  What exact action, on what exact record, on what exact table?

             

            Last, making a HUGE assumption on your table schema, including assuming that your check-in layout is based on the Event Table, can you have your "check-in layout" be based on the Teacher>--< Event join table?  That way each teacher will be, by definition, on a different record....

            • 3. Re: Record Locking Error
              wimdecorte

              coherentkris wrote:

               

              in a networked solution with multiple users the first user to click into a field obtains record lock

               

              It's not the clicking into a field that locks the record, but the moment you start editing any field in the record.

              • 4. Re: Record Locking Error
                rbickings

                Thanks so much for each of your responses...let me see if I get this right...this check in module is on a layout, this layout has a tab control with 7 tabs.  Each tab has 2 filtered portals, one for checked out children, and one for checked in children.  Are you all saying that even though each portal record is a separate record, the layout/tab control itself is considered a record so that when any of my teachers checks in or out a child in one tab, and another is doing the same in another tab, they are working in the same record and thus the record locking error?

                 

                Thanks again,

                Pastor Dick Bickings

                • 5. Re: Record Locking Error
                  wimdecorte

                  That is the risk yes.  Whether it actually happens depends a bit on how you do it.

                   

                  But the principle is: if you edit a record in a portal, you lock both that portal record AND the parent record (the record that the tab control layout is on).

                   

                  Note that if two users are on different records for the tab control layout then their portal editing actions will not collide - except perhaps if they try and change the same portal record.

                  • 6. Re: Record Locking Error
                    rbickings

                    Ok thanks, so how do I resolve the issue...do I have them create a new tab control/layout record when they click on the tab?

                    • 7. Re: Record Locking Error
                      ninja

                      Clearing up the words a little...(because they help the understanding)....

                       

                      The check in interface you are using is a layout (I'll call it CheckIn). 

                      That layout is based on a table occurrence (I'll call it Attendance TO),

                      that table occurrence is an instance of a table (Ill call it Event).

                       

                      To use the interface, to interact with the CheckIn layout, you are sitting on a record in the Attendance TO and Event table where the layout is based on.

                      Sitting on that record and interacting with any field on that layout ties up the Event record you are sitting on...and maybe a related record through a portal as well.

                       

                      My guess is that you have one event record that all 6 teachers are on that same record at the same time.

                       

                      If each teacher was sitting on a DIFFERENT record, you would only lock if two teachers were trying to grab the same student..

                       

                      My suggestion (below) to base the layout on a table where each teacher was on a different record stems from this approach.

                       

                      I live about 2 hrs south of you...please feel free to message me if you want to talk "offline".

                      Others here (like wim above) are much more experienced than I...but I'm close by and this seems like a relatively simple issue .

                      I'd offer to swing by if it wasn't for all the snow and ice last night...

                      • 8. Re: Record Locking Error
                        rbickings

                        Thanks Eric, I am off today but will be back at church tomorrow and of course Sunday. SO maybe we can talk this through...what would be the best way to contact you?

                        • 9. Re: Record Locking Error
                          rbickings

                          Hey guys, I was able to figure it out, praise the Lord, in my sleep last night I came up with a solution and fixed it first thing this morning. I would not have understood the problem without your help, thanks from the bottom of my heart!

                           

                          Essentially what I did is give each tab a found set of records in the table instance on which the tab control/layout rides.  So when you click to change a tab, it fires a script trigger which does a find, say on all last names starting with "a", the next tab, "b", and so on so that no one can ever be on the same record at the same time.  It works quickly without any more file locking errors.  I did have to make sure of course, that there were records in the table whose last names began with that letter!


                          Blessings,

                          Pastor Dick Bickings