asked on

Refresh computed field on form via scheduled agent ?

Hi all... first post here. I've been looking for an answer in the archives and can't find one so I'll post a new (old) question / problem.

I have a form which contains Employee information such as name, phone extension, facility they work at, etc...

On the form a have a computed field which fetches the most recent info from our HR system data... when people change departments, locations etc. ... I need this database to be refreshed daily. I know I can do it very easily with a MANUAL agent ie @Command([ToolsRefreshAllDocs]) which works great.... I just can't believe that I can't refresh a computed field via a scheduled agent.

Here's the computed field code... Uses the employee number as a key field for lookups to fetch other data:

key := EmpNum;
@If(key = ""; @Return(""); "");

REM {Get the entire string of emp data};
vLookup := @DbLookup("" : "NoCache" ; Server2 : "empdata2.nsf"; "EmpDirLkp" ; key; 2);

@If(@IsError(vLookup);@Return("");"");

@SetField("dspTitle"; @Word(vLookup;"~~";2));
@SetField("Department"; @Word(vLookup;"~~";8));
@SetField("DeptNo"; @Word(vLookup;"~~";9));
@SetField("Location"; @Word(vLookup;"~~";10));
@SetField("ShortName"; @Word(vLookup;"~~";11));
@SetField("PayStat"; @Word(vLookup;"~~";3));

vLookup

========================

Thanks in advance for any leads on getting this into a scheduled agent !

Mitch

doninja

Generally server based Agents cannot do a refresh as this requires the Notes Client UI to perform all of the database fetching and command interpretation etc.

That said the server agent can go to a lwoer level, have more flexibility and is relatively easy for what you want to do here.

You can create a scheduled agent in your database that is run daily, runs against all documents in database, has document selectiuon formulae limiting it to the particular Form name.
In the actions you can copy your formulae as above and it will then run against each of the documents in the database.

If you want to go further then look at lotusscript to manipulate the data further.

Only suggestion is that this agent should run on the server that has the empdata2.nsf database and not try to connect across the network.

MitchC

ASKER

Anyone else have anything to offer ...perhaps with sample code ?

behenderson

Sub Initialize
Dim session as new notesSession
dim db as notesDatabase
dim varx as variant
dim doc as NotesDocument
dim docNext as notesDocument
dim dcUnp as NotesView
set dcUnp = db.GetView( "NameOfViewYouRunManualAgentIn" )

set doc = dcUnp.getFirstDocument
do while not doc is nothing
set docNext = dcUnp.getNextDocument( doc )
varx = doc.ComputeWithForm( true, false )
set doc = docNext
loop

end sub

if there are typos in here fix them and run this code as a scheduled agent.

MitchC

ASKER

Thanks, but it's my understanding and having tried numerous times that computewith form does not refresh code in computed fields on a form ?

varx = doc.ComputeWithForm( true, false )

I will try this code, but all other methods that use computewithform have not worked. ..I'll let you know how this works. Thanks !

MitchC

MitchC

ASKER

Scheduled mode returned 0 documents so I ran it manually in debug mode add get "Object variable not set" on this line:

Set dcUnp = db.GetView("(Listing by Name)")

Dim session As New NotesSession
Dim db As NotesDatabase
Dim varx As Variant
Dim doc As NotesDocument
Dim docNext As NotesDocument
Dim dcUnp As NotesView
Set dcUnp = db.GetView("(Listing by Name)")

behenderson

I am sorry..I forgot to add a line above that

Set db = session.CurrentDatabase

ASKER CERTIFIED SOLUTION

behenderson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

behenderson

Thank you. I realize that my answer, while truthful and correct, was not what you wanted.

I should also include the solution of using Replica IDs in your dblookups, running the agents on a schedule locally instead of on a server, and having local replicas of the databases you are doing a lookup to. This is a very imperfect solution that depends upon good local replication, uses a local workstation to do enterprise level work, is susceptible to power outages, connectivity issues and the vagaries of local agents running reliably. There are many things to be said about it's imperfections, but we have used it in the past and may at some point use it in the future. In a perfect world we wouldn't but we have and could possibly in the future. So another solution may be to use replicaIDs in lookups and to have replicas. Sadly there is not another solution which handles that elegantly, the limitation of not accessing other servers with background agents is a huge problem when working with lotus notes. Good luck.