Previeous Question Caveat

Everything is working fine with the question except with a computed field (a dblookup) on the form not showing up in the view. I am referring to this question:  http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21828050.html

After the import, the dblookup populates the field with the data and it shows up on the form, just not in the view for some reason.  Any ideas?
schmad01Asked:
Who is Participating?
 
marilyngConnect With a Mentor Commented:
I think the problem you're going to have is that you don't want to nest a computewithform command in the middle of your import... (not a good idea).

So, when you get to the end:

Set App= Nothing '// Free the memory that we'd used
view.Refresh

you would want to cycle through your view and refresh the documents:

Set App= Nothing '// Free the memory that we'd used
'-----------------------------------------------
'step through view documents
Dim vdoc as NotesDocument
   set vdoc =view.getfirstdocument
     while not vdoc is nothing
        vdoc.computewithform false,false
        vdoc.save
   set vdoc = view.getnextdocument(vdoc)
wend
set vdoc = nothing
'----------------------------------------
view.Refresh

0
 
schmad01Author Commented:
This is the dblookup formula.  so := @Text(@DbLookup("Notes":"Nocache" ; "Servername":"database.nsf"; "SOLV" ; batch ; 2 ));
@If( @IsError(so); "na"; so);

I also asked in another question if there is an equivalent to this in script. I thought maybe if the equivalent was tagged at the end of the import script, it might work.  Just thinking out loud. :)
0
 
Sjef BosmanGroupware ConsultantCommented:
Is the document saved AFTER the dblookup? Are the data saved in the field? If you look in the document properties of that field, are the contents as you expected?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Sjef BosmanGroupware ConsultantCommented:
It depends on when the dblookup is executed. If it is only executed when you open the document, then nothing will have been saved in the document itself.
0
 
schmad01Author Commented:
I assumed it was saved, because after doing the import, I go to the view open a document, and all the data is there. The data is correct.  All other fields show in the view except the computed field.
0
 
schmad01Author Commented:
I will double check, though.
0
 
schmad01Author Commented:
Just double checked. Did a fresh import, I was correct, the data imported ok, the dblookup worked, just did not show up in the view. I did a Refresh Selected Doc on one of them , then it showed up. So, should something be added to the script to refresh all docs in the view when complete, or something else?
0
 
Sjef BosmanGroupware ConsultantCommented:
Seems a contradiction to me: if the data are correct in the document and not in the view, then there's something wrong with the view. You must have used a different fieldname in the column formula. But only if the right fieldname appears in the Document Properties with the right data!
0
 
schmad01Author Commented:
There is only one field by that name. and it is set in that column in the view. Like, I said, it does show up after a Refresh All Docs in the view. Maybe it is not indexing right away? or is there a way to do the lookup within the import script instead of in the field?
0
 
schmad01Author Commented:
No, It's not the index. I tried an update index and it did nothing.
0
 
Sjef BosmanGroupware ConsultantCommented:
There's one thing that might prevent a document field to appear in a view: if it's not set to SUMMARY. That happens when the field is created using New NotesItem. When created (explicitly or implicitly) using ReplaceItemValue or AppendItemValue, SUMMARY will automatically be set.
0
 
schmad01Author Commented:
Here is the import agent that I am using (Thanks Marilyng) which works great. I think if I could just recalculate the documents once imported it will show in the view. I'm just not sure how to do this automatically.

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="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
      
      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
      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    
0
 
Sjef BosmanGroupware ConsultantCommented:
And what's the name of the field that isn't appearing correctly in the view?
0
 
Sjef BosmanGroupware ConsultantCommented:
I mean, if the field isn't created in the script above, where does it come from?

The ComputeWithForm call might seem a cure for all, but it shouldn't be necessary to apply it. There may be some simple explanation that the view isn't showing the information you expect. Using ComputeWithForm in this case seems like killing the cat because she's got just one flea. On the other hand, if it's a killer-flea...
0
 
schmad01Author Commented:
The field is called "so" and it is not part of the import.  I will try marilyng's idea as soon as I can. Have to tend to some support issues.  Some of us don't have the convenience of just developing. I am the I.T. department and must do everything else, too!  :)
0
 
Sjef BosmanGroupware ConsultantCommented:
So, if "so" is not part of the import, then you cannot show it in the view, isn't that so? All you'd have to do is combine the two scripts you got earlier, into

     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim solvview as NotesView
     Dim importdoc As NotesDocument
     Dim solvdoc As NotesDocument
     Dim key As String
     
     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")    
     Set solvview = db.GetView("SOLV")
     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))

                Set solvdoc = solvview.GetDocumentByKey(batch, True)
                If solvdoc Is Nothing Then
                  .so= "na"
                Else
                  .so= solvdoc.ColumnValues(1)
                End If
     
               .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
     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  


0
 
marilyngCommented:
agreed with the compute with form.. if the field is so, and it's doing those dblookups that trigger other dblookups, then the compute with form can be problematic, which is why I suggest doing  that part separately from the import.  Get the documents in, once in you can massage them to your heart's content.

However, I usually don't rely on computewithform, but push the values that I want into fields.

Like sjef says, it may not work, depending on what you have coded in the fields or forms.
0
 
schmad01Author Commented:
Understood.  I will try and see what happens.  If that doesn't work, I have already done the second suggestion by grabbing the data once the docs are in.
0
 
schmad01Author Commented:
Marilyng, tried putting this at the end:
'step through view documents
Dim vdoc as NotesDocument
   set vdoc =view.getfirstdocument
     while not vdoc is nothing
        vdoc.computewithform false,false
        vdoc.save
   set vdoc = view.getnextdocument(vdoc)
wend
set vdoc = nothing
'----------------------------------------
view.Refresh


Getting error for the line vdoc.save  :  Missing argument:SAVE
0
 
schmad01Author Commented:
sjef, tried yours, too and it stopped with a pop-up saying No Resume. I turned on script debugging and it steps all the way through.
0
 
Sjef BosmanGroupware ConsultantCommented:
At the end of the code, at Handle_Error at the end of the code I posted above, change these lines

     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  

into

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.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
 
marilyngCommented:
schmad01..

When you get an error like that you open designer and look up doc.save and see what other arguments it needs.   You would have had a solution by now. :)

doc.save true, false, true

or

call doc.save(true, false, true)


Sjef, nice addition to the code..
0
 
schmad01Author Commented:
I looked in the help, but I still don't understand what the true,false, true represents.  Can someone give an explanation?
0
 
Sjef BosmanGroupware ConsultantCommented:
Puzzled me as well. What are you referring to, Marilyn?

Btw, the last True will mark the changed document Read for you. With False, it would become Unread after the change.
0
 
marilyngCommented:
Ok, sjef, what are you talking about??

NotesDocument class SAVE method.

flag = notesDocument.Save( force, createResponse [, markRead ] )

I usually change documents to read that are created by an agent.  Depends on the application.

How could either of you not find this? Arrrrgh!
0
 
schmad01Author Commented:
Ah, got it. Ok, got Marilyng's addition to work. Now I have another question. Posting soon.
0
 
schmad01Author Commented:
Thanks again!
0
 
Sjef BosmanGroupware ConsultantCommented:
I don't understand at all what's been happening. Beep, next question, beep... :-S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.