Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Refresh View using Lotusscript

Posted on 2007-08-09
28
Medium Priority
?
3,513 Views
Last Modified: 2013-12-18
I have an import from excel to lotus notes agent.
The agent import fields to the document and it does a lot of test date calculations

There is a specific view in the db which I urgent need to refresh after the import using the same LS agent as it does the recomputation of all the computed fields that I need on the doc and which are the basis of all the reports in the db.

Using an action in the view with @command([toolsrefreshalldocs]) and @command([toolsrefreshselecteddocd]); IS NOT AN OPTION
I know that I can create a scheduled agent to run in the background which has @command([toolsrefreshalldocs]) THAT ALSO IS NOT AN OPTION as the users may decide to import and directly extract a report from db which requires that the view is refresh
Its a very large db with over 100,000 docs.
In the script agent, I do have call view.refresh and uiw.viewrefresh but this works OK for currently open view, however, the view that Urgently needs refresh is not the currently open view during the import

Any help will be greatly appreciated

Thanks,
Varvoura
0
Comment
Question by:varvoura
  • 10
  • 8
  • 5
  • +2
28 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 19661361
You're close.

NotesUIWorkspace.ViewRefresh only refreshes the current view.
NotesView.Refresh can refresh any view in any database.

      Set view = db.GetView("Reports")
      Call view.Refresh()

0
 

Author Comment

by:varvoura
ID: 19668410
Hey bill,

Thanks for your prompt response.

I do have call view.refresh in my script which I didn't include in my problem description

But I have tried yours which has () to see if this was the problem

call view.refresh()

still not working

Something a bit weird about this thing

Any other ideas?

Thanks
0
 
LVL 22

Assisted Solution

by:Bill-Hanson
Bill-Hanson earned 400 total points
ID: 19669300
Hi Varvoura,

I'm still not clear on a couple of things.  In order yto run your report, I'm not sure whether you need to refresh the view, refresh the documents, or rebuild a view index.  It will depend on how your import agent updates the documents and how the report is run.

Can you describe how the report is run?  Also, what is the indication that you have now.  Basically, what is wrong with the report results?
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!

 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 400 total points
ID: 19673241
The LotusScript equivalent of [Refresh***Documents] is NotesDocument.ComputeWithForm.  I don't really like it much personally, but you CAN add it to your LotusScript just above the NotesDocument.Save, which will accomplish computing the form values before save as you create each document.
0
 

Author Comment

by:varvoura
ID: 19678957
Bill,

I need the view to be refreshed or more likely the document that has been created via import to be refreshed because the document has many other hidden calculated fields which are not created via the import and are the results of computation of other fields on the document.
EVERYTHING WORKS FINE WHEN I GO TO THAT VIEW AND CLICK THE REFRESH BUTTON WHICH HAS
@command([toolsrefreshalldocs]) but I can rely on that as the user will have to do the import and the view that needs refreshing is hidden from those users.
Hope I made things a bit clearer for you

Qwalatee,

I already had computeeithform just before doc.save(true, true) after each record is imported, however, this also doesn't work because I believe that computewithform does not work/refresh computed fields on the docs which in this case is my problem

Thank you both for your suggestions and hope you have more idea for me because I am running out of mine at this stage

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19683094
And is
      @Command( [ViewRefreshFields] )
an option??

By the way, why do you want a view that is not open to be refreshed? What do you need it for? If it's for the view that is opened on another workstation, well, that's a totally different question. There's an option in the view (in the Designer) what to do when Notes sees that there's a change. What's your goal?

Regards,
   Sjef
0
 

Author Comment

by:varvoura
ID: 19683221
Answers
@command([viewrefreshfields]) tried that one already...

Goal

I am importing documents from a spreadsheet to a db.
with this import routine I am also calculating test dates which are used in test scheduling
then I am calling the computewithform and the doc.save to save the document in the db

Now the document that has been created DOESN'T have all the fields that I have on the form as many of those probably around 20 fields are computed field of some sort. Some of those use @replace.... to get a value from 2 other field values and others are doing lookups.

Now because I can't set the value of those fields on import, I would need to refresh the doc so those fields can have their values recalculated. Hence my problem

