Real-World Example 1: Importing a Delimited Text File
Real World Example 1 Importing a Delimited Text File
This script will come in handy when you have an automated application that sends an email nightly to a robot containing updates or stores updated files on a host drive somewhere to be imported into a database. This routine can be set to run on a scheduled basis, for example, to seek out all the latest incidents submitted to a customer incident-tracking mail-in database. It probes each email document to get its attached delimited text file, detaches the file, and then imports each delimited record into the incident database as a new incident record. When the script completes, it sends an email notification to the administrator about how many records were retrieved, processed , and created in the database by the agent. What's nice about this example is that you can use some or all of its parts . If you need only the import function, you can use just a subset of the code. If you need to use the email subroutine, you can also pull that out and reuse it wherever necessary. Let's get started.
The options area of the script module, as shown in Listing 15.1, is where the developer states preferences for the script module, such as Public , Declare , Base , and Compare . Option Public , as used here, unequivocally allows any variables dimensioned ( DIM ) within the script module to be declared as publicly usable to any routine or subroutine throughout the entire module. Option Private can be used to override Option Public . Option Explicit or Option Declare sets the module so that all variables must be declared ( DIM 'd). If you use undeclared variables on the fly, when you go to compile your script, syntax errors will be generated. This rule does not apply to arrays; you can define an array implicitly using the ReDim declaration method.
Listing 15.1 Option Preferences
1. Option Public
2. Option Explicit
The block of global declarations in Listing 15.2 sets up constants and variables for the module to run. The text delimiter , view names, form names , and database IDs are declared globally so that they can be used (called) anywhere inside the module. It's generally good practice to declare all your dimensioned variables in the (Declaration) area. However, this doesn't prevent you from declaring them in the Initialize event or any other event in the module.
Listing 15.2 Global Declarations
1. Const gFieldDelimiter=""
2. Const gListViewColumn=1
3. Const gIncidentIDFieldPosition = 1
4. Const gFormName="Incident"
5. Const gIncidentNumViewName="vINLU"
6. Const gMailDBRepID = "85256C2F004CB691"
7. 'Now declare most of the variables we need for this module
8. Dim gFieldList List As Integer
9. Dim currentDb As NotesDatabase
10. Dim MailView As NotesView
11. Dim MailDoc As NotesDocument
12. Dim TempDoc As NotesDocument
13. Dim rtitem As Variant
14. Dim fileCount As Integer
15. Dim FileName As String
16. Dim EMailDoc As NotesDocument
17. Dim Body As NotesRichTextItem
18. Dim BodyTextA As Variant
19. Dim BodyTextB As Variant
20. Dim ret As Variant
21. Dim ImportRecordCounter As String
22. Dim Intro As String
23. Dim errormsg As String
Depending upon your needs, the gFieldDelimeter constant as shown in Listing 15.3 can be changed to any ASCII character code of your choice. The delimiter separates each record in the target text file. The gUniqueIDFieldPostion variable on line 3 determines the position in the import record where a unique number exists. The position must be declared as an integer. Obviously, the view names, replication IDs, and form names can all be changed to work within your applications.
Listing 15.3 The Initialize Subroutine
1. Sub Initialize
2. Dim session As New NotesSession
3. Dim MailDB As New NotesDatabase("","")
4. Dim sID as String
5. On Error Goto ERRORHANDLER
6. Set currentDB = session.CurrentDatabase
7. Call session.SetEnvironmentVar("ImportRecordCounter", "0")
8. fileCount = 0
9. 'Check to see if the database is on a server and add currentDB.server otherwise use ""
10. If session.IsOnServer Then
11. sID = currentDB.Server
12. Else
13. sID=""
14. End If
15.
16. If MailDB.OpenByReplicaID ( currentDB.Server,gMailDBRepID ) Then
17. ' go get the new mail message in the $All view
i. Set Mailview = MailDB.GetView( "($POP3)" )
ii. Set MailDoc = Mailview.GetFirstDocument
iii. While (Not MailDoc Is Nothing)
1. Set rtitem = MailDoc.GetFirstItem( "Body" )
2. If ( rtitem.Type = RICHTEXT ) Then
a. If MailDoc.HasEmbedded Then
i. Forall file In rtitem.EmbeddedObjects
1. If ( File.Type = EMBED_ATTACHMENT ) Then
a. fileCount = fileCount + 1
b. file.ExtractFile "d:INCDNTIMPT" & Cstr(Month(
Today) &_
c. Day(Today) & FileCount) & ".txt"
d. 'Now process the dettached file here
e. gFieldList("RequestID") = 1
f. gFieldList("CompanyName") = 2
g. gFieldList("ContactName") = 3
h. gFieldList("Address") = 4
i. gFieldList("City") = 5
j. gFieldList("State") = 6
k. gFieldList("ZipCode") = 7
l. gFieldList("Country") = 8
m. gFieldList("Phone") = 9
n. gFieldList("EmailAddress") = 10
o. Filename = "d:INCDNTIMPT" & Cstr(Month(
Today) &_
p. Day(Today) & Cstr(FileCount)) & ".txt"
q. 'Go and run the main routine to process the
file
r. Call main(session, currentDB, filename)
s. 'Remove the importfile we're done with it
t. Kill "d:INCDNTIMPT" & Cstr(Month(Today) &
Day(Today) &_
u. Cstr(FileCount)) & ".txt"
2. End If
ii. End Forall
b. End If
3. Else
a. Goto PROCESSNEXT
4. End If
18. PROCESSNEXT:
1. Set TempDoc = Mailview.GetNextDocument(MailDoc)
2. MailDoc.Remove True
3. Set MailDoc = TempDoc
ii. Wend
19. End If
20. 'Now send a mail memo telling how many files and records were processed today.
21. Call sendMail(session, currentDb, ImportRecordCounter, fileCount)
22. Exit Sub
23. ERRORHANDLER:
24. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in LS agent: " &_
25. "ImportRecords in Incident Import App at " & Erl()
26. Msgbox errormsg
27. Resume Next
28. End Sub
The purpose of the Initialize subroutine in Listing 15.3 is to get and extract each text attachment from each email that resides in the Email Incident Bucket database, send it off to be processed by the Main subroutine, clean up the file that it extracted, and delete the email after it has been processed. The Initialize subroutine also keeps track of how many files have been processed by the agent and sets up the environment variable placeholder ImportRecordCounter to keep track of how many records are eventually processed. After all the files and records have been processed by the agent's subroutines, the Initialize subroutine finally sends an email to the person who ran the agent about how many files and records have been processed.
The Initialize subroutine also provides an error-handling routine so that if an error is encountered in the routine when run, the agent is halted and an error message is invoked that is captured in the log.nsf of the server where the agent is run.
Code lines 17e “17n in Listing 15.3 use the gFieldList declared array to set up the imported file's field placeholders. The imported file must contain data in the exact order as what appears in the array. This means that the incident's ID number must be stored in the number 1 position in the delimited text file, the company name value must be stored in the number 2 position in the delimited text file, and so forth, as shown in Listing 15.4. You can change the field names and array names; just make sure that they match the order in the delimited file to the labels in the array. The field names on the form that you are importing to also must match the names of the array labels exactly. If not, the import might not work as expected.
The kill statement in line 17.t of Listing 15.3 deletes the delimited file where it was extracted in line 17.b. You could use variables and other LS commands to get and set the file-extraction path programmatically. However, for the sake of clarity in this example, the file path is explicitly named in this routine.
The routine in Listing 15.5 first calls the FreeFile() function to get a file handle on the import file and assign it an internal file number so that it can be identified as an object in the back end. An Open statement is then called to actually open the import file. The incident view object is then set as declared in the Declarations area. Next, a unique number list (an array), made up of all the incident numbers in the file, is built so that when the processFile subroutine is called, the routine can check to make sure that duplicate incidents are not processed but are overwritten. After the NumberList array is built, each record in the file is processed thereafter. If the incident number is not found in the NumberList array, a new document is created for the unique record in the Incident Import App database. If the incident does exist, the agent updates the existing document with the new information. When the agent has finished and no errors are generated, it closes the import file by clearing its file handle. It then is returned to the Initialize subroutine to complete the agent's next course of action.
Listing 15.5 The Main Subroutine
1. Sub main (session As NotesSession, CurrentDb As NotesDatabase, ImportFileName As
String)
2. Dim view As NotesView
3. Dim importFile As Integer
4. Dim success As Integer
5. Dim NumberList List As String
6. On Error Goto ERRORHANDLER
7. If (Not ImportFileName = "") Then
i. ImportFile = Freefile()
ii. Open ImportFileName For Input As ImportFile
iii. 'Get the Number List
iv. Set view = currentDB.getView(gIncidentNumViewName)
v. If dbColumn(CurrentDB, view, gListViewColumn, NumberList) Then
1. Call processFile(session, view, currentDb, importFile, NumberList)
vi. End If
vii. Close importFile
8. End If
9. Exit Sub
10. ERRORHANDLER:
11. errormsg = " * * Agent Error: " & Err & " - " & Error() &_
12. " in the main sub-routine of LS agent " &_
13. "ImportRecords in Incident Import App at " & Erl()
14. Msgbox errormsg
15. Resume Next
16. End Sub
The purpose of the function in Listing 15.6 is to get each sequential line in the file, parse each delimited position in the line, and either place the string content of each position in its respective field on a new incident or overwrite the field contents of an existing incident. Line 12.i calls the Line Input # statement to read the line, grabs the string for the position of the record label being processed, and places it into the record String variable. This results in the record string looking something like this: 12345 MyCompany1 MyContact ... .
Listing 15.6 The processFile Function
1. Function processFile(session As NotesSession, view As NotesView, currentDb As
NotesDatabase, Byval inputFile As Integer, IncidentList List As String) As Integer
2. Dim IncidentInList As Integer
3. Dim record As String
4. Dim IncidentID As String
5. Dim OldIncidentID As String
6. Dim key As Variant
7. Dim IncidentDoc As NotesDocument
8. Dim Counter As Integer
9. On Error Goto ERRORHANDLER
10. processFile = True
11. Counter = Cint(session.GetEnvironmentString("ImportRecordCounter"))
12. Do While Not Eof(inputFile)
i. Line Input # inputFile,record
ii. record=record & gFieldDelimiter
iii. IncidentID = getField(record, gIncidentIDFieldPosition, gFieldDelimiter)
iv. IncidentInList = Iselement(IncidentList(IncidentID))
v. If IncidentInList Then
1. key = IncidentID
2. Set view = currentdb.GetView(gIncidentNumViewName)
3. Set IncidentDoc = view.GetDocumentByKey(Key)
4. If (Not IncidentDoc Is Nothing) Then
a. Call OverwriteIncident(currentdb,session, IncidentDoc, record, IncidentID)
5. End If
vi. Else
1. Call createIncident(currentdb, session, record, IncidentID)
vii. End If
viii. IncidentList(IncidentID) = IncidentID
ix. Counter = Counter + 1
x. OldIncidentID = IncidentID
13. Loop
14. Call session.SetEnvironmentVar( "ImportRecordCounter", Cstr(Counter) )
15. Exit Function
16. ERRORHANDLER:
17. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the processFile
sub-routine of LS agent: " &_
18. "ImportRecords in Incident Import App at " & Erl()
19. Msgbox errormsg
20. Exit Function
End Function
Next, the getField function is called to extract the record's unique incident number string in position 1, up to the delimiter in the record. The routine then determines whether the incident number exists in the NumberList array. If the incident number exists, the processFile function retrieves the existing incident document object by a key in a view, as shown in line 12.v.3, and calls the OverwriteIncident subroutine to process the incident in the database. If it does not exist in the NumberList array, line 12.vi.1 shows that the CreateIncident subroutine is called to create a new incident in the database for the current record.
The processFile function then goes on to complete its tasks for the current record, as shown in lines 12.vii “12.x, and loops through each record until all the records in the file have been processed likewise. After each record is processed, the NumberList array is reassigned, the record counter is incremented by 1, and the current incident ID is assigned to the OldIncidentID variable. This allows the IncidentID variable to be reset when the routine loops back to the DoWhile() statement in line 12 once again, to process the next record in the file.
When all records have been processed, the file counter environment variable is incremented by 1, and the function exits and returns 1 (true) to the Main subroutine, telling it that it successfully completed.
The function in Listing 15.7 creates a new incident (line 2) in the Incident database and populates its fields based on the string values in each position inside the current record (lines 8 “9). The ListTag function is used in line 8.i to return the name of the gFieldList list position currently being processed by the ForAll statement. The routine then steps through each position and replaces the position value with that of the corresponding record string for the current position.
Listing 15.7 The CreateIncident Function
1. Function CreateIncident(db As NotesDatabase, session As NotesSession, record As String,
IncidentID As String) As Integer
2. Dim IncidentDoc As New NotesDocument(db)
3. On Error Goto ERRORHANDLER
4. IncidentDoc.docKey = IncidentDoc.universalid & session.UserName
5. IncidentDoc.Form = "Incident"
6. IncidentDoc.LastImportDate=Now
7. IncidentDoc.DateImported = Now
8. Forall positions In gFieldList
i. IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,
gFieldDelimiter))
9. End Forall
10. IncidentDoc.Save True,True
11. createIncident = True
12. Exit Function
13. ERRORHANDLER:
14. errormsg = " * * Agent Error: " & Err & " - " & Error() &_
15. " in the CreateIncident sub-routine of LS agent: " &_
16. "ImportRecords in Incident Import App at " & Erl()
17. Msgbox errormsg
18. Exit Function
19. End Function
When all the fields have been populated for the new incident, the document is saved to the database and the function returns 1 (true) back to the processFile function, telling it that it successfully completed.
The purpose of the function in Listing 15.8 is to parse the record at each position in the record or the position requested , remove its delimiter, and return the stripped string value back to the routine that called the getField function. If the routine starts with a string that looks like MyString1MyString2MyString3... and position 2 is requested by the calling routine, it returns only MyString2 .
Listing 15.8 The getField Function
1. Function getField(record As String, Byval position As Integer, delimiter As String) As
String
2. Dim i As Integer
3. Dim fieldStart As Integer
4. Dim fieldEnd As Integer
5. getfield=""
6. On Error Goto ERRORHANDLER
7. fieldStart = 0
8. fieldEnd = 0
9. For i=1 To position
i. fieldStart = fieldEnd + 1
ii. fieldEnd = Instr(fieldStart, record, delimiter)
iii. If fieldEnd=0 Then Exit For
10. Next
11. If fieldEnd>0 Then
i. getfield = Mid(record, fieldStart, (fieldEnd - FieldStart))
12. End If
13. Exit Function
14. ERRORHANDLER:
15. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the getField
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " &
Erl()
16. Msgbox errormsg
17. Exit Function
18. End Function
The function in Listing 15.9 actually mimics the dbColumn @Function using LotusScript by building a list of values from each document found in a specified view. The NumberList array, found in the Main subroutine, is created using the dbColumn function to build the list of incident numbers that currently reside in the database already. It builds the list of incident number values in each document, builds the columnValues array, and then returns 1 (true) to the routine that called it, to let it know that it successfully completed. You can reuse this repeatedly throughout your applications.
Listing 15.9 The dbColumn Function
1. Function dbColumn(DB As NotesDatabase, view As NotesView, Byval columnNumber As
Integer, columnValues List As String) As Integer
2. 'Variable Declarations
3. Dim value As Variant
4. Dim doc As NotesDocument
5. On Error Goto ERRORHANDLER
6. Erase columnValues 'Clears the columnValues list
7. Set doc = view.getFirstDocument
8. While Not (doc Is Nothing)
i. value = doc.columnValues(0)
ii. If Isnumeric (value) Then
1. value = Cstr(value)
iii. End If
iv. columnValues(value) = value
v. Set doc = view.getNextDocument(doc)
9. Wend
10. dbColumn = True
11. Exit Function
12. ERRORHANDLER:
13. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the dbColumn
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
14. Msgbox errormsg
15. Exit Function
16. End Function
NOTE
You can also use the NotesViewColumn method of the NotesView class to get the values of a particular column, as in this example:
Dim column as NotesViewColumn
Set column = view.Columns( 0 )
However, the dbColumn function is more than handy when you need to retrieve only certain values from a subset of docs in a view or pull only those that meet a certain criteria.
The OverwriteIncident function in Listing 15.10 is very similar to the CreateIncident function because it doesn't set the form field, the docKey field, and the dateImported field values in the Incident document. Its purpose is to solely parse each position value in the record and update the field values with the new data from each. It then goes on to save the document and return 1 (true) to the processFile function, telling it that it successfully completed.
Listing 15.10 The OverwriteIncident Function
1. Function OverwriteIncident(db As NotesDatabase, session As NotesSession, IncidentDoc
As NotesDocument, record As String, IncidentID As String ) As Integer
2. OverwriteIncident = True
3. On Error Goto ERRORHANDLER
4. IncidentDoc.LastImportDate=Now
5. Forall positions In gFieldList
i. IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,
gFieldDelimiter))
6. End Forall
7. IncidentDoc.Save True,True
8. Exit Function
9. ERRORHANDLER:
10. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the
OverwriteIncident
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
11. Msgbox errormsg
12. Exit Function
13. End Function
The last subroutine for this example is shown in Listing 15.11. It can be reused repeatedly throughout all of your applications to automate email generation or automatic workflow notifications. Its purpose is self-explanatory. The power in this routine is that it enables agents to send emails summarizing its activities, successes, and failures.
Listing 15.11 The OverwriteIncident Function
1. Sub sendMail(session As NotesSession, Currentdb As NotesDatabase, ImportRecordCounter
As String,fileCount As Integer)
2. On Error Goto ERRORHANDLER
3. 'Now send a mail memo telling how many files and records were processed today.
4. Set EMailDoc = New NotesDocument(Currentdb)
5. Set Body = New NotesRichTextItem(EMailDoc, "Body")
6. ImportRecordCounter = session.GetEnvironmentString( "ImportRecordCounter")
7. EMailDoc.sendTo = session.Username
8. ret = Evaluate("@unique(SendTo)",EMailDoc)
9. EMailDoc.SendTo = ret
10. EMailDoc.Form = "Memo"
11. EMailDoc.principal = "ImportIncidents*Robot"
12. EMailDoc.Subject = "Incident Import Activity Report"
13. intro$ = "This is an automated notification. The incident Import Agent last ran at "
& Time$() & " on " & Format(Now(), "Long Date")
14. BodyTextA = Cstr(fileCount) & " File(s) have been processed."
15. BodyTextB = ImportRecordCounter & " Record(s) have been processed."
16. Call Body.appendText(Intro$)
17. Call Body.AddNewLine(2)
18. Call Body.appendText(BodyTextA)
19. Call Body.AddNewLine(2)
20. Call Body.AppendText(BodyTextB)
21. 'Don't save the mail message in the Email Import incident db after it's sent
22. EMailDoc.SaveMessageOnSend = False
23. 'Send the new mail notification for this import run
24. Call EMailDoc.Send(False)
25. Exit Sub
26. ERRORHANDLER:
27. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main
sub-routine of LS agent: " &_
28. "ImportRecords in Incident Import App at " & Erl()
29. Msgbox errormsg
30. Resume Next
31. End Sub
The complete ImportIncidents agent code listing is shown in Listing 15.12.
Listing 15.12 The ImportIncidents Agent Complete Script Listing
'-ImportIncidents LotusScript Agent
'Options:
Option Public
Option Explicit
'Declarations:
Const gFieldDelimiter=""
Const gListViewColumn=1
Const gIncidentIDFieldPosition = 1
Const gFormName="Incident"
Const gIncidentNumViewName="vINLU"
'this is the RepID of the mail file where the import files reside.
Const gMailDBRepID = "85256C2F004CB691"
Dim gFieldList List As Integer
Dim currentDb As NotesDatabase
Dim MailView As NotesView
Dim MailDoc As NotesDocument
Dim TempDoc As NotesDocument
Dim rtitem As Variant
Dim fileCount As Integer
Dim FileName As String
Dim EMailDoc As NotesDocument
Dim Body As NotesRichTextItem
Dim BodyTextA As Variant
Dim BodyTextB As Variant
Dim ret As Variant
Dim ImportRecordCounter As String
Dim Intro As String
Dim errormsg As String
'Routines:
Sub Initialize
Dim session As New NotesSession
Dim MailDB As New NotesDatabase("","")
Dim sID As String
On Error Goto ERRORHANDLER
Set currentDB = session.CurrentDatabase
Call session.SetEnvironmentVar("ImportRecordCounter", "0")
fileCount = 0
'Check to see if the database is on a server and add currentDB.server otherwise use
""
If session.IsOnServer Then
sID = currentDB.Server
Else
sID=""
End If
If MailDB.OpenByReplicaID ( sID,gMailDBRepID ) Then
' go get the new mail message in the $All view
Set Mailview = MailDB.GetView( "($POP3)" )
Set MailDoc = Mailview.GetFirstDocument
While (Not MailDoc Is Nothing)
Set rtitem = MailDoc.GetFirstItem( "Body" )
If ( rtitem.Type = RICHTEXT ) Then
If MailDoc.HasEmbedded Then
Forall file In rtitem.EmbeddedObjects
If ( File.Type = EMBED_ATTACHMENT ) Then
fileCount = fileCount + 1
file.ExtractFile "d:INCDNTIMPT" &
Cstr(Month(Today) &_Day(Today) & FileCount) & ".txt"
'Now process the file here
gFieldList("RequestID") = 1
gFieldList("CompanyName") = 2
gFieldList("ContactName") = 3
gFieldList("Address") = 4
gFieldList("City") = 5
gFieldList("State") = 6
gFieldList("ZipCode") = 7
gFieldList("Country") = 8
gFieldList("Phone") = 9
gFieldList("EmailAddress") = 10
Filename = "d:INCDNTIMPT" & Cstr(Month(Today) &_
Day(Today) & Cstr(FileCount)) & ".txt"
'Go and run the main routine to process the file
Call main(session, currentDB, filename)
'Remove the importfile we're done
with it
Kill "d:INCDNTIMPT" & Cstr(Month(Today) & Day(Today)
&_
Cstr(FileCount)) & ".txt"
End If
End Forall
End If
Else
Goto PROCESSNEXT
End If
PROCESSNEXT:
Set TempDoc = Mailview.GetNextDocument(MailDoc)
MailDoc.Remove True
Set MailDoc = TempDoc
Wend
End If
'Now send a mail memo telling how many files and records were processed today.
Call sendMail(session, currentDb, ImportRecordCounter, fileCount)
Call session.SetEnvironmentVar("ImportRecordCounter", "0")
Exit Sub
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in LS agent: " &_
"ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Resume Next
End Sub
Sub main (session As NotesSession, CurrentDb As NotesDatabase, ImportFileName As String)
Dim view As NotesView
Dim importFile As Integer
Dim success As Integer
Dim NumberList List As String
On Error Goto ERRORHANDLER
If (Not ImportFileName = "") Then
ImportFile = Freefile()
Open ImportFileName For Input As ImportFile
'Get the Number List
Set view = currentDB.getView(gIncidentNumViewName)
If dbColumn(CurrentDB, view, gListViewColumn, NumberList) Then
Call processFile(session, view, currentDb, importFile, NumberList)
End If
Close importFile
End If
Exit Sub
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Resume Next
End Sub
Function processFile(session As NotesSession, view As NotesView, currentDb As
NotesDatabase, Byval inputFile As Integer, NumberList List As String) As Integer
Dim IncidentInList As Integer
Dim record As String
Dim IncidentID As String
Dim OldIncidentID As String
Dim key As Variant
Dim IncidentDoc As NotesDocument
Dim Counter As Integer
On Error Goto ERRORHANDLER
processFile = True
Counter = Cint(session.GetEnvironmentString("ImportRecordCounter"))
Do While Not Eof(inputFile)
Line Input # inputFile,record
record=record & gFieldDelimiter
IncidentID = getField(record, gIncidentIDFieldPosition, gFieldDelimiter)
IncidentInList = Iselement(NumberList(IncidentID))
If IncidentInList Then
key = IncidentID
Set view = currentdb.GetView(gIncidentNumViewName)
Set IncidentDoc = view.GetDocumentByKey(Key)
If (Not IncidentDoc Is Nothing) Then
Call OverwriteIncident(currentdb,session, IncidentDoc, record, IncidentID)
End If
Else
Call createIncident(currentdb, session, record, IncidentID)
End If
NumberList(IncidentID) = IncidentID
Counter = Counter + 1
OldIncidentID = IncidentID
Loop
Call session.SetEnvironmentVar( "ImportRecordCounter", Cstr(Counter) )
Exit Function
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the processFile
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Exit Function
End Function
Function CreateIncident(db As NotesDatabase, session As NotesSession, record As String,
IncidentID As String) As Integer
Dim IncidentDoc As New NotesDocument(db)
On Error Goto ERRORHANDLER
createIncident = True
IncidentDoc.docKey = IncidentDoc.universalid & session.UserName
IncidentDoc.Form = "Incident"
IncidentDoc.DateImported = Now
IncidentDoc.LastImportDate=Now
Forall positions In gFieldList
IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,
gFieldDelimiter))
End Forall
IncidentDoc.Save True,True
Exit Function
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the CreateIncident
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Exit Function
End Function
Function getField(record As String, Byval position As Integer, delimiter As String) As
String
Dim i As Integer
Dim fieldStart As Integer
Dim fieldEnd As Integer
getfield=""
On Error Goto ERRORHANDLER
fieldStart = 0
fieldEnd = 0
For i=1 To position
fieldStart = fieldEnd + 1
fieldEnd = Instr(fieldStart, record, delimiter)
If fieldEnd=0 Then Exit For
Next
If fieldEnd>0 Then
getfield = Mid(record, fieldStart, (fieldEnd - FieldStart))
End If
Exit Function
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the getField
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Exit Function
End Function
Function dbColumn(DB As NotesDatabase, view As NotesView, Byval columnNumber As Integer,
columnValues List As String) As Integer
'Variable Declarations
Dim value As Variant
Dim doc As NotesDocument
On Error Goto ERRORHANDLER
Erase columnValues
Set doc = view.getFirstDocument
While Not (doc Is Nothing)
value = doc.columnValues(0)
If Isnumeric (value) Then
value = Cstr(value)
End If
columnValues(value) = value
Set doc = view.getNextDocument(doc)
Wend
dbColumn = True
Exit Function
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the dbColumn
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Exit Function
End Function
Function OverwriteIncident(db As NotesDatabase, session As NotesSession, IncidentDoc As
NotesDocument, record As String, IncidentID As String ) As Integer
On Error Goto ERRORHANDLER
IncidentDoc.LastImportDate=Now
Forall positions In gFieldList
IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,
gFieldDelimiter))
End Forall
IncidentDoc.Save True,True
OverwriteIncident = True
Exit Function
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the OverwriteIncident
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Exit Function
End Function
Sub sendMail(session As NotesSession, Currentdb As NotesDatabase, ImportRecordCounter As
String,fileCount As Integer)
On Error Goto ERRORHANDLER
Now send a mail memo telling how many files and
'records were processed today.
Set EMailDoc = New NotesDocument(Currentdb)
Set Body = New NotesRichTextItem(EMailDoc, "Body")
ImportRecordCounter = session.GetEnvironmentString( "ImportRecordCounter")
EMailDoc.sendTo = session.Username
ret = Evaluate("@unique(SendTo)",EMailDoc)
EMailDoc.SendTo = ret
EMailDoc.Form = "Memo"
EMailDoc.principal = "ImportIncidents*Robot"
EMailDoc.Subject = "Incident Import Activity Report"
intro$ = "This is an automated notification. The incident Import Agent last ran at " &
Time$() & " on " & Format(Now(), "Long Date")
BodyTextA = Cstr(fileCount) & " File(s) have been processed."
BodyTextB = ImportRecordCounter & " Record(s) have been processed."
Call Body.appendText(Intro$)
Call Body.AddNewLine(2)
Call Body.appendText(BodyTextA)
Call Body.AddNewLine(2)
Call Body.AppendText(BodyTextB)
'Don't save the mail message in the Email Import db after it's sent
EMailDoc.SaveMessageOnSend = False
'Send the new mail notification for this import run
Call EMailDoc.Send(False)
Call session.SetEnvironmentVar("RecordCounter", "0")
Exit Sub
ERRORHANDLER:
errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main
sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
Msgbox errormsg
Resume Next
End Sub