Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Multivalue Column - Split - New Beginning

Posted on 2006-05-09
68
Medium Priority
?
413 Views
Last Modified: 2013-12-18
Ok, let me start at the beginning. I have a database with a form called Yieldsheet1 and views called YieldImport and YieldImportSplit.  I use Marilyng's code to do an import from excel into YieldImport.  I have it in an agent which is started from a button in the view. The agent for this is called ImportExcel3.  Here is the button code:

@Command([ToolsRunMacro];"ImportExcel3");

@Command([ToolsRunMacro];"GetSales");

@Command([ToolsRunMacro];"GetSales2")

The other 2 agents are discussed later.

 Here is the code for the import agent:

Sub Initialize
Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Set db = ses.currentdatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      
      On Error Goto Handle_Error    
      
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
      Dim importlog As NotesLog
      Dim sendTo As String
      
      
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
      SendTo="My Name"
      
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
      Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
      On Error Goto Handle_Error
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
     'Choose Excel file
      XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
      
     'get Excel Object
      Print "Preparing to import..."
      Set App = CreateObject("Excel.Application")
      If App Is Nothing Then
            Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
            Exit Sub
      End If
      
      App.Visible = False      
      App.Workbooks.Open XLFile
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      Set db = ses.CurrentDatabase
      Set view =  db.GetView("YieldImport")    
      Dim importCnt As Long ,Batch As String, skipCnt As Long
      
      importCnt = 0
      skipcnt = 0
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
            Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
            Set importdoc = view.GetDocumentByKey(Batch, True)          
            If importdoc Is Nothing Then  
              'Then create the document if there is no match
                  Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1
               'Goto nextRow
            End If    
            With importdoc
                  .form = "YieldSheet1"
                  
                  .batch = batch
                  .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
                  .save True, False,True    
            End With          
            importCnt = importCnt + 1    
NextRow:
            row = row + 1
            
      Wend
      
      On Error Goto 0
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used
      
'----------------------------------------
      Call view.Refresh
      
      Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
      Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
      Call importlog.close
      Exit Sub
Handle_Error:
      On Error Goto 0
      If Not importlog Is Nothing Then
            Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
            Call importlog.close
      End If
      
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used  
End Sub  

The button that starts this agent also starts two other agents afterwards.  They are called GetSales and GetSales2. They do lookups to another database to populate the so and so2 fields on Yieldsheet1. so and so2 are text fields and editable with a default value of "".

Here are their codes respectively:

FIELD so := @DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV";batch;2);SELECT @All

FIELD so2 := @DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV2";batch;2);SELECT @All

After this is all done the view appears to look ok.  The first column is sorted and categorized (batch) .  The second column in the import view is the only one with a formula in it and is set to show multiple values as separate entries.  The rest of the columns are just straight field values.  Here is the second column formula.

@Trim(@If(!@IsError(so);" OOTS Batch 1 - " + so;"OOTS Batch 1 - Na"):@If(!@IsError(so2);"OOTS Batch 2 - " + so2;"OOTS Batch 2 - Na"))

Now after the import button is hit, it seems that everything works ok and the view looks ok. But, if I open a document, edit it, and save it , any empty so or so2 fields get errors in them. The same thing happens if I Refresh All Docs in the view. That's the first problem.  Now once this is fixed, I want to go into a flat version of the view which I have created to split the documents where so or so2 have multiple values.  That view is called YieldImportSplit.

Now, if all this can be done in 1 script shot, wow, that would be great, even the dblookups.  But if not I will be greatful for anything that works or any suggestions.

I hope this is somewhat clearer than the last question.

0
Comment
Question by:schmad01
  • 38
  • 26
  • 4
68 Comments
 

Author Comment

by:schmad01
ID: 16639419
I can email a copy of the database to anyone that is interested or if that would help.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16641223
I think you'd better rewrite those two agents GetSales and GetSales2 into one, seems a lot easier to me. Important question: do they run on all documents??

What I'd like to know, before we start coding: what are so and so2? Are they related? How do you want the duplicate documents to be created? Are they always pairs, I mean: does a duplicate document have to be created per so/so2-couple?

My suggested strategy:
- open databases and views
- for all documents present (where??)
-   get so and so2
-   forall values in so and so2
-      create duplicate documents
-   end forall
- next
0
 

Author Comment

by:schmad01
ID: 16641950
I am using these hidden field to hold the data from the dblookups.  Let me try to explain this that makes sense. After the excel import, we now have a key which is "batch".  The dblookups use batch to find a match in documents in the remote database "oots4.nsf". The documents in oots4 may have 1 or 2 batch numbers on it. The batch numbers may be in fields called batchnum or batchnum_1. The oots4 document also has 1 sales order field called Sales_order.  

The dblookups look for any match for "batch" in batchnum or batchnum_1. If it finds a match, even if it is in the same document the Sales order number is returned. so is for batchnum and so2 is for batchnum_1.

If it finds multiples, I want them to be brought back as their own documents, but I also don't want to lose any field data from the import.  The reason I want to do this is because then I want to use the Sales order number as a key lookup for something else.

Confusing?  Hit me with more questions.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:schmad01
ID: 16642039
Oh , to answer your question, yes the agents run on all documents in the view.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16644082
Hi schmad01,
> FIELD so := @DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF";
> "SOLV";batch;2);SELECT @All

> FIELD so2 := @DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF";
> "SOLV2";batch;2);SELECT @All

Just going to make this comment..

You have an agent that collects and imports documents from excel.  

After the import you have another agent that sets field SO...

So, the main problem with your dblookup is no error trapping:

thisval:=@DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV";batch;2);
thisVal2:=@if(@isError(thisVal)|thisVal="";"";ThisVal);
FIELD so := thisVal2;


Regards!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16647558
No, it's not confusing. I'm quite busy at the moment, so I'll pick this up later today, in 5-6 hours from now (or so).
0
 

Author Comment

by:schmad01
ID: 16648456
Ok, thanks to Marilyn's error trapping and sjef's suggestion of combining the two, that part is working good now.  So, what I have so far when the Import from Excel Button is clicked is the execution of 2 agents, the import agent, and the GetSales agent. Here is the new code for GetSales:

thisval:=@DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV";batch;2);
thisVal2:=@if(@isError(thisVal)|thisVal="";"";ThisVal);
FIELD so := thisVal2;

thisval3:=@DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV2";batch;2);
thisVal4:=@if(@isError(thisVal3)|thisVal3="";"";ThisVal3);
FIELD so2 := thisVal4;

0
 

Author Comment

by:schmad01
ID: 16648468
So all that is left (which is still alot I know) is to get these multivalues split into multiple documents without losing all the other fields information as well.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16653684
Ah, now you're going to have to make a list of what values need to stay, and what you are splitting- the point of confusion last time.
if