I have tried nearly every refresh idea between view.refresh, computewithform(although I know it doesn't work with computed fields). I have even tried to
call doc.save(true, true)
call doc.computewithform(true, false)
call doc.save(true, true)
to enforce the save before and after the computewithform hoping for those field to calculate but still no luck

As I said, THE ONLY THING that works is when I place an action in the view
@command([toolsrefreshalldocs]) which then refreshed the documents and the fields then have their correct value.

HOWEVER, THIS IS NOT AN OPTION as this db will have over 100,000 docs. Can you only imagine if each time an import happens and all those docs are refreshed?

I hope you have more idea because I worn out all of mine

Thanks

0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 1200 total points
ID: 19683406
Is there a lot of script-code in the PostRecalc of the form? Would it be possible to move that code to a Script library, into a Sub (or Function), in order to call that Sub both from the PostRecalc and from the import?

Sub MyFormRecalc(doc As NotesDocument)
' place here the code from the PostRecalc event on the form
End Sub

From the agent, you can Call that Sub easily, with the document as parameter. You might also need a ComputeWithForm right before it. Just don't forget to Save afterwards!
0
 

Author Comment

by:varvoura
ID: 19683589
I have no code in the PostRecalc of the form at this stage. Everything usually happens on save when the document is created manually.
With the import, it is done via an agent which is triggered when an import button is clicked.

Are you saying that the postrecalc event triggers which each document that I am saving via the import?
If so, then maybe I could link the current ui document (source in postrecalc) to the doc(backend) still within the postrecalc event and then call a refresh on that doc

for example,

Sub PostRecalc(source As NotesUIDocument)
dim uiworkspace as new notesuiworkspace
dim doc as notesdocument
set uidoc= uiw.currentdocument
set doc = uidoc.document
call doc.computewithform(true, false)
call doc.save(true, true)
End Sub

Could that work?
I will try it but I am not sure if its feasible


0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19683660
No, sorry, nothing gets triggered during the Import, you've got to activate everything yourself. For the "PostRecalc", you can also read "QuerySave". If you put all LotusScript calculations in one Sub, you can call that sub both from the QuerySave and from the Import agent.
0
 
LVL 2

Expert Comment

by:Remi Gelinas
ID: 19684678
You could get your refresh simply by checking the option "Evaluate actions for every document change" of your view property (second tab).

Be carefull with this option as it can increase your processing time.
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 19684738
Ok, I'm clear on the situation now.

We can assume that view.Refresh is working properly and the problem lies with ComputeWithForm.

You can continue troubleshooting using your existing code using ComputeWithForm, or you can move all of your computations to a LotusScript function like sjef_bosman recommends.

If you want to troubleshoot using ComputeWithForm, here's what you need to do:

First, I assume that your code looks something like this:

' .. import some data to a NotesDocument.
Call doc.ComputeWithForm(True, False)
Call doc.Save(True, False)
' ... process some more documents.
Call view.Refresh()
Call ws.ViewRefresh()

(1) Leave your code as is.
(2) Turn the LotusScript debugger on and set a break point in the LotusScript debugger on the doc.Save line.
(3) Inspect the notes items using the debugger.
(4) Pick one field that did not compute correctly.
(5) Post your results back to this site.  I'll need to know the field's name, type, and all other settings from the first tab of the field's properties screen as well as the entire computed formula for that field.

If we take this one field at a time, I'm sure that we can get it working.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19687470
I like Bill Hanson's approach, becase I don't like ComputeWithForm.  However, for your situation, ComputeWithFOrm is probably easier.  Up to you.

And COmputeWithFOrm DOES work with computed fields.  I don't know why you t hink otherwise.  My current project would be dead without that.
0
 

Author Comment

by:varvoura
ID: 19691260
It must be a computewithform problem! or is it a response problem as the doc is a response document of a main, pls see below (doc1) is the main document

Call doc.MakeResponse(doc1)                                    
Call doc.ComputeWithForm(True, False)
                              
Call doc.save(True, True)

OK, this is the steps that I have taken to test this

1. inserted breakpoint on doc.save(true, true) which happens to be after the computewithform after
2. started the debugger, I have noticed that on doc.save(true, true) only 120 of the 140 fields which are on the documents are populated.
84 of those are populated via the import
10 others are set after the import and before the save
26 other just appear in the document properties fine with "" values which is fine because they don't have any value
The remaining 20 just don't appear at all UNITL I OPEN AND CLOSE THE DOCUMENT. WHAT WAS STRANGE IS THAT I DIDN'T EVEN HAVE TO SAVE THE DOCUMENT, JUST OPENNING AND CLOSING THE DOC WITHOUT SAVING POPULATES THE OTHER FIELDS ON THE DOCUMENT

Sample field (from those they don't populate when the document is saved

Name    projdates        

Type      Date/Time field (computed, allow multiple value)              

Formula  - @TextToTime(@Replace(@Text(testdates);@Text(actualcompleted);""))

Testdates is usually calculated in the import script
actualcompleted is a completed lookup field from the view

Even when I remove this formula and get "projdates" to be a computed field with the value of "testdates" which is the list of dates calculated/set in the import agent, still doesn't work

Real puzzle, its driving me crazy, I don't think that there is any from of refresh that I haven't tried yet.

0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 19691627
Is the "projdates" field on a subform?  If so, try moving it the the main part of the form and try again.  For the most part, this should not matter, but I seem to remember something about subforms and ComputeWithForm.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19692784
Did you ever look at the return value of ComputeWithForm? "False indicates that there are errors on the document".  Maybe there are some formulas that cannot be evaluated with a ComputeWithForm, for the right context might be missing.

Sjef
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19714188
>> JUST OPENNING AND CLOSING THE DOC WITHOUT SAVING POPULATES THE OTHER FIELDS ON THE DOCUMENT

You definitely have some script running in that form, and ComputeWithForm does not run scripts.  the scripts must calculate the additiona lfield and save the back end document.
0
 

Author Comment

by:varvoura
ID: 19714279
2 things

1. tried to check the return value of Computewithform as sjef advise and it seems to have a problem computing some of fields. I get the error 4000 : Field in note has wrong datatype. Of course, notes being notes you don't know which field which means that I would have to go though 140 fields to find out the offending line
Now, is there a way to find out the field name that has the error?

2. qwaletee

Yes the form has lots of script in its querysave and querypostsave events and this was created originally when the form was created and the product form needed to be created manually. The import just came about now to import the thousands of product records that a business has and after that they will be using the manual facility(create product record) each time they need to add a product
This being the case, i cannot at any stage remove the script from the querysave or postsave of the product form.
Now do you think they'll be another solution to this?

many thanks everyone for all your support and help
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19715356
It's usually not a text-field that generates this problem, but a numeric or date-type field. Most likely, in your import, you don't convert your data to the right type for the field. Or, if you do, you have the wrong conversion. If you have Notes R7, what happens when you open an imported document in the client? Since the Notes client performs a ComputeWithForm internally (so to speak), you should get an error. If not, the fualty field is corrected during the open, either in the QueryOpen or in the PostOpen.

Make sure you convert to the right datatype during the import.

Sjef
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19716592
I'm not saying remove the script.  Just the oppossite.  You need to incorporate code form the scrips into your import routine.  I can't tell you exactly what to incorporate, but the starting point would be to look at what is in QueryOpen, PostOpen, QuerySave, and PostRecalc
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19717135
As I suggested in my 2nd post in this question... :-))
0
 

