I am working on a lotus script shared agent called from the web that loops thorugh the documents of a view until it finds a match on employee number. When I display the view in notes or from the web I can see the employee number that I am looking for. If I select all the documents in the view from the notes client, I see there are 1128 documents in the view.
In the shared action, I loop thorugh all the documents from the view after doing a getallentries from the view collection. In looping throught the documents, I do not find a match on the desired employee even though I can see it in the view. To help debug this, I put a counter in the loop at output the counter on each itteration though the loop. The lotus scriipt only finds 1122 documents in the view even though there should be 1128. The view is based upon the document type employeerecord used to create the document. The employee record I am looking for is one that was entered reciently. It is working for documents that were uploaded initially from a text file. If I compare the format of document uploaded with the one entered manually, they look identical except for the obvious difference in employee number and name on the record.
The code I am using is below and I have written it numerous times but can not seem to get it to find the document that were entered manually.
’ Using ViewAllEntries
’ Loop through all documents in employees view until found a match
’ doing this because can not get getEntryByKey to work
ActiveEmployeeNumber = Val(ActiveEmployeeText)
Set EmployeesViewCollection = EmployeesView.AllEntries
Set EmployeesViewEntry = EmployeesViewCollection.getFirstEntry()
Set EmployeeRecord = EmployeesViewEntry.Document
Index = 0
Do Until EmployeeRecord Is Nothing
Index = Index + 1
Print index
EmployeeNumberVariant = EmployeeRecord.GetItemValue( "EmployeeNumber" )
EmployeeNumber = (Cdbl(Trim$(EmployeeNumberVariant(0))))
EmployeeNumberText = Trim$(Cstr(EmployeeNumberVariant(0)))
If EmployeeNumberText = ActiveEmployeeText Goto FoundEmployee
Set EmployeesViewEntry = EmployeesViewCollection.getNextEntry(EmployeesViewEntry)
Set EmployeeRecord = EmployeesViewEntry.Document
Loop
Print " "
Goto LoginError
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
What is really strange is that on the Submit button that calls the agent that I am having trouble with, I do a @DBLookup agains the employee number and the @DBLookup finds the employee but the agend looping through the view does not. Here is the code from the Submit button.
FIELD EmployeePIN := @DbLookup(“NOTES”:“NoCache”;“”;“Employees”;ActiveEmployee;“EmployeePIN”);
FIELD EmployeeFirstName := @DbLookup(“NOTES”:“NoCache”;“”;“Employees”;ActiveEmployee;“EmployeeFirstName”);
FIELD EmployeeLastName := @DbLookup(“NOTES”:“NoCache”;“”;“Employees”;ActiveEmployee;“EmployeeLastName”);
FIELD EmployeeFullName := EmployeeFirstName + " " + EmployeeLastName;
FIELD LoggedIn := @If(@UpperCase( ActivePin ) = EmployeePIN;“Y”;“N”);
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
Rich,
Those were my initial thoughts. The view is sorted by EmployeeNumber and I tried that method of searching at first however it did not work. That is why I put the remark there.
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
Is EmployeeNumber a number or text that looks like a number? And are you comparing against a numeric value or text that looks like a numeric value?
If EmployeeNumber is text and contains a space after the “number”, you would not see this in the view but itw would fail to match in either the lookup or looping through the documents one at a time.
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
In the employee document, the employee number is defined as a number not text. I have tried to use both the numeric and text representations of this field with the same result.
The one thing that keeps bugging me is that if I loop through the documents in the view from the shared agent, I get 1122 documents counting them all. If I open the view in the domino client and select all the documents, I get 1128. I am trying to figure out where the 6 missing documents are in the Agent as these are the ones that were added manually.
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
Perhaps a silly question but when you “select” all documents in the view, are you using Ctrl-A to select them all (or the menu equivalent)? I imagine so because you are probably not clicking on each document separately. If so, does the view contain categories? Those will get “selected” when using Ctrl-A and appear as if more documents were selected than truly were. Your agent will only “select” true documents and not their categories and this may explain the difference of 6 in the count.
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
Actually, what you probably mean is in the FORM, the EmployeeNumber is defined as a number field. The ones that were added manually will therefore have the EmployeeNumber field in the DOCUMENT in numeric format. But the others… those might not be numeric. How were they added? Have you checked the field flags in the document properties dialog to make sure that they weren’t really added as text?
If they are not actually numeric, you should refresh the docs to force them to be converted. You should also fix your process for creating the document.
And BTW: If you set the first sorted column formula in your lookup view to @Trim(@Text(EmployeeNumber)), it won’t matter whether they are numeric or text. A text lookup should find them.
Subject: RE: Do you have a view that is sorted by EmployeeNumber?
Rich,
Thank you for the response.
You were correct, in the FORM I have defined the field EmployeeNumber as numeric. I am not exactly sure how to verify or check the Field Flags, maybe you could enlighten me.
I did try to use a calculated column simular to your Trim statement. I will try that again.
I converted the Agent code to the following to make sure I was using numeric value for the key in the view and for the GetDocumentbyKey method in the lotus script.
Maybe you can spot something I am doing wrong.
Get URL Parameters
URLString = session.DocumentContext.Query_String(0)
idToken = Instr(URLString,"&ActiveEmployee2=")
strLen = Len(URLString)
TempString = Mid$(URLString,idToken + 17 , strLen)
idToken = Instr(TempString,"&ActivePIN=")
strLen = Len(TempString)
ActiveEmployeeText = Ucase$(Mid$(TempString,1, idToken - 1))
ActivePinText = Mid$(TempString,idToken + 11, strLen)
Set db = session.CurrentDatabase
Set EmployeesView = db.GetView("Employees")
Dim Index As Integer
Dim EmployeesViewCollection As NotesViewEntryCollection
Dim EmployeesDocument As NotesDocument
Dim EmployeesViewEntry As NotesViewEntry
ActiveEmployeeNumber = Cdbl(ActiveEmployeeText)
Set EmployeeRecord = EmployeesView.GetDocumentByKey(ActiveEmployeeNumber,True)
If Not ( EmployeeRecord Is Nothing ) Then
EmployeeNumberVariant = EmployeeRecord.GetItemValue( "EmployeeNumber" )
If EmployeeNumber = ActiveEmployeeNumber Then
Goto FoundEmployee
Else
Goto LoginError
End If
Else
Goto LoginError
End If
Select the doc in the view, right click and bring up Document Properties. Select the second tab, locate the EmployeeNumber field in the left side scroll box and click on it. The field data is displayed on the right.
If the Data Type says Text, that means that your documents are not being created with numeric data in that field. Note that only manually created documents necessarily get the data type that is defined in the form. Docs created by agents or other mechanisms may be created with other field types – unless care is taken.
thanks for the info. I was able to review athe field properties in the document. The documents working are numeric and the ones not are text. I think I will convert them all to numeric using another view and then try the lookup using this new view. What I don’t understand is why the @DBLookup works on all the documents but the looping through the documents dosen’t work.
Sorry I spoke too soon. The documents all contain numeric employee numbers. I did not realize but when I moved from document to document, the field selected changed from the EmployeeNumber to EmployeeName and that I why I though that some were text. Going back through the documents, making sure that EmployeeNumber was selected, they all showed numeric for the EmployeeNumber.
Are you certain that when you do your looping and substring extraction using the Mid$ functions, that you are actually getting the right value to use in the comparison? At this point, that is the only thing that comes to mind.
I am sure this is working as every time I put stops in the lotus script and print the values, they are correct for every employee.
I just found out that the @DBLookup I have on the initial screen that calls the script works from the Domino Client for all employee records. But only works from the Web for the employee numbers that were initially uploaded.
I am trying to think what would make the @DBLookup work from the client but not the web. Maybe an authorization issue.
If I take a new employee entered manually, and enter the employee number and press the submit button, I get the employee @DBLookup working and it returns the employee information. If I do the same thing from the browser with the same employee number, I get nothing returned from the @DBLookup.