• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

field types

Hi there,

I have this agent which imports data from a .txt to a notes database. The import seems to work fine, but I want to add some field validation on the imported data so that they are placed correctly on the form. I have done a brief field validation on the entire string, but i prefer if I can do it on each part of the string(imported array). Would that be possible, could I get some help with this small section.
Below is my script and I am currently working to resolve it.

Lotus Notes Database Synopsis - Generated at 07:49:52 PM on 08/05/2006
Agent Information
Name:      test working import
Last Modification:      08/05/2006 07:49:20 PM
Comment:      [Not Assigned]
Shared Agent:      No
Type:      LotusScript
State:      Enabled
Trigger:      Scheduled
Interval:      On Schedule Daily
Acts On:      All documents in database
LotusScript Code:
Option Public
Option Compare Nocase
Public Const str_INPUTFILE="C:\test.TXT"             'Imput filename
Sub Initialize
      On Error Resume Next
      Dim session As New NotesSession
      Dim db As notesdatabase
      Dim view As notesview
      Set db = session.currentdatabase
      Set view = db.getview("orderplan")
      Dim fileName As String
      Dim txt As Variant
      Dim fileNum As Integer
      Dim doc As NotesDocument
      Dim newdoc As notesdocument
      Dim itemA As NotesItem
      'enable agent manager logging
      Dim msg As String
      Dim agentLog As NotesLog
      Set agentLog = New NotesLog("Importing Franchise Stocklist.txt")
      Call agentLog.OpenMailLog("vkalouche@pirtek.com.au","Franchise Stocklist Import Log")
      Call agentlog.LogAction("Starting the Import agent...")
      'check if file exist
      If Not doesFileExist(str_INPUTFILE) Then
            msg ="Sorry, unable to locate " + str_INPUTFILE
            Call agentlog.LogAction(msg)
            Call agentlog.Close
            Exit Sub
      End If
      'populate the imported records in their equivalent document fields
      Dim ret As Variant
      Dim colNames(0 To 17) As String      
      colNames(0) = "test1"
      colNames(1) = "test2"
      colNames(2) = "test3"
      colNames(3) = "test4"
      colNames(4) = "testn"
      colNames(5) = "test_1"
      colNames(6) = "test_2"
      colNames(7) = "test_3"
      colNames(8) = "test_4"
      colNames(9) = "test_5"
      colNames(10) = "test_6"
      colNames(11) = "test_7"
      colNames(12) = "test_8"
      colNames(13) = "test_9"
      colNames(14) = "test_10"
      colNames(15) = "test_11"
      colNames(16) = "test_12"
      colNames(17) = "opproddesc"
      fileName$ = "test.TXT"
      Call view.AllEntries.RemoveAll(True)
' Get an unused file number so LotusScript can open a file.
      fileNum% = Freefile()
      Open fileName For Input As fileNum%
      Do While Not Eof(fileNum%)      
               ' Read each line of the file.
            Line Input #fileNum%, txt
            If txt <> "" Then
            '      ret = Split(txt, "      ")
                  Set doc = New NotesDocument(db)
                  doc.Form = "orderplan"
                  For x = 0 To 17
                        If Instr(txt,"      ") = 0 Then
                              lstr = txt
                              lstr = Strleft(txt,"      ")
                        End If
                        txt = Strright(txt,"      ")
                        'field handling
                        If Isdate(lstr)Then
                        Elseif lstr = " " Then
                              lstr = Cstr(lstr)
                        Elseif Isnumeric (lstr) Then
                              lstr =Cdbl(lstr)
                              lstr =Cstr(lstr)
                        End If
                        'end of field handling
                        Set item = doc.replaceItemValue(colNames(x), lstr)
                  Call doc.ComputeWithForm(False,False)
                  Call doc.Save( True, True )                  
            End If
      Call agentlog.LogAction("File Imported Successfully")
      Call agentlog.Close
      Close fileNum%
      Name fileName$ As Strleftback(Lcase(fileName$),".txt") & Format$( Today, "dd.mm.yyyy" ) & "old"
      Exit Sub
End Sub
Function doesFileExist(strPath As String) As Integer
      Dim tmpFile As String
      doesFileExist = False
      If strPath="" Then Exit Function
      On Error Goto NoFile_Error    
      tmpFile = Dir$(strPath)
      If Len(tmpFile)<>0 Then doesFileExist = True
      Exit Function
      Err = 0
      doesFileExist = False
      Exit Function
End Function
Sub Terminate
End Sub
Your help will be much appreciated.


  • 2
1 Solution
I am not quite sure from the cde what you are trying to do.

I would think that if you know what to expect in each of the 17 columbs, it would be much easier to do a select case statement in the for x section and
see if the values fit what is expected.

If not then write to a text or other log file the line and column of the error.

I hope this helps !

varvouraAuthor Commented:
Thanks for replying back, but this didn't resolve the problem.

colNames(4) is a numeric value, but for some reason whenever the import happens, it is not imported, hence creating other problems. When discussing with my manager, the problem would be resolved if I enable some sort of numeric field validation. I have tried using colNames(4) = cdbl(testn)... but nothing seems to work, when I check the document properties, i can see the field as "" of text type.

varvouraAuthor Commented:
Anyone else with any ideas? I've ran out of ideas and I need to hand this in the morning, so any help/ideas will be great
>Call doc.ComputeWithForm(False,False)

should be Call doc.ComputeWithForm(true,true) , so any validation that is defined in the form will raise errors. Capture the return value to save or not to save the doc


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now