Hi, I have run into a scenario with a form that I am struggling with. I created a signup form for a client that allows people to sign up for a volunteer shift. There are a certain number of openings for any given shift. I have a related list that contains all of the available shifts and the number of openings. When a request is submitted, a workflow subtracts one available shift from the related list of openings. On the request form, you choose a shift, and a calculated control does a lookup to the related list and displays the number of openings.
The calculated control formula is as follows:
lookup("Shift Openings", "ID", Shift , "Openings")
If the number of shifts is less than 1, a formatting rule displays the number of openings bold and in red, and then a validation rule will prevent the person from submitting it.
This was working perfectly well and I didn't see the gap in the logic until we ran into a scenario today...
Two people were filling out a request for the exact same shift at the same time. The calculated control on the form looked at the related list and displayed 1 opening. Person A, then submitted their form and the workflow subtracted 1 opening. Person B had selected the shift prior to Person A's workflow subtracting 1 opening, so Person B's form was also showing 1 opening in the calculated control. Both were submitted and the result was the shift was overbooked and now has -1 available.
Thank you very much!
Could you put a Validation Rule on the Save button using the lookup function to check whether the slot has been booked as the submit button is pressed?
I love where you're going with this. For some reason, it hadn't occurred to me to do it that way. I added this condition as a validation rule on the Submit button:
lessThan(lookup("Shift Openings", "ID", Shift, "Openings"), 1)
It isn't doing exactly what I need it to do though. It seems like it isn't doing the lookup at the time I click the button, but rather when the shift is selected. Those that appear with less than 1 opening are invalid, those that have 1 or more are submitting correctly, but when I start filling out the form (with one opening in the related list), and then remove the available openings, it still lets them through and the result is the negative number of openings again.
I am not sure if I am setting it up incorrectly, or if this is just normal behavior and it should calculate the value when the shift is selected instead of when the button is pressed.
Any additional thoughts on this are appreciated.
Thanks for testing this and validating the findings. I wasn't sure if I just had it set up wrong or something.
If anyone else had some ideas for this, I'd love to hear them.