Link to home
Start Free TrialLog in
Avatar of varvoura
varvoura

asked on

Refresh View using Lotusscript

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
Avatar of Bill-Hanson
Bill-Hanson
Flag of United States of America image

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()

Avatar of varvoura
varvoura

ASKER

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
SOLUTION
Avatar of Bill-Hanson
Bill-Hanson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Sjef Bosman
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
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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


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.
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.
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.
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.
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.

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.
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
>> 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.
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
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
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
As I suggested in my 2nd post in this question... :-))
A stubborn little thing I am :-)
I'll test your suggestions guys and hopefully that'll be it

Thanks
> As I suggested in my 2nd post in this question...

Ya, but unlikely to be that particular event. :p
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)




                  
Motto:

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

Sjef :-))
I like your Motto, I just hope that I don't start using this in the wrong context

Now you all have been too good, how am I going to splits those points fairly then?
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