Notes.net Exposed: The Lotusphere 2001 contest database

The Lotusphere 2001 contest database

by
Jason
Lounsbery

Level: Beginner
Works with: Domino 5.0
Updated: 01-May-2001

If you were fortunate enough to attend Lotusphere this year, you had the opportunity to enter our Notes.net contest simply by filling out a survey. If you had to stay in the office, you could also fill in the survey, although you were not eligible for the contest.

The idea behind both the contest and survey was to promote Notes.net, offer Notes.net users a chance to win some prizes—most notably four MP3 players—get some user feedback, and have some fun. We realized early on that with all the activity during Lotusphere, we needed to set up a database that would automate, as much as possible, the various processes involved: validation of entries, survey completion and submission, and random selection of 16 winners each day.

This article takes a look at the contest database that was developed to handle our Lotusphere contest and survey. You can download the template for the Lotusphere 2001 contest database from the Iris Sandbox if you want to examine it more closely. The template was designed by Knowledge Resource Group, a Lotus Business Partner.

The big picture
The contest database was designed so that Lotusphere attendees could enter the contest and everyone could fill out the survey from their browser. The point of entry was the Lotusphere Contest welcome page:

When users clicked the Enter the contest link, an entry document appeared. The entry and survey are actually different sections of a single document. Which section appears depends on which step the user is completing. In step 1, you supply your name and e-mail address. Step 2 is entering the contest, for those who were attending the conference. Because entering the contest required you to provide a unique ID number that was given to each Lotusphere attendee, those not attending the conference could not enter the contest. Step 3 involves filling out and submitting the survey.

Validation occurs on three fields in the document: Name, E-mail Address, and for those attending Lotusphere, Contest Number. All the validation is done on the back-end via the QuerySave agent. The document is saved and validated after each step is completed, and the survey is not submitted until the final step. If the survey is not submitted, then the contest entry is not submitted.

Once submitted, the entries are put into a view and an agent is run on that view to pick the daily winners.

Step 1: Name and e-mail
Let’s jump into step 1, where the user is asked for their name and e-mail address. Both of these fields are required fields. If one of the fields is blank, the user is redirected back to the current page and asked again to fill in their name and e-mail address.

screen of step 1

There are two links under the fields: "I am not at Lotusphere" and "I am at Lotusphere." Clicking on either link causes the fields to be validated. All of the validation is on the back-end using the same QuerySave agent. Clicking on “I am not at Lotusphere” will lead the user to the survey section of the document. Clicking on “I am at Lotusphere” will lead the user to step 2, entering the contest.

Here is the formula for the “I am not at Lotusphere” hotspot.

@SetField("AtLotusSphere";"no");
@SetField("pass";"5");
@Command([FileSave]);
@Command([FileCloseWindow])

The field “pass” is used in the QuerySave agent. Its value tells the system what section of the document to validate. Remember, the survey is one document, and we need to tell it what portion we want validated at each step. Here is a section of the QuerySave agent.

Select Case pass…

Case "5":

If (lotusname = "" Or Trim(doc.Emailaddress(0)) ="")Then

doc.pass ="0"

doc.passRegistration ="false"

Else

doc.passRegistration ="true"

doc.pass ="2"

End If

Lotusname is set to the Name field on the document. The Print statement is a link to the document that the user is currently filling out. If validation fails then the pass field is set 0 and passRegistration is set to false. In this case, the user will remain in step 1 with a note explaining what is wrong.

validation failed message

If validation succeeds, then the survey questionnaire section is displayed (remember, this is the "I am not at Lotusphere" hotspot). Since the survey is the same for both attendees and non-attendees, let’s take a look at the path for attending the conference. Here is the code for the other hotspot in step 1, the "I am at Lotusphere" hotspot:

@SetField("AtLotusSphere";"yes");
@SetField("pass";"4");
@Command([FileSave]);
@Command([FileCloseWindow])

The same validation agent is called, but this time we are interested in a different section of the code:

Case "4":

If (lotusname = "" Or Trim(doc.Emailaddress(0)) ="") Then

doc.pass ="0"

doc.passRegistration ="false"

Else

doc.passRegistration ="true"

doc.pass ="1"

End If

Again the name and e-mail address are validated. If validation succeeds, then the pass field is set to 1. Setting the pass field to 1 displays the screen for step 2, which only conference attendees should see.

Step 2: Entering the contest
Step 2 involves entering the contest.

Here the validation gets a little tricky. We must check the Contest Number field for several things:

