Solved

Refresh computed field on form via scheduled agent ?

Posted on 2011-03-24
8
1,232 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:MitchC
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:doninja
ID: 35209192
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.

0
 

Author Comment

by:MitchC
ID: 35237218
Anyone else have anything to offer ...perhaps with sample code ?
0
 
LVL 8

Expert Comment

by:behenderson
ID: 35244683
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.
0
 

Author Comment

by:MitchC
ID: 35244849
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:MitchC
ID: 35244960
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)")
0
 
LVL 8

Expert Comment

by:behenderson
ID: 35258801
I am sorry..I forgot to add a line above that


Set db = session.CurrentDatabase
0
 
LVL 8

Accepted Solution

by:
behenderson earned 500 total points
ID: 35259122
There are 3 scenarios where the compute with form will not update computed fields that I can think of off the top of my head.  The first is where the form is stored in document and therefor has no form associated with it, the second would be with version 8.52 where Lotus has listed that in it's bug fix docments
From the IBM Site.

     When using a scheduled LotusScript agent with
     "ComputeWithForm" method to recalculate
     computed fields it will not work with the 8.5.2
     version. The problem is also with the Lotus
     Notes Client 8.5.2, if you start the agent manually
     via client.
    Workaround:
    Add a print statement directly above the compute with form line:
    Print "About to do compute with form"

if you are using the 8.52 server or client add the line
Print "About to do compute with form"
directly above the compute with form statement for some reason any print or write statement fixes this issue.

The last scenario where a compute with form line may not work as expected is if there are fields that are not arrays that will become arrays when compute with form is applied and those fields are used as calculations for other fields.  When this is the case you will sometimes, but certainly not always have to address those fields in your code specifically but if that is the case I don't believe your toolsRefreshALlDocs will work either.

Compute with form calculates computed fields.  I prefer not to use it often because it is a lazy approach and is very very slow.  My co workers are beginners at lotusScript, and because that is the case we do use that method in a variety of different database agents because if we did not they would be manually running ToolsRefreshALlDocs all the time which is just as slow and just as processor intensive.

Some methods you can use to avoid having to use Coimpute with form include:

Put the logic in the view not the document when dates are involved.  Views get refreshed.

Write agents that include the logic of what you want done instead of using compute with form OR toolsRefreshAllDocs it is much cleaner faster and less processor intensive, and yes I did see at the top where you specifically said you did not like that type of solution.  Like it or not it is better.

ComputeWIthForm is the only solution that meets your needs.  That does not make it good, but it does make it the most correct answer to your question.
0
 
LVL 8

Expert Comment

by:behenderson
ID: 35260224
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now