Solved

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

Posted on 2016-08-31
7
45 Views
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
Working
Held
Approved
Complete

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.
0
Comment
Question by:davidweekleyhomes
  • 3
  • 2
  • 2
7 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
Comment Utility
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)
0
 

Author Comment

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

Expert Comment

by:Sjef Bosman
Comment Utility
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...
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Expert Comment

by:ThomasMcA2
Comment Utility
Sorry about the mixup - you can use @Prompt commands in @Formulas just like you can use MsgBox in LotusScript.
0
 

Author Comment

by:davidweekleyhomes
Comment Utility
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.
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
Comment Utility
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.
0
 

Author Closing Comment

by:davidweekleyhomes
Comment Utility
Thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

  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…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

728 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