The field must not be empty.
It must contain a valid Notes.net contest number.
The contest number cannot have been previously entered.

Here is the formula for the Continue hotspot on the Step 2 page:

@SetField("pass";"2");
@Command([FileSave]);
@Command([FileCloseWindow])

The document is saved again, and the pass field tells the agent what to validate.

Case "2":

If(doc.AtlotusSphere(0) = "yes") Then

If CanRegister(lotusname,lotusnumber) = "True"Then

doc.PassRegistration = "True"

Else

doc.PassRegistration ="false"

doc.pass = "1"

End If

If notused(lotusname,lotusnumber,s) ="True" Then

doc.notused ="no"

Else

doc.notused ="yes"

doc.pass ="1"

End If

The CanRegister function is called at the beginning of the second pass:

Function CanRegister(lotusname As String,lotusnumber As String) As String

Dim firststring As String

Dim rest As String

Dim smallnumber As String

Dim largenumber As String

firststring = Left(lotusnumber,1)

rest = Right(lotusnumber,6)

Passes = "Continue"

smallnumber ="2"

largenumber ="1"

Select Case firststring

Case "Q","q"

smallnumber ="111000"

largenumber = "112000"

Case "R","r"

smallnumber ="121000"

largenumber = "122000"

Case "S","s"

smallnumber ="131000"

largenumber = "132000"

Case "T","t"

smallnumber ="141000"

largenumber = "142000"

Case "U","u"

smallnumber ="151000"

largenumber = "152000"

Case "V","v"

smallnumber ="161000"

largenumber = "162000"

Case "W","w"

smallnumber ="171000"

largenumber = "172000"

Case "X","x"

smallnumber ="181000"

largenumber = "182000"

Case "Y","y"

smallnumber ="191000"

largenumber = "192000"

Case "Z","z"

smallnumber ="201000"

largenumber = "202000"

Case "B","b"

Dim temp As String

temp = Ucase(rest)

rest = temp

smallnumber = "NN1000"

largenumber ="NN1021"

Case Else

smallnumber ="2"

rest ="1"

End Select

If (smallnumber <= rest And rest <= largenumber And Len(lotusnumber)=7) Then

CanRegister ="True"

Else

CanRegister = "False"

End If

End Function

Since the contest numbers were not associated with a specific person, there was no way to connect the number with a person. To validate, the number was matched with a number in a list. The contest numbers consisted of a letter followed by six integers. The six integers fell in a range depending on the first character. If the number is invalid, then the user remains in step 2 with an invalid number error. Again, the print statement directs the user to the correct section.

Step 2 validation

If the number is correct, the notused function is called. This function determines if the number has already been used, which guarantees that a person cannot enter the contest more than once.

Function notused (lotusname As String, lotusnumber As String, s As notessession) As String

Dim db As notesdatabase

Dim view As notesview

Set db = s.currentdatabase

Set view = db.getview("byregid")

Dim doc As notesdocument

Set doc = view.getdocumentbykey(Ucase(lotusnumber))

If doc Is Nothing Then

notused = "True"

Else

notused = "False"

End If

End Function

If the registration number had already been used, the user had to find a member of the Notes.net team in the Iris Developers Lab at Lotusphere and make their case for a new valid number. Since there was a possibility, however remote, that someone might enter someone else's number, we wanted to accommodate attendees and allow them to enter the contest.

Step 3: The survey
After all validation is completed, the user is ready to participate in the survey. As before, the print statement at the end of the case statement displays the correct section of the document, in this case, the survey.

screen for step 3 - the survey

The user scrolls through the survey, answering the various questions. At the bottom of the survey is a Submit hotspot. The formula for this hotspot is:

@SetField("pass";"3"):
@SetField("Submitted";"Yes");
@SetField("showall";"yes");
@SetField("validatenow";"no");
@Command([FileSave]);
@Command([FileCloseWindow])

Here is the validation section of the code that we are concerned about in pass 3:

Case "3":

doc.pass ="2"

doc.Can_Read ="[Readers]"

Set item = doc.getfirstitem("Can_Read")

item.IsReaders = True

Print |[| + vPath(0) + |/ThanksSurvey?Openform]|

Once the document is submitted, a Readers field is set to allow only specific users—the Notes.net team in this case—to see the completed survey. The user is directed to a thank you page.

Picking the winners
So far we've looked at how Lotusphere attendees and others filled in the survey and how attendees entered the Notes.net contest. Now let's look at how we picked the winners.

