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

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.
Who is Participating?
Sjef BosmanGroupware ConsultantCommented:
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.
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)
davidweekleyhomesAuthor Commented:
Well, my lines of code are formula language and your @MessageBox appears to be Lotusscript. I am not sure how to mix these?  Thanks!
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Sjef BosmanGroupware ConsultantCommented:
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...
Sorry about the mixup - you can use @Prompt commands in @Formulas just like you can use MsgBox in LotusScript.
davidweekleyhomesAuthor Commented:
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.
davidweekleyhomesAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.