FullDoc
value1= "apples"
value2="Pears"
value3="Grapefruit"
so="one":"two":"three"
so2="four":"five":"six"

Doc1
  value1
  value2
  value3
  so="one"
  no so2

Doc2
value1
  value2
  value3
  so="two"
  no so2

Doc3
value1
  value2
  value3
  so="three"
  no so2

Doc4
value1
  value2
  value3
  so2="four"
  no so

and so on...

0
 

Author Comment

by:schmad01
ID: 16654864
Ok, you have the right idea, so do I need to tell you every field in the form or just imported column fields?
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16655018
Actually, I'm trying to get to the point where sjef wanted clarification.  So if that is what you want, I'll leave it to sjef to decide if he wants to continue.  But, I will say, I was right :O)

I'm guessing that you want the entire doc copied except for the SO and SO2 fields.  What do these turn into? Just SO if it was in SO and SO2 if it was in SO2?

Also, did we decide what happens with the old forms?  
0
 

Author Comment

by:schmad01
ID: 16655155
Actually, I'm trying to get to the point where sjef wanted clarification.  So if that is what you want, I'll leave it to sjef to decide if he wants to continue.  But, I will say, I was right :O)

I'm guessing that you want the entire doc copied except for the SO and SO2 fields.  What do these turn into? Just SO if it was in SO and SO2 if it was in SO2?  Correct on so and so2

Also, did we decide what happens with the old forms?   Either create new singles or leave them as singles with just the first so and so2 values.
0
 

Author Comment

by:schmad01
ID: 16660768
In other words I don't care if the documents with multiple values in the so and so2 field gets deleted , then replaced or just modified with so and so2's first values, whichever is easiest.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16663509
so and so2's first value << what does this  mean?
0
 

Author Comment

by:schmad01
ID: 16664864
I mean if a document has mutiple values in so or so2, then from what I understand I have 2  choices.

1.Split them into multiple documents and delete the originals.

2 Use so and so2's first values for the original documents and split the rest into new documents.

Whichever is easiest to accomplish is fine with me.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16666975
I think the general idea here would be:
- create a document
- if there are more than one so and so2 values
    - duplicate the document, and give each document one set of the so,so2 values

Here's the import agent again, modified this time:

Sub Initialize
     Dim ws As New NotesUIWorkSpace
     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim importdoc As NotesDocument
     Dim dupdoc As NotesDocument
     Dim ub As Integer
     
     On Error Goto Handle_Error    
     
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
     Dim importlog As NotesLog
     Dim sendTo As String
     Dim soldb As NotesDatabase
     Dim solv As NotesView
     Dim solv2 As NotesView
     Dim so As Variant
     Dim so2 As Variant
     Dim dosave As Integer
     
     Set soldb= ses.GetDatabase("USOWN002/Admin","LOCAL\\OOTS4.NSF", False)
     If Not soldb.IsOpen Then Exit Sub
     Set solv= soldb.GetView("SOLV")
     Set solv2= soldb.GetView("SOLV2")
     
     
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
     SendTo="My Name"
     
     Set importlog = New NotesLog( "Import from Excel" )
     Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
     Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
     On Error Goto Handle_Error
     
     Dim App As Variant, Wbook As Variant, WSheet As Variant
     
     Dim RetVal As Variant
     Dim XLfile As String
     Dim row As Long
     
     'Choose Excel file
     XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
     
     'get Excel Object
     Print "Preparing to import..."
     Set App = CreateObject("Excel.Application")
     If App Is Nothing Then
          Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
          Exit Sub
     End If
     
     App.Visible = False      
     App.Workbooks.Open XLFile
     Set Wbook = App.ActiveWorkbook
     Set WSheet = Wbook.ActiveSheet
     
     Set db = ses.CurrentDatabase
     Set view =  db.GetView("YieldImport")    
     Dim importCnt As Long ,Batch As String, skipCnt As Long
     
     importCnt = 0
     skipcnt = 0
     
     row = 2 '<--- skip the column header use 1 if your file has no column header
     While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
          Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
          Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
               Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               'Goto nextRow
          End If    
          With importdoc
               .form = "YieldSheet1"
               
               .batch = batch
               .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
          End With          
          ' duplicate when multiple so's
          so= ViewLookup(solv, batch, 1) ' always returns an array
          so2= ViewLookup(solv2, batch, 1)
          ub= Ubound(so)
          If ub<Ubound(so2) Then
               ub= Ubound(so2)
          End If
          If ub>0 Then ' multiple values (Lbound is always 0)      
               For i= 1 to ub
                    Set dupdoc= newdoc.CopyToDatabase(db)
                    If i<=Ubound(so) Then
                         dupdoc.so= so(i)
                    End If
                    If i<=Ubound(so2) Then
                         dupdoc.so2= so2(i)
                    End If
                    dupdoc.Save True, False, True
               Next
          End If
          importdoc.so= so(0)
          importdoc.so2= so2(0)
          importdoc.Save True, False, True
          importCnt = importCnt + 1    
NextRow:
          row = row + 1
         
     Wend
     
     On Error Goto 0
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used
     
'----------------------------------------
     Call view.Refresh
     
     Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
     Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
     Call importlog.close
     Exit Sub
Handle_Error:
     On Error Goto 0
     If Not importlog Is Nothing Then
          Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
          Call importlog.close
     End If
     
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used  
End Sub  

Function ViewLookup(view As NotesView, key As Variant, nr As Integer) As Variant
     Dim doc As NotesDocument
     Dim dc As NotesDocumentCollection
     Dim result As Variant
     Dim count As Integer
     Dim i As Integer

     Set dc= view.GetAllDocumentsByKey(key, True)
     Redim result(0) As String ' returns "" by default
     count= dc.Count
     If count>0 Then
           Redim result(count-1)
           For i= 1 To count
                Set doc= dc.GetNthDocument(i)
                result(i-1)= doc.ColumnValues(nr)
           Next
     End If
     ViewLookup= result
End Function

Could you try it? I didn't check the code, so there could be typos...
0
 

Author Comment

by:schmad01
ID: 16667177
Ok, thanks, sjef, will try.
0
 

Author Comment

by:schmad01
ID: 16667223
I created the agent , no errors on save, tried to run it, and right off the bat, got the message No Resume.
0
 

Author Comment

by:schmad01
ID: 16667229
The email log says variant does not contain a container
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16667969
Ah drat...


