Concurrent Data Entry in Forms

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
We created a form using for staff to register for daily available slots. Unfortunately, when we tested the form, we did not test it for concurrent data entry. And lo and behold, when we opened the registration for the event, we found out that when two users were working on the form at the same time, the form rules could not stop them from passing the limit (e.g., if one slot was available two people could sign up for it, provided that when they opened the form to register, one slot was available). Is there a way to fix this so that two people cannot register for the same slot?
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
  • anxious

Posted 3 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
Are you using only one table? Or, do you have a parent table where each record represents a time slot? 

You can set a child registration record limit of one per parent time slot. 

Could you give that a try? 
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
How do I set up that limit?

Perhaps I was not clear. We have two tables. One table is called Event Dates. The other is Event Registration Request table (the latter is a child of the former). Each event date has a total number of slots that can be requested (one event per day, with a limited number of slots available. Slots may be added if money is available) . One user can request more than 1 slot on a certain date provided that the slots are still available. We created a formula field that calculates slots still available for each day. We have a rule set to abort the save and display a message indicating that the "Number of requested slots is greater than the number of slots available for this date". The form works fine if I go in the form and try to request 2 slots when only 1 is available. But if I and a colleague go in at the same time and request 1 slot each, the form can't stop us.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
I think this problem is a little be harder than you may think. Basically you need a way to create a lock on a record to the exclusion of others that may be attempting to create a lock on the same record. In Computer Science this lock is called a mutex and it can be a difficult subject to understand and implement.

I haven't worked out all the details but you might be able to create a lock on a record (the time slot) by associating a unique ID for the record. Then all users that might want to attempt to lock that record would create record in a secondary lock table using API_AddRecord where there is a field that has the unique property set. The first API call that reaches QuickBase will successfully create the record in the second table but all other users would receive Error Code 51 (Attempting to add a non-unique value to a field marked "unique"). Now the user that successfully created the record in the secondary table can be allowed through client side logic to claim / edit the desired record in the original table and subsequently delete the record in the secondary table (so it does not grow endlessly).
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
This would be an elegant solution if I can do it (which sounds hard for me :) ). In the meantime, I will attempt Alex solution below. Thank you for thinking through this. I am glad to know that there wasn't any native solution ("check") I did overlook. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
I will probably create a demo of this and I think you will be stunned by how short the code is. However, I go through a process that has a lot of steps when creating a demo and I a have a few others in the pipeline and other work to do. Hang on and ping me if I don't get back to you.
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
Hello, were you able to do anything about this? Thanks. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
It’s not a prevention, but you can fire an email if the course is over Subscribed.
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
 I think this was an unlikely incidence because we asked staff to register during a group meeting so they all got on the form at the same time! I will see if I can try the solution below from Alex. 
Well, the root of the problem is the difference between table states when the form is loaded and when it's saved, I think? Basically more than one user opening a registration form and sitting on that page, after which there is no subsequent check against availability.

So you could narrow your problem window quite a bit by performing another check after the registration form is filled out. To avoid too much UI aggravation, maybe do something similar to a multi-stage online payment - at the bottom of the registration form, rather than a SUBMIT or REGISTER button, implement a PROCEED button (or even call it CHECK AVAILABILITY) that goes to another form page displaying/confirming the information they've entered, with the REGISTER button on that page - or instead of the REGISTER button, display a 'no more availability' message if someone else has already registered that slot.
Photo of Gus M

Gus M

  • 242 Points 100 badge 2x thumb
Thank you. This I think would be the doable solution for the future unless I can figure out Dan's solution above. I will try and let you know.
Photo of Gus M

Gus M

  • 252 Points 250 badge 2x thumb
Hello all, any solution? I am really still puzzled. Does Quickbase not properly do locking at the database level? I have tried the check availability idea and hoped that people will not check availability and then sit and create the same problem again.