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

Posted on 2016-08-31
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
  • 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...
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 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