Author Comment

by:varvoura
ID: 19722236
A stubborn little thing I am :-)
I'll test your suggestions guys and hopefully that'll be it

Thanks
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19735159
> As I suggested in my 2nd post in this question...

Ya, but unlikely to be that particular event. :p
0
 

Author Comment

by:varvoura
ID: 19736423
OK, finally this issue is resolved

How?

I was importing the code, then opening a view in which I was going to use its 3 columns as as key to check whether a specific document exists in the db so my routine won't import again, then I do the test dates calculations, then I decide to create the response doc, computewithform, then save

I kept all my code as iseverything as is and the only thing that I needed to do is to include another "computewithform" just after populating the fields from the import, then do all my other calculations, then invoke another computewithform and save.

I think sjef mentioned something similar to this when he suggested the postrecalc sub in the postings above.

I can't thank you all enough for all the great responses that you posted, they're certainly an eye opener.


So in summary this is what I did

...open the excel
doc.fielda = Trim(.Cells( row, 1).Value)
doc.customername=Trim( .Cells(row, 2).Value)
doc.fieldb=Trim( .Cells(row, 3).Value)
doc.fieldc=Trim( .Cells(row, 4).Value)
doc.fieldn=Trim( .Cells(row, 5).Value)

Then

call doc.computewithform

dim view as notesview
set view = db.getview("viewname")
dim key as string
set key = ...

Then

Start the calcualtion routine which is another couple of hundred lines

then

call doc.makeresponse(doc1) where doc1 is the doc picked up from view above
call doc.computewithform(true, false)
call doc.save(true, true)




                  
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19736458
Motto:

Do While code doesn't work
     Add some ComputeWithForm calls  
Done

Sjef :-))
0
 

Author Comment

by:varvoura
ID: 19736569
I like your Motto, I just hope that I don't start using this in the wrong context

0
 

Author Comment

by:varvoura
ID: 19736572
Now you all have been too good, how am I going to splits those points fairly then?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 19736900
Well, that's all up to you. If I may give you a suggestion: look at your real question (the one we actually solved), and then selected the answer(s) that helped you most or that came closest to the solution. Minor contributions could be allowed minor points. All the rest gets nothing, and that's all in the game.

Sjef
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview
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