5 Replies Latest reply on Mar 28, 2011 11:32 AM by RonCates

    Record Locking Problem



      Record Locking Problem


      I've been developing a solution for our company for a couple years now. Some time last year I came up with an invoice numbering system for our office use ( Not for use as any kind of key). At the time only a select few were actively using the database. Now we have begun expanding it's use to other people in the office and this has caused a record locking problem when incrimenting the invoice number. I have read many things reguarding how to deal with something like this but at the moment I can't think of any of the techniques. The script is called from within the invoice creation script and looks like this:

      Perform Script[ “User Abort” ]
      Set Field [ Tickets::ticket_num; Right("000" & (Jobs::ticket_num + 1); 4) ]
      Set Field [ Jobs::ticket_num; Jobs::ticket_num + 1 ]

      I was hoping there might be a quick fix. Right now a dialogue pops saying John Doe is modifying the record but after it continues on to create the invoice without changing the number for the next invoice and we end up with duplicate invoice numbers. Any help would be appreciated.



        • 1. Re: Record Locking Problem

          Why can't you just specify an auto-enter serial number field in Tickets for this? What's different about the number from Jobs? I think I can recommend a fairly straight forward fix here but need to understand the link between Jobs and Tickets.

          • 2. Re: Record Locking Problem

            We do multiple "jobs" each year. The company wanted invoice numbers that were based on each job. So, for instance if the job name is Avery 2012 the invoice numbers would be Avery - 12 - 0001 and so on. The number starts with 1 for each jub and incriments only for that job. The field that holds the current number is located in the job record for that reason. The record locking occurs when someone is in the Job record while an invoice is being created.

            • 3. Re: Record Locking Problem

              I was afraid of that. This is a classic example of why I recommend that you don't use such a numbering scheme in a multi-user database.

              Try this first:

              Create a new table for managing the latest job number for each Job. That way edits on the Job table won't create a record lock against another user editing the field. Then I'd put a unique values validation rule on the Ticketnumb field to catch any instances where you still end up with a duplicate value in the field. On rare instances when this might happen, the validation error can pop up, the user can revert and try again and should then be able to get a unique value entered.

              Your JobNumbers table could have two fields: JobID, a number field to link to the jobs and tickets tables and LatestTicketNumb, a number field to increment with each new ticket record for that job.

              Once you have that working, you can try updating the script to check for duplication and try again if the value returned is a duplicate.

              • 4. Re: Record Locking Problem

                Thanks Phil. I was thinking I would have to move that field to a table of it's own. I'll put it together on mMonday.

                • 5. Re: Record Locking Problem

                  Got it, Thanks Phil :)