Sub Initialize
     Dim ws As New NotesUIWorkSpace
     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim importdoc As NotesDocument
     Dim dupdoc As NotesDocument
     Dim ub As Integer
     
     On Error Goto Handle_Error    
     
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
     Dim importlog As NotesLog
     Dim sendTo As String
     Dim soldb As NotesDatabase
     Dim solv As NotesView
     Dim solv2 As NotesView
     Dim so As Variant
     Dim so2 As Variant    
     
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
     SendTo="My Name"
     
     Set importlog = New NotesLog( "Import from Excel" )
     Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
     Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
     On Error Goto Handle_Error
     
     Set soldb= ses.GetDatabase("USOWN002/Admin","LOCAL\\OOTS4.NSF", False)
     If Not soldb.IsOpen Then Exit Sub
     Set solv= soldb.GetView("SOLV")
     If solv Is Nothing Then Error 30001, "View SOLV not found"        
     Set solv2= soldb.GetView("SOLV2")
     If solv Is Nothing Then Error 30002, "View SOLV2 not found"

     Dim App As Variant, Wbook As Variant, WSheet As Variant
     
     Dim RetVal As Variant
     Dim XLfile As String
     Dim row As Long
     
     'Choose Excel file
     XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
     
     'get Excel Object
     Print "Preparing to import..."
     Set App = CreateObject("Excel.Application")
     If App Is Nothing Then
          Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
          Exit Sub
     End If
     
     App.Visible = False      
     App.Workbooks.Open XLFile
     Set Wbook = App.ActiveWorkbook
     Set WSheet = Wbook.ActiveSheet
     
     Set db = ses.CurrentDatabase
     Set view =  db.GetView("YieldImport")    
     Dim importCnt As Long ,Batch As String, skipCnt As Long
     
     importCnt = 0
     skipcnt = 0
     
     row = 2 '<--- skip the column header use 1 if your file has no column header
     While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
          Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
          Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
               Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               'Goto nextRow
          End If    
          ' get so and so2
          so= ViewLookup(solv, batch, 1) ' always returns an array
          so2= ViewLookup(solv2, batch, 1)
          With importdoc
               .form = "YieldSheet1"
               
               .batch = batch
               .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
               .so= so(0)
               .so2= so2(0)
               .Save True, False, True
          End With          
          ' duplicate when multiple so's
          ub= Ubound(so)
          If ub<Ubound(so2) Then
               ub= Ubound(so2)
          End If
          If ub>0 Then ' multiple values (Lbound is always 0)      
               For i= 1 to ub
                    Set dupdoc= importdoc.CopyToDatabase(db)
                    If i<=Ubound(so) Then
                         dupdoc.so= so(i)
                    End If
                    If i<=Ubound(so2) Then
                         dupdoc.so2= so2(i)
                    End If
                    dupdoc.Save True, False, True
               Next
          End If
          importCnt = importCnt + 1    
NextRow:
          row = row + 1
         
     Wend
     
     On Error Goto 0
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used
     
'----------------------------------------
     Call view.Refresh
     
     Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
     Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
     Call importlog.close
exitsub:
     Exit Sub
Handle_Error:
     On Error Goto 0
     If Not importlog Is Nothing Then
          Call importlog.LogAction("Error processing import on line " & Erl & ": " & Error$ & "-" & Err )
          Call importlog.close
     End If
     
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used  
     Resume exitsub
End Sub

S'pose it'll work better now...
0
 

Author Comment

by:schmad01
ID: 16668202
No Resume went away but error log still says this.  It appears nothing is happening when I run the agent.

error log:  Error processing import on line 84: Variant does not contain a container-184
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16668255
Ok. What's on line 84?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16668309
Maybe ViewLookup must be defined or called differently:
          so= ViewLookup(solv, CVar(batch), 1) ' always returns an array
          so2= ViewLookup(solv2, CVar(batch), 1)


0
 

Author Comment

by:schmad01
ID: 16668549
Nope, still nothing. doesn't even import.  Does the code still know that SOLV and SOLV2 is in a different database?  Just to let you know in case you haven't been monitoring, my original import code has changed slightly because of a scenario that popped up.  It looks like this:

Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Set db = ses.currentdatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      Dim viewProd As NotesView
      Set viewProd  =  db.GetView("YieldImportReleased")
      On Error Goto Handle_Error    
      
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
      Dim importlog As NotesLog
      Dim sendTo As String
      
      
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
      SendTo="David P Schmalzer"
      
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
      Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
      On Error Goto Handle_Error
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
     'Choose Excel file
      XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
      
     'get Excel Object
      Print "Preparing to import..."
      Set App = CreateObject("Excel.Application")
      If App Is Nothing Then
            Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
            Exit Sub
      End If
      
      App.Visible = False      
      App.Workbooks.Open XLFile
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      Set db = ses.CurrentDatabase
      Set view =  db.GetView("YieldImport")    
      Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
      
      importCnt = 0
      skipcnt = 0
      updateCount = 0
      
     'Setting a boolean value to chk if I need to skip stuff
      Dim docExists As Integer
      docExists = False
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
            Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
            Set importdoc = view.GetDocumentByKey(Batch, True)          
            If importdoc Is Nothing Then  
              'Then create the document if there is no match
                  Set importdoc = db.createDocument  
               'Set docexists to false
                  docExists = False
               'There is NO match, create new document <<mgl changed.
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                   '-----> check if IMPORTdoc already exists          
                  Set importdoc = view.GetDocumentByKey(Batch, True)          
                  If importdoc Is Nothing Then  
                        Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
                  End If
                  If importdoc Is Nothing Then  
              'Then create the document if there is no match
                        Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                        Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                        skipcnt = skipcnt +1
               'Goto nextRow
                  End If
            Else
                  docExists = True
                  updateCount = updateCount +1
            End If    
            With importdoc              
                  If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                        .form = "YieldSheet1"
                        .batch = batch
                  End If
                  .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
                  .save True, False,True    
            End With          
            importCnt = importCnt + 1    
NextRow:
            row = row + 1          
      Wend
      
      On Error Goto 0
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used
      
'----------------------------------------
      Call view.Refresh
      
      Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
      Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
      Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
      Call importlog.close
      Exit Sub
