Updating all the documents in a database
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("","JobsRevi
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(d
Loop
Messagebox ("Completed updating days to go")
End Sub
It looks good to me, but it doesn't work. Can anyone fix it?
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("","JobsRevi
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(d
Loop
Messagebox ("Completed updating days to go")
End Sub
It looks good to me, but it doesn't work. Can anyone fix it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Andy is right ComputeWithForm does not compute all the fields and it took me a while to understand that within one of my apps.
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.
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.
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.
:)
:)
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.
:-)
:-)
ASKER
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
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
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.
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.
ASKER
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("","JobsRevi
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(d
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(d
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.c
Regards,
Stuart
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("","JobsRevi
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(d
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(d
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.c
Regards,
Stuart
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.
Please open a NEW question.
Sorry, I just saw you did that. See you there.
doc.ComputeWithForm( False, False )