Find a Lotus Notes Document based on a field value

2
votes

I have a field called "JobID" that needs to be unique in the notes database. My plan is to search the database for a document's JobID and ,if it is equal to the current document's JobID, alert the user and cancel the save. I can't seem to figure out how I would do this though.

5
lotus-noteslotus-dominolotusscriptlotus-formula
new user tip - if you find some answer helpful, you may accept/upvote it. – Piotr Wadas
While there be more than one replica of the database? If there will be, then anything that attempts to verify uniqueness needs to take that into account. – David Navarre

5 Answers

2
votes

You could shorten your code a bit, there is no need to loop through all documents...

db.Search is very slow, I would use a view lookup instead. You could use db.FTSearch, but if the full-text index is not up-to-date, you will not get the correct return value. So just create a hidden lookup view with the job ID as the first and only column (sorted).

You should also declare session/db/etc in your function. Make sure you always use Option Declare...

Function JobIdIsValid (jobId As String) As Boolean
'*** Check if a form with this project number is already created
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim col as NotesDocumentCollection
Dim searchformula As String

Set db = session.CurrentDatabase
Set view = db.GetView("(LookupJobID)")
Call view.Refresh()   'Optional, but useful if documents are created often
Set col = view.GetAllDocumentsByKey(JobID)
If col.Count > 0 Then
    JobIdIsValid = False
Else
    JobIdIsValid = True
End If
End Function
2
votes

Managing the unique number yourself like that may lead you into trouble. There is a formula @Unique, which will return to you a unique value and you should probably use that instead.

Alternatively you could maintain a single document in the database that holds a number value that you increment each time manually. It's more work, but gives you something like an auto-incremented Id that relational databases have.

2
votes

I am the first to admit that this is rather ugly, but it's effective and very simple if you have a view where the first column is the JobID.

Put this in the input validation of the JobID field.
outcome := @DbLookup("":"NoCache"; "":"" ; "<viewname>"; JobID; 2);
@If(@IsError(outcome); @Success; @Failure("A Job with this ID already exists").

Or something like that.

You could also do something much nicer in many other ways:

e.g. LotusScript in the Exiting event of the JobID field using
....
set doc= NotesView.GetDocumentByKey(workspace.currentdocument.fieldgettext("JobID"))
If doc Is Nothing Then ....
etc.

Hope this helps inspire you :-) Phil

0
votes

Hm, I'm afraid there's no other way than iterating over all documents in database, which use specific form containing your field. Much more efficient way instead of iterating every time, would be saving used JobID in two places - with the document, and in some storage containing all JobIDs used - then you could check with gathered set, and/or possibly do all document's scan at off hours to ensure data integrity. You can narrow search by using some specific custom-defined view, and iterate over documents in this view instead of all documents in DB, however such solution will be a risk, that with data integration somewhat forged you'll get yourself into trouble..

0
votes
Dim ws As New NotesUIWorkspace
Dim session As New NotesSession

Set db = session.CurrentDatabase
Set view = db.GetView(("TMP_ID"))

Dim ckDoc As NotesDocument

Set ckDoc = view.GetDocumentByKey(ws.CurrentDocument.FieldGetText("ID"), True)

If Not ckDoc Is Nothing Then 
    Messagebox ("Запись с таким идентификатором уже существует")
    Call source.GotoField( "ID" )
    continue = False
End If