Combo Box – Registration with Limits on the number of attendees.
I have an application where we are collecting the reservations for breakout sessions at an all day event. Some of the breakout sessions are in large rooms and can handle everyone at the event. Others are space limited and can only hold a small number of people. Rather than allowing everyone to register and then call them to let them know they can’t attend, I decided to change the list of options depending on how many people were registered.
Data Base Support:
I added two tables to my SQL Database: helm_meeting list and helm_meetingres.
Helm_meetinglist
MeetingShort varchar(50) (used for the Question Options
Meeting varchar(50) (used for the Question Option Value
Session varchar(50) (groups the meetings by time period)
maxnum int (sets the maximum number of attendees
Helm_meetingres
Registration Unique ID
Session varchar(50)
Meeting varchar(50)
Number int (how many people registered for one registration form)
ReservationTime Timestamp
The first table allows me to flexibly change the meeting list and schedule without changing the dynamic question definition. The second table is used to track how many people are currently registered for each meeting at the event.
Form Event:
When a form is saved, the following insert is executed for each combo box on the form. This saves the amount of seats reserved.
insert into dbo.helm_MeetingRes
(UserName, Session, Meeting, Number) values
($(UserID), 'Session A', '$(SessionA)', $(NumResSA1))
Whenever a form is saved, the number of people reserving spots for each meeting is saved to the helm_meetingres table.
Getting the list
When setting up the combo box, I use the SQL option to populate the option list. For this project the selection query is of the form:
select MeetingShort as QuestionOption, Meeting as QuestionOptionValue
from dbo.helm_meetinglist a
where session = 'Session A'
and maxnum > isnull((select sum(number) as curnum
from dbo.helm_meetingres b
where session = 'Session A'
and a.meeting = b.meeting), 0)
order by displayorder
The IsNull function is used to provide 0 as the number of reservations when there are no tables in the row. Otherwise the query fails and returns nothing.
When someone opens the form, the list is generated based on the number of current registrations. It is possible to overbook for a meeting if two people load the form at the same time but the chance of this is fairly small. In the event that this happens, we may need to contact the second person to let them know the meeting was full. We use the timestamp to break ties.
I hope this is useful to someone else. I have multiple dynamic forms on this website accomplishing everything from giving instructions before the person registers (and asking them to say they read the instructions) to an Email generation page to make it easier for people to send emails to their legislators.