Handle_Error:
      On Error Goto 0
      If Not importlog Is Nothing Then
            Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
            Call importlog.close
      End If
      
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used    
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16669013
Nope, I used the older code, too busy with other things :(

Can you merge them yourrself, or reapply the changes you made??
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16669044
solv and solv2 use the database as you described in the @DbLookups, I think:

     Set soldb= ses.GetDatabase("USOWN002/Admin","LOCAL\\OOTS4.NSF", False)
     If Not soldb.IsOpen Then Exit Sub
     Set solv= soldb.GetView("SOLV")
     If solv Is Nothing Then Error 30001, "View SOLV not found"        
     Set solv2= soldb.GetView("SOLV2")
     If solv Is Nothing Then Error 30002, "View SOLV2 not found"

Can you disable the line with On Error Goto Handle_Error, and then walk through the code using the debugger? Even click Continue and wait vor the error to kick in?
0
 

Author Comment

by:schmad01
ID: 16669546
Took out the error handling, debugged and clicked on continue. It went through the script and did not stop but still got the error in the error log. And , still did not appear to do anything, import or anything else.
0
 

Author Comment

by:schmad01
ID: 16669548
And yes, once we get it working I will merge the solution into the updated code.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16670161
Huh? You clicked continue and it finished the code?? Can't be... You should never have got the error in the error log, because that indicates that the handler is still active.

A mistake...
    Set soldb= ses.GetDatabase("USOWN002/Admin","LOCAL\OOTS4.NSF", False)

There was one backslash too many on that line.
0
 

Author Comment

by:schmad01
ID: 16682428
Nope, still nothing happening. Not sure what to do next.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16682480
And you disabled errorhandling? What error do you get, still the same? What line does it refer to?
0
 

Author Comment

by:schmad01
ID: 16684758
The log refers to line 84, but no errors or stops in the debugger. I did disable the error handling.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16686221
I don't know what to say... except for: this is not possible :(

Can you create some test-database and an Excel-test file, that should work, then zip them together and send them to me? I'd really love to test this.
0
 

Author Comment

by:schmad01
ID: 16690010
Ok, will send as soon as I  can. Thanks.
0
 

Author Comment

by:schmad01
ID: 16690159
Not sure what your email address is.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16690260
See my EE-profile, somewhere in the middle...
0
 

Author Comment

by:schmad01
ID: 16690333
Coming across the pond now.
0
 

Author Comment

by:schmad01
ID: 16690964
Just checking if you got them.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16691366
There's something wrong with the ViewLookup function I made, it seems that GetAllDocumentsByKey doesn't support the ColumnValues property. Please replace the existing function with the code below. Humble apologies...

Function ViewLookup(view As NotesView, key As Variant, nr As Integer) As Variant
      Dim dc As NotesDocumentCollection
      Dim vec As NotesViewEntryCollection
      Dim ve As NotesViewEntry
      Dim result As Variant
      Dim count As Integer
      Dim i As Integer
      
      Set vec= view.GetAllEntriesByKey(key, True)
      Redim result(0) As String ' returns "" by default
      count= vec.Count
      If count>0 Then
            Redim result(count-1)
            For i= 1 To count
                  Set ve= vec.GetNthEntry(i)
                  result(i-1)= ve.ColumnValues(nr)
            Next
      End If
      ViewLookup= result
End Function

Btw, your code still contained the On Error... I'll test also with the db's you sent.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16691433
The OOTS4 you sent me doesn't contain a SOLV2 view... It's SOLV_2, the underscore is missing.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16691516
And the value used in the ColumnValues(index) needs to be one less...

Function ViewLookup(view As NotesView, key As Variant, nr As Integer) As Variant
     Dim dc As NotesDocumentCollection
     Dim vec As NotesViewEntryCollection
     Dim ve As NotesViewEntry
     Dim result As Variant
     Dim count As Integer
     Dim i As Integer
     
     Set vec= view.GetAllEntriesByKey(key, True)
     Redim result(0) As String ' returns "" by default
     count= vec.Count
     If count>0 Then
          Redim result(count-1)
          For i= 1 To count
               Set ve= vec.GetNthEntry(i)
               result(i-1)= ve.ColumnValues(nr-1)
          Next
     End If
     ViewLookup= result
End Function
0
 

Author Comment

by:schmad01
ID: 16692885
Ok, I think we are getting there , but came across a problem. In my normal import from excel button, I call the agent GETSales after the import. So, obviously I need to get the Get the sales orders before the split, so I need a script version of the code below to run after the import and before the split :  Possible?  I will compensate with more points in a new question if you like, because I think we (you really) are so close to the solution.  I did run the update against documents with sales orders that I already had in the view and it did seem to split them, although it looked like it also created documents with no sales orders. I am not sure. I want to run in a blank view to be sure when the code is complete.  And thanks for your comments on the form. I designed that. It still is in it's infancy.

thisval:=@DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV";batch;2);
thisVal2:=@If(@IsError(thisVal)|thisVal="";"";ThisVal);
FIELD so := thisVal2;
thisval3:=@DbLookup("":"NoCache";"USOWN002/Admin":"LOCAL\\OOTS4.NSF"; "SOLV2";batch;2);
thisVal4:=@If(@IsError(thisVal3)|thisVal3="";"";ThisVal3);
FIELD so2 := thisVal4;

SELECT @All

0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 16694626
Sorry, don't understand. The formula at the bottom is handled already during the import. The @DbLookup is done by the lines

          so= ViewLookup(solv, batch, 1) ' always returns an array
          so2= ViewLookup(solv2, batch, 1)

and splitting the document is handled by the lines after the first save:

          ' duplicate when multiple so's
          ub= Ubound(so)
          If ub<Ubound(so2) Then
               ub= Ubound(so2)
          End If
          ' ub contains the maximum upperbound of the arrays so and so2
          If ub>0 Then ' multiple values (Lbound is always 0)      
               ' don't forget, so(0) and so2(0) are already saved in the first document!
               For i= 1 to ub
                    ' make a copy of the imported document earlier created
                    Set dupdoc= importdoc.CopyToDatabase(db)
                    ' before setting the so and so2 fields, check if there are values
                    If i<=Ubound(so) Then
                         dupdoc.so= so(i)
                    End If
                    If i<=Ubound(so2) Then
                         dupdoc.so2= so2(i)
                    End If
                    ' and save the duplicate document
                    dupdoc.Save True, False, True
               Next
          End If

So, you don't have to run the GetSales agent anymore. But I could have misunderstood you... What's the problem?
0
 

Author Comment

by:schmad01
ID: 16698585
Oh, ok.  Let me check that out.  I didn't know that did the getsales part of it.  Will get back shortly.
0
 

Author Comment

by:schmad01
ID: 16699712
You were correct as usual.  Here is my final code in all it's glory.  I did some cutting and pasting from the newest version of the export along with your split changes.  It works perfectly:

'FinalImport9:

Option Public

Sub Initialize
      Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Set db = ses.currentdatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      Dim viewProd As NotesView
      Set viewProd  =  db.GetView("YieldImportReleased")
      On Error Goto Handle_Error    
      
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
      Dim importlog As NotesLog
      Dim sendTo As String
      
      
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
      SendTo="David P Schmalzer"
      
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
      Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
      On Error Goto Handle_Error
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
     'Choose Excel file
      XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
      
     'get Excel Object
      Print "Preparing to import..."
      Set App = CreateObject("Excel.Application")
      If App Is Nothing Then
            Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
            Exit Sub
      End If
      
      App.Visible = False      
      App.Workbooks.Open XLFile
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      Set db = ses.CurrentDatabase
      Set view =  db.GetView("YieldImport")    
      Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
      
      importCnt = 0
      skipcnt = 0
      updateCount = 0
      
     'Setting a boolean value to chk if I need to skip stuff
      Dim docExists As Integer
      docExists = False
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
            Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
            Set importdoc = view.GetDocumentByKey(Batch, True)          
            If importdoc Is Nothing Then  
              'Then create the document if there is no match
                  Set importdoc = db.createDocument  
               'Set docexists to false
                  docExists = False
               'There is NO match, create new document <<mgl changed.
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                   '-----> check if IMPORTdoc already exists          
                  Set importdoc = view.GetDocumentByKey(Batch, True)          
                  If importdoc Is Nothing Then  
                        Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
                  End If
                  If importdoc Is Nothing Then  
              'Then create the document if there is no match
                        Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                        Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                        skipcnt = skipcnt +1
               'Goto nextRow
                  End If
            Else
                  docExists = True
                  updateCount = updateCount +1
            End If    
            With importdoc              
                  If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                        .form = "YieldSheet1"
                        .batch = batch
                  End If
                  .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
                  .save True, False,True    
            End With          
            importCnt = importCnt + 1    
NextRow:
            row = row + 1          
      Wend
      
      On Error Goto 0
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used
      
'----------------------------------------
      Call view.Refresh
      
      Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
      Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
      Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
      Call importlog.close
      Exit Sub
Handle_Error:
      On Error Goto 0
      If Not importlog Is Nothing Then
            Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
            Call importlog.close
      End If
      
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used    
End Sub
Function ViewLookup(view As NotesView, key As Variant, nr As Integer) As Variant
      Dim dc As NotesDocumentCollection
      Dim vec As NotesViewEntryCollection
      Dim ve As NotesViewEntry
      Dim result As Variant
      Dim count As Integer
      Dim i As Integer
      
      Set vec= view.GetAllEntriesByKey(key, True)
      Redim result(0) As String ' returns "" by default
      count= vec.Count
      If count>0 Then
            Redim result(count-1)
            For i= 1 To count
                  Set ve= vec.GetNthEntry(i)
                  result(i-1)= ve.ColumnValues(nr-1)
            Next
      End If
      ViewLookup= result
End Function

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16700159
I'm happy for you! See you next time :)
0
 

Author Comment

by:schmad01
ID: 16701860
Well, maybe I spoke too soon. I thought it was working, but it isn't . I think I just combined the 2 scripts improperly.  Can you check this final code real quick for errors and redundancies?

Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Set db = ses.currentdatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      Dim viewProd As NotesView
      Dim dupdoc As NotesDocument
      Dim ub As Integer
      Set viewProd  =  db.GetView("YieldImportReleased")
      
      'On Error Goto Handle_Error    
      
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
      Dim importlog As NotesLog
      Dim sendTo As String
      Dim soldb As NotesDatabase
      Dim solv As NotesView
      Dim SOLV_2 As NotesView
      Dim so As Variant
      Dim so2 As Variant
      
      
      
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
      SendTo="David P Schmalzer"
      
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
      Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
      On Error Goto Handle_Error
      
      Set soldb= ses.GetDatabase("usown002/Admin","Local\OOTS4.nsf",False)
      If Not soldb.IsOpen Then Exit Sub
      Set solv =soldb.GetView("SOLV")
      If solv Is Nothing Then Error 30001, "View SOLV not found"
      Set solv_2= soldb.GetView("SOLV_2")
      If solv Is Nothing Then Error 30002, "View SOLV_2 not found"
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
     'Choose Excel file
      XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
      
     'get Excel Object
      Print "Preparing to import..."
      Set App = CreateObject("Excel.Application")
      If App Is Nothing Then
            Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
            Exit Sub
      End If
      
      App.Visible = False      
      App.Workbooks.Open XLFile
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      Set db = ses.CurrentDatabase
      Set view =  db.GetView("YieldImport")    
      Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
      Dim importCnt As Long ,Batch As String, skipCnt As Long
      
      importCnt = 0
      skipcnt = 0
      updateCount = 0
      
     'Setting a boolean value to chk if I need to skip stuff
      Dim docExists As Integer
      docExists = False
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
            Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
            Set importdoc = view.GetDocumentByKey(Batch, True)          
            If importdoc Is Nothing Then  
              'Then create the document if there is no match
                  Set importdoc = db.createDocument  
               'Set docexists to false
                  docExists = False
               'There is NO match, create new document <<mgl changed.
               'There is NO match, so DON't overwrite, but add to log
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                   '-----> check if IMPORTdoc already exists          
                  Set importdoc = view.GetDocumentByKey(Batch, True)          
                  If importdoc Is Nothing Then  
                        Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
               'Goto nextRow
                  End If
                  If importdoc Is Nothing Then  
              'Then create the document if there is no match
                        Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                        Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                        skipcnt = skipcnt +1
               'Goto nextRow
                  End If
            Else
                  docExists = True
                  updateCount = updateCount +1
            End If    
          ' get so and so2
            so= ViewLookup(SOLV, Cvar(batch), 2) ' always returns an array
            so2= ViewLookup(SOLV_2, Cvar(batch), 2)
            With importdoc              
                  If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                        .form = "YieldSheet1"
                        
                        .batch = batch
                  End If
                  .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
                  .so= so(0)
                  .so2= so2(0)
                  .save True, False,True    
            End With          
          ' duplicate when multiple so's
            ub= Ubound(so)
            If ub<Ubound(so2) Then
                  ub= Ubound(so2)
            End If
            If ub>0 Then ' multiple values (Lbound is always 0)      
                  For i= 1 To ub
                        Set dupdoc= importdoc.CopyToDatabase(db)
                        If i<=Ubound(so) Then
                              dupdoc.so= so(i)
                        End If
                        If i<=Ubound(so2) Then
                              dupdoc.so2= so2(i)
                        End If
                        dupdoc.Save True, False, True
                  Next
            End If
            importCnt = importCnt + 1    
NextRow:
            row = row + 1          
            
      Wend
      
      On Error Goto 0
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used
      
'----------------------------------------
      Call view.Refresh
      
      Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
      Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
      Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
      Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
      Call importlog.close
exitsub:
      Exit Sub
Handle_Error:
      On Error Goto 0
      If Not importlog Is Nothing Then
            Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
            Call importlog.LogAction("Error processing import on line " & Erl & ": " & Error$ & "-" & Err )
            Call importlog.close
      End If
      
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used    
      Resume exitsub
0
 

Author Comment

by:schmad01
ID: 16703463
Nevermind I'll figure it out.  If not I'll post another question.  Sooo.... close.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16706618
I found some errors, but I didn't study the changes you applied in detail. What were they for, what purpose, and what were the changes??

All my comments have a <--- in front of it.


Dim ws As New NotesUIWorkSpace
     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim importdoc As NotesDocument
     Dim viewProd As NotesView
     Dim dupdoc As NotesDocument
     Dim ub As Integer
     Set viewProd  =  db.GetView("YieldImportReleased")
     
     'On Error Goto Handle_Error    
     
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
     Dim importlog As NotesLog
     Dim sendTo As String
     Dim soldb As NotesDatabase
     Dim solv As NotesView
     Dim SOLV_2 As NotesView
     Dim so As Variant
     Dim so2 As Variant
     
     
     
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
     SendTo="David P Schmalzer"
     
     Set importlog = New NotesLog( "Import from Excel" )
     Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
     Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
     On Error Goto Handle_Error
     
     Set soldb= ses.GetDatabase("usown002/Admin","Local\OOTS4.nsf",False)
     If Not soldb.IsOpen Then Exit Sub
     Set solv =soldb.GetView("SOLV")
     If solv Is Nothing Then Error 30001, "View SOLV not found"
     Set solv_2= soldb.GetView("SOLV_2")
     If solv_2 Is Nothing Then Error 30002, "View SOLV_2 not found"  '  <--- tested on solv, now solv_2
     
     Dim App As Variant, Wbook As Variant, WSheet As Variant
     
'     Dim App As Variant, Wbook As Variant, WSheet As Variant  '  <--- declared twice
     
     Dim RetVal As Variant
     Dim XLfile As String
     Dim row As Long
     
     'Choose Excel file
     XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
     
     'get Excel Object
     Print "Preparing to import..."
     Set App = CreateObject("Excel.Application")
     If App Is Nothing Then
          Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
          Exit Sub
     End If
     
     App.Visible = False      
     App.Workbooks.Open XLFile
     Set Wbook = App.ActiveWorkbook
     Set WSheet = Wbook.ActiveSheet
     
     Set db = ses.CurrentDatabase
     Set view =  db.GetView("YieldImport")    
     Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
'     Dim importCnt As Long ,Batch As String, skipCnt As Long  '  <--- declared twice
     
     importCnt = 0
     skipcnt = 0
     updateCount = 0
     
     'Setting a boolean value to chk if I need to skip stuff
     Dim docExists As Integer
     docExists = False  '  <--- useless to initialize docExists outside the loop
     
     row = 2 '<--- skip the column header use 1 if your file has no column header
     While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
          Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
          Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
'               Set importdoc = db.createDocument   '  <--- useless, importdoc will be reused very soon
               'Set docexists to false
               docExists = False
               'There is NO match, create new document <<mgl changed.
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                '-----> check if IMPORTdoc already exists          
'               Set importdoc = view.GetDocumentByKey(Batch, True)          ' <--- why do you do this?? 12 lines earlier, you already tested it!
'               If importdoc Is Nothing Then  
                    Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
               'Goto nextRow
'               End If
               If importdoc Is Nothing Then  
              'Then create the document if there is no match
                    Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                    Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                    skipcnt = skipcnt +1
               'Goto nextRow
'                                                                               <--- why is there no ELSE here?? you found the document in viewProd...
               Else                                                         ' <--- these three lines added sb  
                    docExists = True
                    updateCount = updateCount +1
               End If
          Else
               docExists = True
               updateCount = updateCount +1
          End If    
          ' get so and so2
          so= ViewLookup(SOLV, Cvar(batch), 2) ' always returns an array
          so2= ViewLookup(SOLV_2, Cvar(batch), 2)
          With importdoc              
               If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                    .form = "YieldSheet1"
                   
                    .batch = batch
               End If
               .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
               .so= so(0)
               .so2= so2(0)
               .save True, False,True    
          End With          
          ' duplicate when multiple so's
          ub= Ubound(so)
          If ub<Ubound(so2) Then
               ub= Ubound(so2)
          End If
          If ub>0 Then ' multiple values (Lbound is always 0)      
               For i= 1 To ub
                    Set dupdoc= importdoc.CopyToDatabase(db)
                    If i<=Ubound(so) Then
                         dupdoc.so= so(i)
                    End If
                    If i<=Ubound(so2) Then
                         dupdoc.so2= so2(i)
                    End If
                    dupdoc.Save True, False, True
               Next
          End If
          importCnt = importCnt + 1    
NextRow:
          row = row + 1          
         
     Wend
     
     On Error Goto 0
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used
     
'----------------------------------------
     Call view.Refresh
     
     Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
     Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
     Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
     Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
     Call importlog.close
exitsub:
     Exit Sub
Handle_Error:
     On Error Goto 0
     If Not importlog Is Nothing Then
          Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
          Call importlog.LogAction("Error processing import on line " & Erl & ": " & Error$ & "-" & Err )
          Call importlog.close
     End If
     
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used    
     Resume exitsub


Last question: do you also want to duplicate the importdoc when duplicates already exist??
0
 

Author Comment

by:schmad01
ID: 16708544
The main change to the import part of it was this.  There is a hidden field called release. When a document documents are highlighted in the Yield Import view, and the Release to Production button is clicked, it would change the release field to "Yes" and move it to the YieldImportReleasedview. So , with that said, the newest import agent would check that view as well and if the document existed (still using "batch" as the key)it would update it and it would not dump another copy in the Yield Import view.  Again here was the working latest import code without the split option:

Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Set db = ses.currentdatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      Dim viewProd As NotesView
      Set viewProd  =  db.GetView("YieldImportReleased")
      On Error Goto Handle_Error    
      
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
      Dim importlog As NotesLog
      Dim sendTo As String
      
      
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
      SendTo="David P Schmalzer"
      
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
      Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
      On Error Goto Handle_Error
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
     'Choose Excel file
      XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
      
     'get Excel Object
      Print "Preparing to import..."
      Set App = CreateObject("Excel.Application")
      If App Is Nothing Then
            Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
            Exit Sub
      End If
      
      App.Visible = False      
      App.Workbooks.Open XLFile
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      Set db = ses.CurrentDatabase
      Set view =  db.GetView("YieldImport")    
      Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
      
      importCnt = 0
      skipcnt = 0
      updateCount = 0
      
     'Setting a boolean value to chk if I need to skip stuff
      Dim docExists As Integer
      docExists = False
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
            Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
            Set importdoc = view.GetDocumentByKey(Batch, True)          
            If importdoc Is Nothing Then  
              'Then create the document if there is no match
                  Set importdoc = db.createDocument  
               'Set docexists to false
                  docExists = False
               'There is NO match, create new document <<mgl changed.
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                   '-----> check if IMPORTdoc already exists          
                  Set importdoc = view.GetDocumentByKey(Batch, True)          
                  If importdoc Is Nothing Then  
                        Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
                  End If
                  If importdoc Is Nothing Then  
              'Then create the document if there is no match
                        Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                        Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                        skipcnt = skipcnt +1
               'Goto nextRow
                  End If
            Else
                  docExists = True
                  updateCount = updateCount +1
            End If    
            With importdoc              
                  If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                        .form = "YieldSheet1"
                        .batch = batch
                  End If
                  .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
                  .save True, False,True    
            End With          
            importCnt = importCnt + 1    
NextRow:
            row = row + 1          
      Wend
      
      On Error Goto 0
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used
      
'----------------------------------------
      Call view.Refresh
      
      Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
      Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
      Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
      Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
      Call importlog.close
      Exit Sub
Handle_Error:
      On Error Goto 0
      If Not importlog Is Nothing Then
            Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
            Call importlog.close
      End If
      
      
      Print "Disconnecting from Excel..."
      App.activeworkbook.close    
      App.Quit '// Close Excel
      Set App= Nothing '// Free the memory that we'd used    
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16708966
Did you read/use my previous post? I changed some lines in the code...

And what didn't work??
0
 

Author Comment

by:schmad01
ID: 16709159
Ok, you're right, your updated code works when it is run for the first time on an empty new YieldImport view. But when I run it again on a populated view, it seems to duplicate some documents not sure why that is happening. I need to be able run this against a populated or unpopulated view.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16709326
That's why I added a question at the bottom of that same post:

>  Last question: do you also want to duplicate the importdoc when duplicates already exist??

Documents are always duplicated when multiple so/so2 values exist. Can you indicate when this should not happen, how can one find out that a duplicate already exists?
0
 

Author Comment

by:schmad01
ID: 16709410
Sorry, I should have read that question.  The answer is no, I do not want to duplicate the importdoc when duplicates already exist. Let me know what you come up with so we can put this one to rest.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16709489
> Can you indicate...
How am I supposed to know what a duplicate is?? Can you explain? :)
0
 

