?
Solved

Updating all the documents in a database

Posted on 2003-03-17
12
Medium Priority
?
357 Views
Last Modified: 2013-12-18
I have a database that I use for keeping track of all my tasks and projects.

It has a field in it for the date that tasks or jobs are due, called "Deadline", and one for today's date that it gets from @now. Also it has a field for "Days to go" ie the difference between the two from the formula (Deadline-Today)/86400. The form is set to "automatically refresh fields".

The problem is that after time, the values shown are wrong because todays date has changed. If I open each document, the field is not recalculated, but if I click on any field, it is. If I save the document when closing it, the "days to go" is then correct. But doing this on all the documents in the database is not practical.

So I'd like a script that opens each document in turn and then saves it, after doing something to cause the fields to be refreshed. It would preferably run from a button in the view. So far I have a button with the following script:

Sub Click(Source As Button)
     Dim db As NotesDatabase
     Dim view As NotesView
     Dim doc As NotesDocument
     Set db=New NotesDatabase("","JobsRevised.nsf")
     Set view=db.GetView("All summaries and tasks")
     Set doc=view.GetFirstDocument
     Do Until doc Is Nothing
          Call doc.Save( True, True )
          Set doc=view.GetNextDocument(doc)
     Loop
     Messagebox ("Completed updating days to go")    
End Sub

It looks good to me, but it doesn't work. Can anyone fix it?

0
Comment
Question by:StuartOrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 8155525
Call this method before saving the doc:

doc.ComputeWithForm( False, False )

0
 
LVL 5

Accepted Solution

by:
AndrewJayPollack earned 500 total points
ID: 8155763
Sub Click(Source As Button)
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim doc As NotesDocument
    Set db=New NotesDatabase("","JobsRevised.nsf")
    Set view=db.GetView("All summaries and tasks")
    Set doc=view.GetFirstDocument
    Do Until doc Is Nothing
         call doc.computewithform(false,false)
         Call doc.Save( True, True )
         Set doc=view.GetNextDocument(doc)
    Loop
    Messagebox ("Completed updating days to go")    
End Sub

HOWEVER -- COMPUTE WITH FORM DOES NOT ALWAYS COMPUTE ALL THE FIELDS.

Try this very simple @Function agent:

@Command([ToolsRefreshAllDocs])

as the only line in a function agent, set to run once.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 8159117
Andy is right ComputeWithForm does not compute all the fields and it took me a while to understand that within one of my apps.
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 5

Expert Comment

by:AndrewJayPollack
ID: 8159178
Back in 'the day' the "tools" pull down (notes v2) contained two options no longer in the menu systems.  These were "Refresh All Documents" and "Refresh Selected Documents".

These were removed later, mostly because end lusers would think they were refreshing a view, and instead were causing every document in the database to be marked "updated" which could cause application problems (depending on the design) and also caused "replication storms" and even huge numbers of replication conflicts if more that one user did it.

So...the function was too useful to remove, but too dangerous to give to non programmers.  In version 3 when @Command() was introduced, it was moved there.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 8159210
Nice History on Refresh Andy.  I had this problem of giving the option to end users last year and they made a huge mess which took me two full days to find what happened to documents and retrieve them to the original state.  Its a dangerous command as far my app is concerned though.

:)
0
 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8159260
yes, it can be a powerful tool, but dangerous in the wrong hands.  I deal with tools like that in my 2nd line of work.

:-)
0
 

Author Comment

by:StuartOrd
ID: 8176518
Help and lessons too! Thanks for that to all.

I've tried the suggestions, and this is what happened. I created several copies of the database so that I could try out the two answers and mess around a bit. You now need to know that there are 2 forms in the database each with these two fields - one is a main document and the other is a response document.

1. I put the extra line into the button's script, and ran it. Alas it didn't work - neither of the fields "Today" or "Days to go" were updated, no matte how many times I clicked it.

2. I changed the button to run the @ function. This partly worked.

a) When I run it once, I get the "Today" field being updated to the correct date in both forms. The "Days to go" field is updated in the main document, but not in the response document. All the documents in the database are treated the same way, depending on which form they use.
b) When I run it a second time, the "Days to go" field is correctly updated in the response documents as well.

