Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Previeous Question Caveat

Posted on 2006-05-01
28
Medium Priority
?
280 Views
Last Modified: 2013-12-18
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?
0
Comment
Question by:schmad01
  • 14
  • 10
  • 4
28 Comments
 

Author Comment

by:schmad01
ID: 16576933
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16576934
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16576942
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:schmad01
ID: 16576952
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
 

Author Comment

by:schmad01
ID: 16576957
I will double check, though.
0
 

Author Comment

by:schmad01
ID: 16576977
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16577255
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
 

Author Comment

by:schmad01
ID: 16577289
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
 

Author Comment

by:schmad01
ID: 16577301
No, It's not the index. I tried an update index and it did nothing.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16577336
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
 

Author Comment

by:schmad01
ID: 16579108
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
 
LVL 18

Accepted Solution

by:
marilyng earned 200 total points
ID: 16582572
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16583916
And what's the name of the field that isn't appearing correctly in the view?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16584017
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
 

Author Comment

by:schmad01
ID: 16585622
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16585869
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16588089
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
 

Author Comment

by:schmad01
ID: 16588398
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
 

Author Comment

by:schmad01
ID: 16589878
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
 

Author Comment

by:schmad01
ID: 16589999
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16591003
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16591501
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
 

Author Comment

by:schmad01
ID: 16595684
I looked in the help, but I still don't understand what the true,false, true represents.  Can someone give an explanation?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16595734
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16598129
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
 

Author Comment

by:schmad01
ID: 16598274
Ah, got it. Ok, got Marilyng's addition to work. Now I have another question. Posting soon.
0
 

Author Comment

by:schmad01
ID: 16598276
Thanks again!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16598925
I don't understand at all what's been happening. Beep, next question, beep... :-S
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
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.
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

810 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