Question

Student Lottery Brainstorm

Forms

Updated July 18, 2022

asked on July 13, 2022 • Show version history

We have a potential project coming our way. The request is to help simplify a student lottery process for acceptance into a special program. The acceptance has to be random in order to make it fair.

The student will submit an application for review. Once its reviewed and determined a viable candidate they are current adding them to an excel spreadsheet, assigning a number and holding a lottery. There are three different programs they can apply for and once they accept one of them they forfeit the the remaining options. The choice is up to them if they were selected for multiple programs.

I'm having trouble wrapping my mind around all of the possibilities and a way to remove them from other considerations once they have accepted a program.

Just random thoughts:

I think it would take multiple forms. One form for student submission (non-licensed users) that routes to the reviewer where they accept or deny. The student would enter the programs they wish to apply and the reviewer would have their own box for the programs they recommend. The accepted applications and reviewers answers would write to a SQL table and somehow a random number would be assigned to each student (unsure how to do this).

This is where I'm stumped:

Another form where the committee can select from a dropdown the program they are trying to fill. A random student that is approved for that program is populated and a workflow will send the student an email with a link to accept or deny the program they choose which in turn will write to the table that they have already selected a program so don't randomly generate they application again.

The committee continues until the programs are full. I believe there are three potential programs.

These are just ideas so please let me know if anyone has any suggestions for a way to make this work. None of this is set in stone. I'm just trying to figure out how to help them.

0 0

Replies

replied on July 13, 2022

I think I would create a table for the student lottery. As as student is identified as qualified for different programs a record is inserted into the table for them (or updated if they are already in the table). You could either have one field that lists everything they are qualified for, or columns for each item.

Then you could create Views and Stored Procedures to do things like "view all students qualified for x program and not currently accepted into any program" and "select random student who is qualified for x program and not currently accepted into any program".

You would need a field on the table to identify that they are not in any program, or currently offered (but not yet accepted/rejected a position in the program), or current in a program. That way you can ensure only the students who are not in any program are included in new lotteries.

Since it's only one table, you don't have to keep different records for each different program's lottery, and check against all of them when doing a new lottery, and update all of them when a position is accepted - it's all one central location.

I don't think you need to create a random number for them, because you can make a stored procedure select a random record from the table.

2 0

replied on July 18, 2022

Once the data is in the sql table, to select a random record:

SELECT TOP 1 [columns]

FROM [table]

ORDER BY NEWID()

Create view on sql, link to form using a lookup rule.

1 0

You are not allowed to follow up in this post.

Question

Question

Student Lottery Brainstorm

Replies

Sign in to reply to this post.