Mail-In Database With Auto Extract Attachment - mapping error

Hello Experts,

I managed to get a mail-in database working. The db accepts an email with attachment. It then extracts the attachment to the view / form of the database.

The extract is SUPPOSE to lookup the view for a unique key, being a sorted column in View.

When the extract takes place... it messes my mapping completely and I just cannot find the issues....

Please help, db and sample doc attsched.

(the db file extention *.nsf was not accepted so I removed it... just OPEN WITH PROGRAM...)

bwstest.zip
car701015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
I don't intend to delve into your code, so can you please rephrase your question? I really don't understand what you want to do.

> I managed to get a mail-in database working.
Good.

> The db accepts an email with attachment.
I assume the db accepts ANY mail.

> It then extracts the attachment to the view / form of the database.
What is 'it' in this case? And what do you mean with 'view/form'? And how is the 'extract' done?

> The extract is SUPPOSE to lookup the view for a unique key, being a sorted column in View.
I'm lost... Why lookup? You're not creating a unique sequence number I hope?

> When the extract takes place... it messes my mapping completely and I just cannot find the issues....
What mapping?? What issues??

> Please help, db and sample doc attsched.
Maybe, if you can explain the questions above...
0
car701015Author Commented:
sjef_

You are right... What I am looking for is to see where I went wrong with this code..

Public Sub LoadDetail(fileCSV As String)
      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Set db = session.CurrentDatabase
      
      'Declare variables to hold data
      Dim eiref As String
      Dim edu As String
      Dim adu  As String
      Dim rls As String
      Dim tnf As String
      Dim dev As String
      Dim dlv As String
      Dim oah As String
      Dim oar As String
      Dim exd As String
      Dim cur As String
      Dim crc As String
      Dim customer As String
      Dim account As String
      Dim dv As String
      Dim mn As String
      Dim io As String
      
      Dim counter As Integer
      
      Dim View As NotesView
      Dim filenum As Integer
      
      Dim doc As NotesDocument
      
      counter =0
      
      'setup file number
      filenum% = Freefile()
      
      'If the user chose a file then process
      If Not Isempty(fileCSV ) Then
            Open fileCSV For Input As filenum%
            Do Until Eof(filenum%)
                  
                  'Read a line of data
                  Input #filenum%,  EIREF, EDU, ADU, RLS, TNF,DEV,DLV,OAH,OAR,EXD,CUR,CRC,DV,MN,IO
                  
                  'Create Notes document and write values to it
                  Set view = db.GetView ("View" )
                  Set doc = view.GetDocumentByKey (eiref)
                  
                  If (doc Is Nothing) Then
                        Dim result As Integer
                        Set doc = db.CreateDocument      
                  End If
                  
                  With doc
                        
                        '.customer = customer
                        '.account = account
                        .ei_ref = eiref
                        .edu = edu
                        .adu = adu
                        .rls = rls
                        .tnf = tnf
                        .dev = dev
                        .dlv = dlv
                        .oah = oah
                        .oar = oar
                        .exd = exd
                        .cur = cur
                        .crc = crc
                        .dv_ref = dv
                        .mn_ref = mn
                        .io_ref = io
                        .Form = "Form"
                  End With
                  
                  'save document
                  Call doc.save(False, False)
                  counter = counter +1
            Loop
            
      End If
      
End Sub

My fields and my import from the csv file doesnt match up... I dont know what I am doing wrong here...
0
Sjef BosmanGroupware ConsultantCommented:
The line
                  Set doc = view.GetDocumentByKey (eiref)
searches also for a partial match. Unless that's what you want, it's better to use the 2nd parameter as well:
                  Set doc = view.GetDocumentByKey (eiref, True)
And then
            Input #filenum%,  EIREF, EDU, ADU, RLS, TNF,DEV,DLV,OAH,OAR,EXD,CUR,CRC,DV,MN,IO
that line reads 15 values whereas a line from your input file like
QWERTY KEYBOARD,G9875321,7780067639,,30/01/2010,25/01/2010,,27/01/2010,,27/01/2010,27/01/2010,27/01/2010,27/01/2010,20/01/2010,BOBS Marketing,FCA,LCL
contains 17 values. Input# doesn't read one line at a time, but values, so the first 15 are used for the first document, then the last 2 of the first input line plus 13 values from the second line are used in the second document, etc.

