Solved

# Previeous Question Caveat

Posted on 2006-05-01
278 Views
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

Author Comment

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

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

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

Author Comment

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

I will double check, though.
0

Author Comment

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

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

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

No, It's not the index. I tried an update index and it did nothing.
0

LVL 46

Expert Comment

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

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

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 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 And what's the name of the field that isn't appearing correctly in the view? 0 LVL 46 Expert Comment 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 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 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

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

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

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

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

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

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

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

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

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

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

Author Comment

Thanks again!
0

LVL 46

Expert Comment

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

## Featured Post

### Suggested Solutions

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…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…