Dblookup for # in View document

OK, now that we have things working (sort of), we need to talk about form events and timing for just a bit.  So far, we have some LotusScript code that will load choices into a set of hidden fields and some @Formula code that makes the values in the hidden fields available to the checkboxes.  Now, we need to ...

1) Make sure that the form shows the choices as soon as it loads.

2) Get rid of the temporary choice fields once we're done with them (no need to waste storage space by keeping them in the document).

3) Make sure that the form displays the current choices even when the form is in read-mode.

#1 is easy.  First, make sure that all of your checkbox fields have the "Refresh choices on document refresh" property enabled.  Then, just refresh the form at the end of the PostOpen event.  Here's the line that you need to add:

    If (Source.EditMode) Then Call Source.Refresh

#2 is also easy.  You can handle this 2 different ways:

#2a - Use Computed For Display fields for the choice fields (recommended).  If you add the choice fields to the form (it sounds like you already did), then you can set them up so that the choice values never get saved to the database.  To do this, change the field type to "Computed for display", enable "Allow multiple values", and put the choice field name in the choice field's "Value" property.  For example, if you are setting up the "SubmissionList_Choices" field, then the value should be "SubmissionList_Choices" (no quotes).  That's it.  Notes will not allow the values to be saved to the backend document.  Note:  If you have existing records in the database that already have the choice values saved in the document, this will not remove them.  It only prevents the values from entering new documents.

#2b - Remove the temp fields in the form's QueryClose event.  You would have to remove the fields from the document using LotusScript, then perform a second save on the backend document.  Try using #2a first.  If you want to explore this option, just let me know.

#3 is also pretty simple, but you need to understand a few things about form events and timing.

There are two events that we can code to manipulate the document as the form opens: QueryOpen and PostOpen.  As the names imply, QueryOpen runs before the form is displayed and PostOpen runs just after it is displayed.

I choose QueryOpen in the example above because it is the easiest to understand, and I wanted to keep things simple until you had the core functionality up and running.  Now, you need to understand the differences between the two events.

There are 3 main issues that we need to be aware of:

1) When you compose a new document, Source.Document is Nothing in QueryOpen because the backend document has not been created yet.  This is why I did not have you put the code into this event to start with.  When you open an existing document, however, the backend document is available.

2) The UI form is never available in QueryOpen since the form is not displayed yet.

3) Once the form has loaded, you can't call NotesUIDocument.Refresh if the form is in read-mode (which we now do in PostOpen per #1 above).

So, it sounds like we have a catch-22 situation.  We can't load the choices in QueryOpen for new docs because there's no document available, and we can't load them in PostOpen for existing documents because we can't refresh the form in read-mode.

The trick is to put the code in both events!  Also, since we don't want two sets of identical code (for maintenance and performance reasons), we should move your code into a reusable function (Am I pushing your LotusScript limits yet?  Good!).

I've included an example of the function definition below.  Just paste this code into the "Declarations" module of your form (if you've changed the code, you'll need to apply the changes here as well).  Note that the call to Source.Refresh has been left out on purpose.

Now remove the old code from PostOpen and make your QueryOpen and PostOpen events look like this:
      

      Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
            
            If (Not Source.IsNewDoc) Then Call LoadCheckboxChoices(Source)
            
      End Sub


      Sub Postopen(Source As Notesuidocument)
            
            If (Source.IsNewDoc) Then Call LoadCheckboxChoices(Source)
            If (Source.EditMode) Then Call Source.Refresh
            
      End Sub


Now, QueryOpen loads the choices for existing documents and PostOpen loads the choices for new docs.

Sorry for the long-winded response, but I didn't want to leave you hanging without finishing this up the right way.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Sub LoadCheckboxChoices(Source As NotesUIDocument)
	
	Dim db As NotesDatabase
	Dim view As NotesView
	Dim nav As NotesViewNavigator
	Dim entry As NotesViewEntry
	Dim doc As NotesDocument
	Dim count As Integer
	Set doc = Source.Document
	Set db = doc.ParentDatabase
	Set view = db.GetView("topten")
	Set nav = view.CreateViewNav
	Set entry = nav.GetFirstDocument
	Do While (Not entry Is Nothing)
		If (count = 0) Then
			Call doc.ReplaceItemValue("SubmissionList_Choices", entry.Document.GetItemValue("Subject")(0))
		Else
			Call doc.ReplaceItemValue("SubmissionList_" & count & "_Choices", entry.Document.GetItemValue("Subject")(0))
		End If
		count = count + 1
		Set entry = nav.GetNextDocument(entry)
	Loop
	
End Sub