Querying a specific record in Lotus Notes

Question

I'm used to working with relational databases, where you could target a specific record. For instance, using the pseudo sql below.

SELECT id, name, otherVar FROM students WHERE id=:studentId

However, I am unsure how to even approach this in Lotus Notes with its flat data model. I have been googling but keep coming up with hits on how to update Lotus Notes itself, not a document inside of Lotus Notes. If someone with the some LN expertise could point me in the right direction, that would be much appreciated.

Naveen Naveen · Accepted Answer · 2013-10-28T05:05:32

Using the SQL statement as analogy, lets say you have a view students with columns id, name and otherVar. The column id should be sorted (either in ascending or descending). So the view looks something like this

╔════╦════════════╦═════════════╗
║ id ║ name       ║ otherVar    ║
╠════╬════════════╬═════════════╣
║ 1  ║ Daniel     ║ ----------  ║
║ 2  ║ Joseph     ║ ----------  ║
║ 3  ║ Michelle   ║ ----------  ║
╚════╩════════════╩═════════════╝

To lookup this view you would write something like this in LotusScript:

Dim session As New NotesSession 'Get current session
Dim currentDB As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim studentId As String

studentId = "<STUDENT_ID>" 'The student ID that needs to be searched
Set currentDB = session.CurrentDatabase 'Get current database
Set view = currentDB.GetView("students") 'Get the view
Set doc = view.GetDocumentByKey(studentId, True) 'Look up the view with student ID to get the student document

Do a simple Google search for NotesView for more information. In formula language you can write it as:

@DbLookup("Notes":"NoCache"; ""; "students"; "<STUDENT_ID>"; "<FIELD TO BE RETRIEVED>"; [FailSilent]);

But formula is less flexible that LotusScript if you want to do complex calculations.

Querying a specific record in Lotus Notes

2 Answers