Author Comment

by:schmad01
ID: 16709560
Ah that is a good question. I would think that after the import and split is run for the first time , any imports thereafter would have to search against batch and so and so2 for matches. Does that sound right?  
0
 

Author Comment

by:schmad01
ID: 16709625
Ok, I'm just thinking out loud here.

1:  Import excel spreadsheet into empty YieldImport view, then search Oots4 database for matching batch numbers and bring in sales order numbers so and so2, split documents with multiple so and so2's.

2: Import excel spreadsheet into populated YieldImport view only updating existing documents adding new documents if new batch number, then search Oots4 database for matching batch numbers and bring in sales order numbers so and so2, split new documents where so and so2 have multiple values, don't split documents where so and so2 have either no data or just 1 value.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16709799
I have some "supposes"...

Suppose the YieldImport view is empty, then there's nothing to worry about: happily duplicating everafter ;)

Suppose there are documents, and a matching document is found: which document is it??

Suppose someone changed the values so or so2 in the meantime, between two imports, what's to be done?? Delete all duplicates?

How is I to find out that a duplicate document already exists (if you understand what I mean)? Document, 2x so/so2, so 1 duplicate created, now there's an update apparently...

Your move...
0
 

Author Comment

by:schmad01
ID: 16710987
I will try to answer as best I can:

