I have a database with 3 columns in it and an agent that imports new data from a csv file into the view of the db. The import portion works. What I need to do is compare the first column of the csv file to the first column of the database and if they match, then the 3rd column needs to be changed. I am new to LotusScript. Here is my attempt (which renders a 'type mismatch' error when agent is run). How do I get the agent to read the first column, compare it and replace if it is a match?
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.Currentdatabase
Dim uiws As New NotesUIWorkspace
Dim fileCSV As Variant
'Declare variables to hold data'
Dim FAC_CCN As String
Dim FAC_STATE As String
Dim FAC_ZIP As String
Dim counter As Long
Dim doc As NotesDocument
'Added this to get rid of error message that filenum had not been declared
Dim filenum As Integer
Dim view As NotesView
Set view = db.GetView("Input")
Set doc = view.GetFirstDocument
Dim item As NotesItem
counter=0
'setup file number
filenum% = FreeFile()
'Ask user for file location
fileCSV = uiws.OpenFileDialog(False, "Choose the CSV file,","*.csv","c:\ ")
'If the user chose a file then process
If Not IsEmpty(fileCSV) Then
Open fileCSV(0) For Input As filenum%
Do Until EOF(filenum%)
'Read a line of data
Input #filenum%,FAC_CCN, FAC_STATE, FAC_ZIP
'Create Notes document and write values to it
Set item = doc.GetItemValue("FAC_ZIP")(0)
If doc.FAC_ZIP = "FAC_ZIP" Then
Call doc.Replaceitemvalue("FAC_ZIP", FAC_ZIP)
End If
Set doc = db.CreateDocument
With doc
.FAC_CCN = FAC_CCN
.FAC_STATE= FAC_STATE
.FAC_ZIP = FAC_ZIP
.Form = "ccnForm"
End With
Call doc.Save(False, False)
Set doc = view.GetNextDocument(doc)
counter = counter + 1
'If counter = 10 Then Exit Sub
Loop
MsgBox "You imported " & counter & " records."
End If
End Sub