Someone on the Notes.net team ran an agent once a day to pick the winners using a random number for each daily winner. There were 16 winners each day, so 16 random numbers were picked. If for some reason a winner was not eligible for the contest, they could be removed and a new winner could be added to the bottom of the list.

Let's examine how this works by looking at the Contest Entries and Daily Winners views.

The Contest Entries view
The Contest Entries view has the following selection formula:

SELECT HasWon ="0" & AtlotusSphere ="yes" & Submitted ="Yes" & Form != "welcomepage" & Form != "winnerslist"

This view displays all of the surveys for people attending Lotusphere that have not won the daily drawing. It is used to pick the 16 winners. The Pick Winner button calls the Pick Winner agent, which picks a random number between 1 and the number of documents in the view. For example if there were 1,543 documents, the agent selects a random number between 1 and 1,543. It then flags the document in that place as a winner, removes the document from the Contest Entries view, refreshes the view, and then repeats the entire process 15 more times. The order of winning is important, because it determines which prize the winner receives.

Here is the code for the agent:

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim doccoll As notesdocumentcollection

Dim doccount As Integer

Dim x As Integer

Dim doc As NotesDocument

Dim dateTime As New NotesDateTime( "" )

Call dateTime.SetNow

Set db = session.CurrentDatabase

Set view = db.GetView( "Admin" ) ' put the name of your view here

For x = 1 To 16

Set doccoll = view.getalldocumentsbykey("0")

doccount = doccoll.count

Randomize

i=Int( (doccount-1)* Rnd+1)

Set doc = view.GetNthDocument( i )

doc.haswon ="1"

doc.datewon = dateTime.dateonly

doc.numberchosen = x

doc.randomnumber = i

Call doc.save(False,False)

Call view.refresh

End Sub

The Daily Winners view
The Daily Winners view displays the winners sorted by the date. The selection formula is:

SELECT form = "Survey" &HasWon ="1" & Form != "welcomepage" & Form != "winnerslist"

If for some reason a winning person needed to be removed from the list, a member of the Notes.net team could remove that person by selecting the document and clicking the Remove from Contest button. The code behind the button is:

Sub Initialize

Dim s As New notessession

Dim db As notesdatabase

Dim doccoll As notesdocumentcollection

Dim doc As notesdocument

Dim entry As NotesViewEntry

Dim datewon As String

Dim view As notesview

Dim vc As NotesViewEntryCollection

Dim x As Integer

Dim doccount As Integer

Set db = s.currentdatabase

Set doccoll = db.unprocesseddocuments

doccount = doccoll.count

Set doc = doccoll. getfirstdocument

datewon = doc.datewon(0)

While Not(doc Is Nothing)

doc.Haswon ="never"

doc.AtlotusSphere ="no"

Call doc.save(False,False)

Set doc = doccoll.getnextdocument(doc)

Wend

'Call doccoll.removeall(True)

Set view = db.getView("winners")

Call view.refresh()

Set vc = view.GetAllEntriesByKey(datewon)

Set entry = vc.GetFirstEntry()

Set doc = entry.Document

x = 1

While Not(entry Is Nothing)

doc.numberchosen = x

Call doc.save(False,False)

Set entry = vc.getnextentry(entry)

If Not(entry Is Nothing) Then

Set doc = entry.Document

x = x+1

End If

Wend

Call view.refresh()

End Sub

This script flags the selected documents as “not at Lotusphere” and prevents them from winning the contest. This causes the documents to be removed from the winners list and displayed only in the Survey view. The other daily winners are moved up a spot depending on the positions of the removed documents, so for example, if the 10th winner is removed, the 11th winner becomes the 10th, the 12th becomes the 11th, and so on.

Finally, we can use the Pick replacement winners button to pick new winners until we have 16 winners again. This agent prompts for how many winners to add, randomly selects that many, and adds them to the end of the winners list.

The other views
In addition to the Contest Entries and Daily Winners views, the Survey view lists all of the completed surveys, the Not Submitted view lists the documents that were started but never submitted, the Welcome view displays the Notes.net Lotusphere Contest welcome page, and the Winners List view contains the documents used on Notes.net to display the daily winners.

Daily winnners posting

The database in action
During Lotusphere, over 1,500 attendees entered the contest. We picked 16 winners every day and posted the winners on Notes.net as well as in the Iris Developer's Lab. Notes.net team members at Lotusphere and back at Iris monitored and facilitated the contest with minimal impact on their day.

About Jason Lounsbery
Jason Lounsbery works at Knowledge Resource Group, a Lotus Business Partner.