3. I changed the button to run the @ function twice. This updates it all as it just automates the double button click above.

So we have a sort of solution, but it's a bit slow waiting for it to save all the documents twice and it's not as elegent as it might be. Is there any way to get the response documents to update properly on the first click of the button? The fields in the two forms are identical and in the same order on their form, although the forms obviously contain other fields that differ from each other.

Stuart
0
 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8176691
Stuart,  the issue is the order in which things compute, I'd guess.  The one that doesn't update (days to go) is based on the values in "today", right?

The first time updates the first field, teh second recalcs the second now that the first is updated.  My bet is the second is getting computed first.

You could run the function twice -- ick, or else just once but update the specified fields to their new value.

So you could use this code, in an agent that runs against all documents:

Select Today < @Date(@Now);
field Today := @Date(@Now);
Field DaysToGo := (whatever the formula on the form is for this field)

That would be faster.  You could also use:

Select @All;
field Today := @Date(@Now);
Field DaysToGo := (whatever the formula on the form is for this field)

In other words, hit the fields themselves.
0
 

Author Comment

by:StuartOrd
ID: 8182388
Hi Andy,

I've tried your two new suggestions now. They both seem to work OK - but only on the document selected. Sometimes it jumps down to the next document, other times it doesn't.


Neither one works if I've got nothing in the Deadline field as I get a field mismatch fault DTG field. This is odd as it doesn't happen normally when i'm using the form, just when the agent runs. When I open the document and click the field, it corrects itself immediately.

Seems like it would work if we could marry this first command with the looping in my original Script so that it does it to all documents, assuming I fix the mismatch problem which should be easy. I tried the @ command in the script but of course it's like mixing 2 languages - nil comprende!

Sub Click(Source As Button)
     Dim db As NotesDatabase
     Dim view As NotesView
     Dim doc As NotesDocument
     Set db=New NotesDatabase("","JobsRevised.nsf")
     Set view=db.GetView("11a Tasks and summaries")
     Set doc=view.GetFirstDocument
     Do Until doc Is Nothing
          Set FIELD Today = @Date(@Now);
          Set FIELD DaysToGo = (Deadline-Today)/86400;
          Call doc.Save( True, True )
          Set doc=view.GetNextDocument(doc)
     Loop
     Messagebox ("Completed updating days to go")    
End Sub

It won't accept the syntax of that, so I changed this last bit to -
        Do Until doc Is Nothing
          DaysToGo = (Deadline-Today)/86400;
          Call doc.Save( True, True )
          Set doc=view.GetNextDocument(doc)
     Loop

That compiles OK but doesn't work - I get the messagebox saying it's complete, but nothing has been updated. If I switch on the debug, for some reason it doesn't work properly - again I just get the messagebox with nothing updated and no debug screen coming up so I don't know how far it got.

So I've had another look at the order of the fields on the two forms. As I said before, the fields are in the same order on the two forms (ie Today is higher up on the form than DaysToGo). To emphasis this, I put a couple of lines between them in the response document, but that didn't change it. The fields in the main document are in a table, so I did the same with the fields in the response document, and it still behaved the same way. So it looks to me like it's something inherent in a response document.

Anyway, I figure this is costing more time than it's worth, so I'll plump for the double run of the Agent as a general update, and the new function as a way to update a single document within a view without opening it which is kinda handy, unless you have any new insight on the original problem. I'll credit the points to you. Thanks for your help! If they close off this question to further comments and you want to contact me, you can send to stuart.ord@process-notes.co.uk

Regards,

Stuart

0
 

Expert Comment

by:BlakeNielson
ID: 14197063
I have a very similar problem,  I have some lotus script that updates fields in a form and calls the computewithforms functions saves the document,  but I have to manually run an agent that calls ToolCommandRefreshallDocs before the view will function correctly. I have tried to call the agent from the lotus script but it does not work.  
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 14197515
Please open a NEW question.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 14197517
Sorry, I just saw you did that. See you there.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 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