Update Field Values with Scheduled Agent

Posted on 2011-09-08
Last Modified: 2013-12-18

I have two apps I am working with.  One is a library that contains employee profiles and is updated by admins with term dates, status, etc.  I have another database that calls some of that data into the form.  This works fine with @DbLookups on the form.  What I am looking for is a way to update the Term Date overnight.  So that if an admin edits the library with a termination date, then that night it will update that field in my current database.  

I do have a basic formula that is running:

name:=@If(CRD = "";"";@Subset(repList;9));
FIELD Adv_Status:= @If(name = "";"";rep[7]);
FIELD Adv_Term:= @If(name = "";"";rep[8]);

This is working on demand but I have a lot of documents going into the new database and I was wondering if there is a more effiecent way to do this.  Also not sure if I would need to  update/refresh the document with the agent. I am running 8.5.1 FP2

I am still learning lotusscript - but I am thinking that this would need to actually be created in script rather then formula.

It seems like a easy answer, to update the term field and refresh the doc but I am struggling with how to accomplish this.  Any help would be appreciated. Thanks
Question by:kali958
  • 2
  • 2
  • 2

Expert Comment

ID: 36506971
Probably best with LotusScript, although I'm not too sure what your code is doing as nameList doesn't seem to be used after you've got a value for it and you seem to be updateing the current document bassed on the current document. I think the below is a good start and worth understanding to get you going.

Dim Session as NotesSession
Dim Db as NotesDatabase
Dim View as NotesView
Dim Doc as NotesDocument

Set Session = New NotesSession
Set Db = Session.CurrentDatabase
Set doc = view.GetFirstDocument

Dim DbOther as NotesDatabase
Dim ViewOther as NotesView
Dim docOther as NotesDocument
Set DbOther = session.getDbByRepID( "862575D2:006578DA" ) 'Check this, can't remember if right off the top of my head
Set ViewOther = db.getView("lkupLOIAll")

Do while not doc is nothing
Set docOther = ViewOther.GetDocumentByKey(doc.CRD(0))
set doc = view.getnextdocument( doc)

Author Comment

ID: 36513180
For some reason it really does not like the line

Set DbOther = session.getDbByRepID( "862575D2:006578DA" )

It says Not Member: getDbByRepID

So then once I pull back the value from the other database do I have to manual set the field(s) that I want to update with any changes or would this do that?

Accepted Solution

Sam654 earned 250 total points
ID: 36515439
LOL, yep, didn't think it would like that line. Read the comment I put after it!
Got access to a Notes Client now, so am able to look this one up.
It should be
Call db.OpenByReplicaID( "Moscow", "862575D2006578DA" )

Okay, so now you have the DOCUMENT from the other Database. You need to get the value from a field, so...
myVal = docOther.FIELDNAME(0)
or set a value in a field...
if you're setting a value then you'll need to save the document afterwards.
You're probably best off going into Notes Designer Help and looking up NotesDocument. It will have a 'save' method and then maybe looking at NotesItem for playing with fields that you want to get values from or set values for.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 11

Assisted Solution

larsberntrop earned 250 total points
ID: 36521494
Ahem. Sorry, I must intervene. What a hideous mess.

Please do not supply such bad examples of programming to people learning Lotusscript!
Ben Poole: Remember: long after “quick” has gone, “dirty” remains


For future maintainability, do not store the database details like server name, filepath or ReplicaID in the code.  It's better to create a config doc, makes testing easier too.  If there already is one in the db, you can just add a spec for the source db in that form.
Then get the db spec from the config doc, and open it.

Next issue: error handling and logging.
Per your description you are working with employee data, and thus should your agent be built with proper error handling and logging.  Yes, this adds overhead, both on development and runtime. But it also save you egg on your face a few months hence when you have long forgotten about this agent and need to troubleshoot stuff, such as why terminations are not processed and finance is paying terminated people.  If you get notified by your code that something is wrong (like someone edited the config or altered access to the original database ) you can step in and rectify before it becomes an organisational problem.

So: either build your own logging framework or use OpenNTF OpenLog, by the most excellent Julian Robichaux. This will enable you to simoply add logging and error handling to all your code.  Also let's you set up event based error reporting.  Just download it and read the documentation.  

Sidenote:  OpenNTF is a goldmine of useful stuff!!  If you need solutions, or building blocks, try researching this site and see if you can reuse some work already done..

These kind of agents can lead to serious user inconvenience if not designed carefully.  Just always updating every document from its source (or worse, removing all documents and recreating them) leads to a lot of view index updates and possibly replication effort.

A good rule of thumb is to try and only read an item from a document or view once, and cache the item in a very handy Lotusscript construct, the list. If you have the needed items displayed in a view, all the better, use NotesViewEntryCollection and its siblings to process it, much faster than accessing the individual documents.  Note that the developer documentation warns against keeping a lot of references to Lotus product objects (objects of type NotesView,  NotesDocument etc.).  These will cause memory problems.  Lists of user defined classes comprised of non-product types or classes can grow very large without problems!  So for instance:
Public Class employeeProfile
'this class examines a specified doc and initializes itself from it
   Dim valid As Boolean
   Dim empno As String
   Dim termDate As Variant
   Public Sub InitFromDoc(doc as NotesDocument) ' note: keeping a reference to this document using a NotesDocument variable would be a Bad Practice!
      ' Test for a proper employee profile document
      'If not exit, leaving valid false
      valid = False

      If doc is Nothing Then Exit Sub
      If Cstr(doc.GetitemValue("Form")(0)) <> "EmployeeProfile" Then Exit Sub

      empno = Trim(Cstr(doc.GetItemValue("empno")(0))) ' if empno does not exist, GetItemValue(itname)(0) will return an empty string
      If empno = "" then Exit Sub
      ' Cstr(..) will make sure we get a string even if a number or date is in the field
      Dim v as Variant
      v = doc.GetItemvalue("termDate")(0)
      If Isdate(v) Then ' leave termdate EMPTY (EMPTY is a special value for non initialized Variant variables, testable with IsEmpty
         termDate = v
      End If
      valid = True
   End Sub
   Public Sub Init(strEmployeeNumber$, vTerminationDate as Variant)
      'If not exit, leaving valid false
      valid = False
      If strEmployeeNumber = "" then Exit Sub

      empno = strEmployeeNumber
      If Isdate(vTerminationDate) Then ' leave termdate EMPTY (EMPTY is a special value for non initialized Variant variables, testable with IsEmpty
         termDate = vTerminationDate
      End If
      valid = True
   End Sub
   Public Function isValid As Boolean
      isValid = valid
   End Function
End Class

Open in new window

then make list:
Dim empProfiles List as employeeProfile

test for existence of listitem:
IsElement(listname(indexvalue)) indexvalues are strings

enough pointers?

Author Comment

ID: 36522788
@Sam654, thanks for the help. I am going to research this further. I think I am close and you did help point me in the right direction.

@larsberntrop - I get it, there is a way to lotusscript and have it be correct. I appreciate you pointing this out.  Also, I am fully aware of OpenNTF, I am a member.  I have also been able to actually attend some LUG events that Mr. Robichaux was a speaker at and he is outstanding.

I am still in the process of learning all that lotusscript can do and appreciate your sharing of knowledge.  It is way over my head but I will take the time to review this further.

I believe that both answers are acceptable and will need to work with this further but for now, I will accept both questions.
LVL 11

Expert Comment

ID: 36523025
for more pointers about objects and lists, see Bill Buchan:

very informative!

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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