Suppose the YieldImport view is empty, then there's nothing to worry about: happily duplicating everafter ;)
Answer: Correct.

Suppose there are documents, and a matching document is found: which document is it??
Answer: If there are matches and so and so2 either have single or no values, then update the matches.

Suppose someone changed the values so or so2 in the meantime, between two imports, what's to be done?? Delete all duplicates?
Answer: That is why I am wondering if we should do a lookup on batch+so&so2. I would say if it finds matches on existing so and so2, just update the document's other fields as usual. If it finds an extra value not in the previous export, then create another document.

How am I to find out that a duplicate document already exists (if you understand what I mean)? Document, 2x so/so2, so 1 duplicate created, now there's an update apparently...
Answer: Again, if I understand your question correctly, and I think this to be true only when the view is not empty, do a lookup based on batch+so and so2. But also remember, if there is a match with batch, and there are no so and so2 values, the documents other fields should still get updated.

Is this confusing or what?  : )

Believe me with all the time and effort you have given to me for this I will throw some more points your way.
0
 

Author Comment

by:schmad01
ID: 16710995
By the way, have you tried your own tests on the database to see what I mean with duplicate documents? Or should I send you a copy of oots4 with more documents?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16713472
Oh, no, I think I have a pretty good idea of what's going on: duplicates are created indiscriminately, as I predicted. No sweat, let's use those duplicates... The lines with NEW are added or modified.

