Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Lotus Notes want to capture date/time FIRST time a field changes status

Posted on 2016-08-31
Medium Priority
Last Modified: 2016-09-06
I have reviewed posts and can't find exactly what I am looking for.

In a form, I have a field called Status. It has values
Not Started

An end user wants me to capture the 1st date the status is Working, Held, etc.  (any status other than "Not Started"). These fields have to be separate fields so I can include in views for her.

I set up 4 hidden date fields - Capture_Working, Capture_Held, etc.

For each one I used this formula language for the field value (this example is for Working status)
@If(@IsNewDoc; "";
@If(@IsDocBeingSaved & Status = "Working" & Capture_Working = "";@Now;Capture_Working))

Held would be Capture_Held:
@If(@IsNewDoc; "";
@If(@IsDocBeingSaved & Status = "Held" & Capture_Held = "";@Now;Capture_Held))

So I thought a new form would have blanks for all of these date fields. It did.

I then changed the status to "Working" and sure enough, it captured the working date/time. BUT it also populated the other capture dates. I don't see how it did this?

A few minutes later I re-saved the document to be sure the Capture_Working field did not change; it did not.

Can you help me figure out why the other capture date fields are changing? I don't want them to until the status actually changes to the status they are referring to.
Question by:davidweekleyhomes
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 14

Expert Comment

ID: 41778230
During development, sometimes it helps to put @MessageBox("About to do such-and-such") entries in your code to see what fires when.

Put those in each computed field to validate when the code runs. If it runs at unexpected times, then include variables in your msgbox's to find out why, like this:

MsgBox("Status = " & status)

Author Comment

ID: 41778255
Well, my lines of code are formula language and your @MessageBox appears to be Lotusscript. I am not sure how to mix these?  Thanks!
LVL 46

Expert Comment

by:Sjef Bosman
ID: 41778293
How do you do your status changes? I hope that they are done using buttons, and that Status is never changed manually. In that case you can use a formula in a status-changing button to set the appropriate field. Try to avoid calculating the status while saving the document. Make them push buttons, that change the status and then save the document.

IMHO it is not a very good idea to keep these events in the document itself. I understand why you do it, but I learned to use other means. As I said, they are events, and events should be logged, e.g. in an event log document. Usually, I have a separate Log document for every change made to a document (but you put all log information in one separate Log document for this master document). With separate documents, it's terribly easy to fetch logs per document, and in every detail you need. Even if you need only the latest logged date values for a document, you can build a view of Log documents, ordered by decreasing log date, and select the state-related log documents.

PS You might share some of your code...
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 14

Expert Comment

ID: 41778483
Sorry about the mixup - you can use @Prompt commands in @Formulas just like you can use MsgBox in LotusScript.

Author Comment

ID: 41778547
Well, the status field is a computed Text field populated by a drop down list of choices.   I really want these first-time-captured date fields in the document since they will be added to multiple views;  I don't think a view of log documents would be helpful for the end user.
There is not much code to post. The formula language above is the only code related to the capture date fields.
I am trying to replace the code with Lotusscript but am not having too much success.  I put the following in the postopen event. Status_1 is a text field equal to Status.
Sub Postopen(Source As Notesuidocument)
      Dim PrevStatus As String
      PrevStatus =  Source.FieldGetText( "Status_1")
End Sub
When I debug, I see that the PrevStatus field gets set right.  I had read that I needed to compare the fields in the QuerySave event but when I get to that, the PrevStatus value is no longer set.
This is the code I had put in QuerySave
      If PrevStatus <> "Working" And Status = "Working" Then
            Capture_Working_1 = Now
      End If
But the if condition falls thru since PrevStatus is not set.  I don't think my field setting is 100% right but since I don't have a previous field for comparison, it is a moot point.
LVL 46

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 41778990
Put the declaration of PrevStatus at the Global level, in the form. I'd advise you as well to add Option Declare (or Option Explicit) to all your code. When that option is set, you are forced to declare every variable explicitly. You would have seen immediately that the variable used in your QuerySave function isn't declared, and hence is different from the one declared and used in your current PostOpen function.

I'd still like to advise you to put all status changes behind buttons. If you have a proper State Transition Diagram, you can easily develop these buttons. The biggest advantage: illegal state transitions cannot happen.

Author Closing Comment

ID: 41786031

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

610 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