If you use Input#, make sure your input matches the list of variables.
0
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.

car701015Author Commented:
Hi,

I have made the changes... still the mapping is off... I think it has to do with when a set of data contains nothing - eg: " " then it screws things up...

'If the user chose a file then process
      If Not Isempty(fileCSV ) Then
            Open fileCSV For Input As filenum%
            Do Until Eof(filenum%)
                  
                  'Read a line of data
                  Input #filenum%,  CUSTOMER,ACCOUNT,EIREF,EDU,ADU,RLS,TNF,DEV,DLV,OAH,OAR,EXD,CUR,CRC,DV,MN,IO
                  
                  'Create Notes document and write values to it
                  Set view = db.GetView ("View" )
            
                      'Set doc = view.GetDocumentByKey (eiref)
                  Set doc = view.GetDocumentByKey (eiref, True)
                  
                  If (doc Is Nothing) Then
                        Dim result As Integer
                        Set doc = db.CreateDocument      
                  End If
                  
                  With doc
                        
                        .customer = customer
                        .account = account
                        .ei_ref = eiref                        
                        .edu = edu
                        .adu = adu
                        .rls = rls
                        .tnf = tnf
                        .dev = dev
                        .dlv = dlv
                        .oah = oah
                        .oar = oar
                        .exd = exd
                        .cur = cur
                        .crc = crc
                        .dv_ref = dv
                        .mn_ref = mn
                        .io_ref = io
                        
                        .Form = "Form"
                        
                  End With
result.bmp
0
Sjef BosmanGroupware ConsultantCommented:
Forget the Input function, you need Input Line.

Here's a file that contains a class to handle CSV files: http://www.nsftools.com/tips/CSVReader.lss

See if you can use it.
0
car701015Author Commented:
I am lost now... sorry...
0
Sjef BosmanGroupware ConsultantCommented:
Lost? Really?? Let's try to find you...

How about this code (not tested!):
Use "CSVReader" ' you need to add the CSVReader.lss as a script library, using %Include "CSVReader.lss"

Public Sub LoadDetail(fileCSV As String)
      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Set db = session.CurrentDatabase
      
      'Declare variables to hold data
      Dim eiref As String   
      Dim counter As Integer
      
      Dim View As NotesView      
      Dim doc As NotesDocument

      Dim reader As CSVReader
      Dim data As Variant
      
      counter =0
          
      'If the user chose a file then process
      If Not Isempty(fileCSV ) Then
            ' setup reader
            Set reader= New CSVReader(fileCSV)
            Do Until reader.isEOF
                  'Read a line of data
                  data= reader.getNextLine
                  If Not IsNull(data) Then
                        eiref= data(2)
                        'Create Notes document and write values to it
                        Set view = db.GetView ("View" )
                        Set doc = view.GetDocumentByKey (eiref, True)                 
                        If (doc Is Nothing) Then
                              Set doc = db.CreateDocument      
                        End If                 
                        With doc                              
                              '.customer = data(0)
                              '.account = data(1)
                              .ei_ref = data(2)
                              .edu = data(3)
                              .adu = data(4)
                              .rls = data(5)
                              .tnf = data(6)
                              .dev = data(7)
                              .dlv = data(8)
                              .oah = data(9)
                              .oar = data(10)
                              .exd = data(11)
                              .cur = data(12)
                              .crc = data(13)
                              .dv_ref = data(14)
                              .mn_ref = data(15)
                              .io_ref = data(16)
                              .Form = "Form"
                        End With                  
                        'save document
                        Call doc.save(False, False)
                        counter = counter +1
                  End If
            Loop            
      End If      
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
car701015Author Commented:
ah, gotcha :)

works with ONE last thing... It does 100% what I want by imorting and placing where it must go... however, when a new upate file received, it doesnt update the existing record with the new information received.

The system should accept new records and update existing records, but not create duplicate records...

btw, thnaks so far... up the points on this... :)
0
Sjef BosmanGroupware ConsultantCommented:
Asking a lot er? ;-)

Apparently there's something wrong with
- the view: can you see the document in the view, is the first column sorted, is it a string, is it indeed ei_ref
- the document: does it contain the right values?

10 to 1 it's the view...
0
car701015Author Commented:
Thanks sjef_ Tried to up the points but no option... :( Thanks for the guidance... nice if you know what's happening :)

Regards

car
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.