Dim ws As New NotesUIWorkSpace
     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim importdoc As NotesDocument
     Dim dc As NotesDocumentCollection ' NEW
     Dim viewProd As NotesView
     Dim dupdoc As NotesDocument
     Dim nextdupdoc As NotesDocument ' ADD
     Dim ub As Integer
     Set viewProd  =  db.GetView("YieldImportReleased")
     
     'On Error Goto Handle_Error    
     
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
     Dim importlog As NotesLog
     Dim sendTo As String
     Dim soldb As NotesDatabase
     Dim solv As NotesView
     Dim SOLV_2 As NotesView
     Dim so As Variant
     Dim so2 As Variant
     
     
     
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
     SendTo="David P Schmalzer"
     
     Set importlog = New NotesLog( "Import from Excel" )
     Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
     Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
     On Error Goto Handle_Error
     
     Set soldb= ses.GetDatabase("usown002/Admin","Local\OOTS4.nsf",False)
     If Not soldb.IsOpen Then Exit Sub
     Set solv =soldb.GetView("SOLV")
     If solv Is Nothing Then Error 30001, "View SOLV not found"
     Set solv_2= soldb.GetView("SOLV_2")
     If solv_2 Is Nothing Then Error 30002, "View SOLV_2 not found"  '  <--- tested on solv, now solv_2
     
     Dim App As Variant, Wbook As Variant, WSheet As Variant
     
'     Dim App As Variant, Wbook As Variant, WSheet As Variant  '  <--- declared twice
     
     Dim RetVal As Variant
     Dim XLfile As String
     Dim row As Long
     
     'Choose Excel file
     XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
     
     'get Excel Object
     Print "Preparing to import..."
     Set App = CreateObject("Excel.Application")
     If App Is Nothing Then
          Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
          Exit Sub
     End If
     
     App.Visible = False      
     App.Workbooks.Open XLFile
     Set Wbook = App.ActiveWorkbook
     Set WSheet = Wbook.ActiveSheet
     
     Set db = ses.CurrentDatabase
     Set view =  db.GetView("YieldImport")    
     Dim importCnt As Long ,Batch As String, skipCnt As Long, updateCount As Long
