Link to home
Start Free TrialLog in
Avatar of shuboarder
shuboarderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Agent to update all records

Hi,

I need an agent to update all records in a database periodically, i.e. I want it to open all documents for editing, then save and close them. (I need this in the event of related records changing)

1. How is this achieved?
2. How long will it take the agent? (approx. 4000 records currently)
3. Are there any issues I should be aware of? i.e. problems with this method?

Thanks for your help experts!
SOLUTION
Avatar of mshogren
mshogren

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
Avatar of Sjef Bosman
Could you elaborate somewhat on the reasons why you want to update ALL records periodically?
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
Avatar of shuboarder

ASKER

Ok,

I've not actually used agents before....

Where should this code go? I'm looking under Shared Code >> Agents

Do I then have to specify somewhere to use Lotus Script?
and how do I schedule this to run?

Thanks
Sjef,

I need to run an agent (I think) to update associated records. i.e. if for example two views relate to the same record, when one record is changed, the other will not be changed until it is opened for editing, saved and closed.

Is there a way to specify refesh document on open under Postopen or something, because this could be sufficient...
No comprendo... Might be my completely Notes-ified mind that "refuses" to understand.

> I need to run an agent (I think) to update associated records.
What are associated records? Documents with identical key(s)? What keys?

> i.e. if for example two views relate to the same record,
Views don't relate to records, views contain documents.

> when one record is changed, the other will not be changed until it is opened for editing, saved and closed.
You said two views? Where is the second record/document coming from??

What needs to be updated in the "other" document? That can also be done a lot quicker using a less general agent, i.e. by only looking for documents that need to be updated.
It's difficult to explain.

Basically if a form has lookups to a view, when the value in the view is changed, it will not change on the form that looks it up until that form is opened for editing, saved and closed.

Let me see...

Say View 1 looks like this:

Pizza Name:-----Sales:------Revenue:
Pollo-------------56-----------1000
Hawaii-----------89-----------800

View 2 contains:

Pizza Name:-----Ingredients--------------------------Qty:
Pollo--------------Cheese, Mushrooms---------------9
Hawaii-----------Cheese, Pineapple------------------15

The form for view 2 has a lookup field for pizza name, meaning it must exist in view 1 first
On selecting the pizza name from the dialog list I populate calculated fields on the form to show the sales and revenue

Now.....if a person edits the sales and revenue in the other view
when the form for view 2 is opened for preview it will still hold the old data until the form is refreshed by pressing F9

So I either need an agent to run through all records and refresh them, or a formula which automatically refreshes all fields upon opening a form.

This has absolutely nothing to do with what I'm doing, but I hope everyone can still see what I'm trying to do in principle.

Thanks for all your helps so far.
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
No, when I say it has nothing to do with what I'm doing, I mean I made the whole pizza thing up...

Thanks for the computed for display thing - very useful

(Phew no need for agents!) - Is there a way to refresh the form (computed for display fields) on opening it?

Thanks.....nearly there I think!
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 fully understood that you made up the pizzas, and I can see you did get my drift as well ;)
Hmmmm.....

mshogren, the calculated for display fields are still only updating when I open the document for editing, save and close it and reopen it.

Back to your code fragment then..... where does this go? what else do I need?

Thanks

 
Arrrrrghhhhh!!!

I have a play with that code....when I clicked run agent the little yellow bolt of lightning in the bottom left hand corner has started flashing and it appears to be doing stuff.

Its now been at it for a while, I don't know if this is good or bad?
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
If there are still fields int the document with the same name as the CfD-fields on the form, you can do 3 things:
1) Delete those fields from all documents in the database
2) Delete those fields when the form opens
    In the form's QueryOpen, add some Formula code:
        Field YourField:= @DeleteField;
3) Rename the CfD fields so they won't display existing values

Option 3 is the easiest...
Thanks for the reply Sjef,

where does this code go within the agent?

I have the following:

Document Selection
(Options)
(Declarations)
Initialize
Terminate
This is what I have tested, and I still get the yellow lightning bolt in the bottom left hand corner for ages:
It seems to be doing something, but I have seen an agent work through thousands of records in seconds,
so I think there must still be something wrong....?

Document Selection:

uses 'details' form

(Options):

Option Public

(Declarations):

Dim db As NotesDatabase
Dim col As NotesDocumentCollection
Dim doc As NotesDocument

Initialize:

Sub Initialize
      'Get a collection of all documents
      Dim ses As New NotesSession
      Dim db As NotesDatabase
      Dim col As NotesDocumentCollection
      
      Set db = ses.CurrentDatabase
      Set col = db.AllDocuments
      
      Dim doc As NotesDocument
      Set doc = col.GetFirstDocument
      
'Loop through all documents
      Do While Not(doc Is Nothing)
            
    'Recompute all fields and save the document
            doc.ComputeWithForm False, False
            doc.Save True, True
            Set doc= col.GetNextDocument(doc) ' <---- was missing
      Loop
End Sub

Terminate:

Sub Terminate
      
End Sub
The agent opens all documents internally, recomputes all documents according to the form that's specified and then saves the document again. You asked for all documents using a certain form, but since the agent takes db.AllDocuments to walk through, all documents in your database are refreshed. That can take a LONG time... Better use db.UnprocessedDocuments instead.

Why are you persisting in running an agent? If you don't trust me, just say so. Get rid of the agent, it will do more harm than good. To start with, all those updated documents will be replicated to all people who have a replica. For nothing. Then, all documents will reappear unread when you use unread marks in your views. And there are more reasons. Only update documents in Notes when something has really changed.
I do trust you sjef,

it's just unfortunate that if someone opens a record to view they have to open it to edit and press F9 to update it.
Even with the computed for display fields they have to click the mouse into the last cell before the record will update.

Like I say, I have seen an agent update thousands of records in just a few seconds, so I thought that if this was simple enough, this would be the best thing to do? I don't use unread marks in views (just messy in my opinion), and this database is running off a server without local replicas on client machines...
Then try option 3: rename the CfD-field, e.g. to OldFieldNameDisplay, so there's no value for that field stored. The formula should still be computed when the document is opened. When in doubt, try a new database, a new form, a new CfD-field with some formula in it (e.g. @Text(@Now)). The field will be computed even when a new document is opened (just tested).
Didn't get a real solution to this problem.

Points awarded as comments made have been useful anyway.

Thanks guys!