4 Replies Latest reply on Aug 10, 2013 8:52 AM by DonCollier

    Selecting unique record from a list in a multi user system


      I have done this many times before but I have never had it where two users finish up selecting the same record. This is probably due to the speed dial that I have added to the telemarketing system which means the telemarketers are going through the list a lot quicker and so are more likely to collide.


      basically I have a list of numbers which is recreated on each run of the script and the relevant part of the script is as follows:


      Sort Records [Restore; No dialog] (Keep records in sorted order is checked and the sort is on d_lastcall in ascending order)

      Go to Record/Request/Page [first]

      Set Field [d_lastcallTimeStamp; Get( CurrentTimeStamp))

      Commit Records/Requests []


      My understanding is that if there are two users that are starting the script at the same time one of them is given priority by the server and the next one should get the next number as the Keep records in sorted order is checked so teh first record will drop to being the last record. Unfortunatly there is a lot of occasions where two users are trying to call the same number by between 1 and 5 seconds apart (from my transaction log) .


      Anyone got any ideas. I don't want to slow it down by installing a lock so only one person can be in the table at one time but that is my next option.

        • 1. Re: Selecting unique record from a list in a multi user system

          Hi Don


          Here are my thoughts on this ...


          The sort can take a different amount of time on each machine, so more than one person can get the same list, with the same record at the top.


          Before you set the current time, test to see if that record is already marked with the time, if so then loop through the list until you come across a record in which the time is still empty. Then set the time and use that record for your next call for that user.


          There is still a moment when one user finds a record with an empty time, just before another user  sets it, but the time taken between two script steps will be much smaller than the various sort times across all your users.


          You could set up a single record table, where at the top of your script each user checks that a flag field is empty and loops with a delay if not. If the flag is empty, then the user sets the flag and continues, clearing the flag at the end of the script.  Thus only one user can be running the sort script at any one time.


          Final twist - Don't test for the flag being empty, just turn on error checking, set the flag (without commit) and then test for an error. If the flag is set without error, you have control of the list - if not, then another user has control.  Loop until it's your turn. Clear the flag on exit, then release the record with a Commit Record.


          Best wishes - Alan



          Alan Stirling Technology Ltd, 135 Lisson Grove, London NW1 6UP [UK]

          +44 (0) 20 7724 2456 - alan@ast.fm - www.ast.fm.

          FileMaker Certified Developer for versions 7, 8, 9, 10, 11, 12

          • 2. Re: Selecting unique record from a list in a multi user system

            Thanks Alan . Not quite there though as I have watched the field change as I am still in the record.  I would have expected that the record lock would take care of it but it doesn't. Imediatly after the commit there is another set field that is not committed until a variable is set and the record is exited on a find.  Originally I had a omit record show omitted and it was a lot worse in terms of collisions so I replaced it with the find.

            • 3. Re: Selecting unique record from a list in a multi user system



              A couple of things:

              The resort of a record set happens differently in a script. In my testing (v12) committing a record in the script doesn't trigger the resort, but changing the record focus does. For example, a record commit doesn't trigger the resort, but if the next script step is go to next record, the resort happens. I don't know if this might be involved, but it's worth checking out.


              If I understand, both users are trying to set the same record? If that's the case, you could put the record commit in a loop which you only exit if the commit succeeds. To be safe, you could capture the record ID that you are trying to set, to make sure the record focus doesn't change as a result of the other input. I'd also put a timer in the loop, so that after 20 - 30 seconds some sort of escape action could occur. Perhaps some other procedure/user will have locked the record, and gone to lunch? If it's critical data, you could log the error (record the record ID and data) in your transaction log and go back later to set the data.


              Bob Gossom

              1 of 1 people found this helpful
              • 4. Re: Selecting unique record from a list in a multi user system

                Thanks Bob


                What I don't get is how a record can be amended when someone is already in it.I thought file locking took care of that!


                The change I have made is to grab the ID got to next record then find the ID. that has drastically reduced if not removed the occurance's but I think you are right about the sort not resorting in a script.


                More investigation needed for sure.