'     Dim importCnt As Long ,Batch As String, skipCnt As Long  '  <--- declared twice
     
     importCnt = 0
     skipcnt = 0
     updateCount = 0
     
     'Setting a boolean value to chk if I need to skip stuff
     Dim docExists As Integer
     docExists = False  '  <--- useless to initialize docExists outside the loop
     
     row = 2 '<--- skip the column header use 1 if your file has no column header
     While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
          Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
          Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
'               Set importdoc = db.createDocument   '  <--- useless, importdoc will be reused very soon
               'Set docexists to false
               docExists = False
               'There is NO match, create new document <<mgl changed.
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1              
               'Goto nextRow     <<See this was set to already overwrite the file
                '-----> check if IMPORTdoc already exists          
'               Set importdoc = view.GetDocumentByKey(Batch, True)          ' <--- why do you do this?? 12 lines earlier, you already tested it!
'               If importdoc Is Nothing Then  
                    Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
               'Goto nextRow
'               End If
               If importdoc Is Nothing Then  
              'Then create the document if there is no match
                    Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
                    Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                    skipcnt = skipcnt +1
               'Goto nextRow
'                                                                               <--- why is there no ELSE here?? you found the document in viewProd...
               Else                                                         ' <--- these three lines added sb  
                    docExists = True
                    updateCount = updateCount +1
               End If
          Else
               docExists = True
               updateCount = updateCount +1
          End If    
          ' get so and so2
          so= ViewLookup(SOLV, Cvar(batch), 2) ' always returns an array
          so2= ViewLookup(SOLV_2, Cvar(batch), 2)
          With importdoc              
               If Not docExists Then
                    'only set batch on new documents and you can also do or update other stuff here
                    .form = "YieldSheet1"
                   
                    .batch = batch
               End If
               .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
               .so= so(0)
               .so2= so2(0)
               .save True, False,True    
          End With          
          ' duplicate when multiple so's
          ub= Ubound(so)
          If ub<Ubound(so2) Then
               ub= Ubound(so2)
          End If
          ' handle existing duplicate documents   ADDed from here until...
          If docExists Then
               Set dc= view.GetAllDocumentsByKey(Batch, True)
               ' walk documents and update so/so2, skip saved importdoc and so(0)/so2(0)
               i=1
               Set dupdoc= dc.GetFirstDocument
               Do Until dupdoc Is Nothing Or i>ub
                    Set nextdupdoc= dc.GetNextDocument(dupdoc)
                    ' skip the importdoc that's already been updated
                    If dupdoc.UniversalId<>importdoc.UniversalId Then
                        dupdoc.so= ""
                        If i<=Ubound(so) Then
                             dupdoc.so= so(i)
                        End If
                        dupdoc.so2= ""
                        If i<=Ubound(so2) Then
                             dupdoc.so2= so2(i)
                        End If
                        dupdoc.Save True, False, True
                         i= i + 1
                    End If
                    Call dc.DeleteDocument(dupdoc) ' document is only removed from the collection, don't worry...
                    Set dupdoc= nextdupdoc
               Loop
               ' remove excess dup's
               If dc.Count>0 Then
                    Call dc.RemoveAll(True)
                End If
                ' any leftovers?
                Do Until i>ub
                        Set dupdoc= importdoc.CopyToDatabase(db)
                        If i<=Ubound(so) Then
                             dupdoc.so= so(i)
                        End If
                        If i<=Ubound(so2) Then
                             dupdoc.so2= so2(i)
                        End If
                        dupdoc.Save True, False, True
                        i= i + 1
                Loop
          Else ' end of ADDed, including this line, and one more line below
              If ub>0 Then ' multiple values (Lbound is always 0)      
                   For i= 1 To ub
                        Set dupdoc= importdoc.CopyToDatabase(db)
                        If i<=Ubound(so) Then
                             dupdoc.so= so(i)
                        End If
                        If i<=Ubound(so2) Then
                             dupdoc.so2= so2(i)
                        End If
                        dupdoc.Save True, False, True
                   Next
              End If
          End If ' ADD
          importCnt = importCnt + 1    
NextRow:
          row = row + 1          
         
     Wend
     
     On Error Goto 0
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used
     
'----------------------------------------
     Call view.Refresh
     
     Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " total records")
     Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
     Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new records")
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
     Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
     Call importlog.close
exitsub:
     Exit Sub
Handle_Error:
     On Error Goto 0
     If Not importlog Is Nothing Then
          Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
          Call importlog.LogAction("Error processing import on line " & Erl & ": " & Error$ & "-" & Err )
          Call importlog.close
     End If
     
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used    
     Resume exitsub

Some explanation: the added code will take duplicates as long as they exist; if there are too many, the excess documents will be removed; if there are not enough, they will be created. Please watch it, it's dry coded, so I didn't even apply a syntax check in the Notes Designer, but I hope you'll get the drift.

EE ain't gonna like us, a question that's been going on for ages, with lots of code in it... :-D
0
 

Author Comment

by:schmad01
ID: 16717730
Oh man, sooooo close.  Here is what appears to be happening. I do an import on a fresh blank view.  Everything looks ok.

Then, I make a small change in the excel file just to make sure it updates the notes document, but when I hit the import for the second time, a bunch of documents in the view are deleted. It looks like it just keeps duplicates. Now, here is the kicker, I hit the import for a 3rd time and it then it looks fine, so it seem to do a delete on every other import.

Fyi, documents that I move into the released view are getting updated fine.

So, Sjef, I don't know if you know the answer to this, but what I am thinking is just run it twice on a click.

What do you think?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16717892
Would be absurd... I must have done something very wrong. Zo zorry, back to drawink board...
0
 

Author Comment

by:schmad01
ID: 16717966
I wouldn't say very wrong.  It is very very close.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16718327
Can't find it on ze drawink board :( Did you use my code exactly, copy/paste?
0
 

Author Comment

by:schmad01
ID: 16721253
Yep.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16722240
Oh shoot...

I forgot one case: if there's only one document... Search for the first line, with the comment, and modify these lines as follows:

               ' remove excess dup's
               Do Until dupdoc Is Nothing
                    ' skip the importdoc!!!
                    If dupdoc.UniversalId=importdoc.UniversalId Then
                         Call dc.DeleteDocument(dupdoc) ' remove document from the collection
                         Exit Do
                    End If
                    Set dupdoc= dc.GetNextDocument(dupdoc)
               Loop
               If dc.Count>0 Then ' still documents in the collection...
                    Call dc.RemoveAll(True)
                End If
                ' any leftovers?
0
 

Author Comment

by:schmad01
ID: 16733823
Where do I put this?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question