everetjo
asked on
older Approval Date is overwritten with Today's date
I'm new to Lotus programming, and I need a solution ASAP.
I inherited a Notes 6.5 approval database with a problem in the approval date field. This approval database was in use for a few years before the programmer inserted a field called Approval date. The approval date reads a status field, and when status=3, the Approval date field populates with the moment that the document was approved. This works fine for new approvals.
There is a problem, however, with old approved documents.
When you open a document that is older than the day than the status field was introduced, the formula in the approval date recalculates, and the old approval date is replaced with today's date (the moment you opened it)
I need to ensure that the old approval dates remain, that approval dates are calculated only once, and that the approval date cannot be changed once the doc was approved.
Approval_date is a computed Date Time field with the following formula : @If(status=3;@Date(@Today)
I inherited a Notes 6.5 approval database with a problem in the approval date field. This approval database was in use for a few years before the programmer inserted a field called Approval date. The approval date reads a status field, and when status=3, the Approval date field populates with the moment that the document was approved. This works fine for new approvals.
There is a problem, however, with old approved documents.
When you open a document that is older than the day than the status field was introduced, the formula in the approval date recalculates, and the old approval date is replaced with today's date (the moment you opened it)
I need to ensure that the old approval dates remain, that approval dates are calculated only once, and that the approval date cannot be changed once the doc was approved.
Approval_date is a computed Date Time field with the following formula : @If(status=3;@Date(@Today)
You will need an agent that goes through all the docs once and creates the Approval_date field for any doc that does not have one. It then needs to populate that field with the data from somewhere.
You mention a status filed and it is not clear if the Approval_date filed was added or a status field was added.
Please clarify.
I hope this helps !
You mention a status filed and it is not clear if the Approval_date filed was added or a status field was added.
Please clarify.
I hope this helps !
ASKER
sjef, I tried your code, and it seems to work for new records only. Old records are still updating with today's date.
Sys, based on what I tested, it appears that there was always a status field, but the Approval_Date field was the new field (Sorry for the confusion)
This database could have been made from an example database or an 'out of the box' template of some sort. The original programmer is no longer in the picture.
What code would I use for the agent? How could I add the approval_date field for docs that dont have one? Also, I wouldn't want to update those approved documents with today's approval date (I don't want to make it appear that a document modified in 2002 was approved today)
Sys, based on what I tested, it appears that there was always a status field, but the Approval_Date field was the new field (Sorry for the confusion)
This database could have been made from an example database or an 'out of the box' template of some sort. The original programmer is no longer in the picture.
What code would I use for the agent? How could I add the approval_date field for docs that dont have one? Also, I wouldn't want to update those approved documents with today's approval date (I don't want to make it appear that a document modified in 2002 was approved today)
Well a simple button or agent can do most of the work, the important info is which date field are you going to use to populate the Approval_date that is added for docs that do not presently have it ?
I hope this helps !
I hope this helps !
If doc.HasItem("Approval_date
else
Doc.Approval_date=doc.last
End if
I hope this helps !
This assumes that you create the rest of the code to either run on selected docs or to go through all the docs in a give view.
I hope this helps !
I hope this helps !
ASKER
I would like to use the last modified date as the approval date (in documents that don't have that field)
ASKER
SHould I create a view with the " where approval date contains no data" and run the agent on that view selection or should I run the agent against all documents in the database assuming that the agent would not update approval date fields that already contain a date ?
ASKER
I still need assistance with adding in the " create approval date field" in documents that do not have one.
You can run it on everything or selected.
It will only affect docs that do NOT have the Approval_date
As always, first test on a Local replica or a COPY via Notes with a few documents
If using a replica make sure replication is disabled until testing is successful
I hope this helps !
It will only affect docs that do NOT have the Approval_date
As always, first test on a Local replica or a COPY via Notes with a few documents
If using a replica make sure replication is disabled until testing is successful
I hope this helps !
ASKER
Sys, I tried your code and I get a " Query is not understandable" message
ASKER
Will this agent run on a local copy of the database?
Sorry, was away for a while... Do you really want to update all documents? Is there any code in the database that uses the last modified date or @Modified, or is the field $Revisions used in any way? If you update one field in a document, several other fields will also be updated.
Can you give the pseudo-code description of the value you want that field to have? Something like
if status doesn't exist then
appdate:= last modified date
elseif status=3 then
...
Is that possible?
Then, you don't have to update all fields, but you have to make provisions in views that rely on or use ApprovalDate. An interesting gadget is Default: if a field doesn't exist in a document, you give a stand-in field the right value.
Suppose document A has field ApprovalDate but document B hasn't.
Then, a view column formula like
Default ApprovalDate:= @Modified;
@Year(ApprovalDate)
would correctly show the real ApprovalDate for document A, because it has that field in the document, but for document B it uses the last modified date.
Can you give the pseudo-code description of the value you want that field to have? Something like
if status doesn't exist then
appdate:= last modified date
elseif status=3 then
...
Is that possible?
Then, you don't have to update all fields, but you have to make provisions in views that rely on or use ApprovalDate. An interesting gadget is Default: if a field doesn't exist in a document, you give a stand-in field the right value.
Suppose document A has field ApprovalDate but document B hasn't.
Then, a view column formula like
Default ApprovalDate:= @Modified;
@Year(ApprovalDate)
would correctly show the real ApprovalDate for document A, because it has that field in the document, but for document B it uses the last modified date.
ASKER
As far as I can tell, there is no last modified date being used in the database, and the $Revisions field isn't being used.
Instead of Modified date, I can use Date_Created (If I need to)
I'm thinking that the Approval_date field was the new field, not the Status field; in the past, I think that people approved the document by changing the status from 1 (under review) to 3 (approved).
If I don't use this against all documents in the database, how would I make the view selection formula to select documents that don't contain the Approval_date ?
Instead of Modified date, I can use Date_Created (If I need to)
I'm thinking that the Approval_date field was the new field, not the Status field; in the past, I think that people approved the document by changing the status from 1 (under review) to 3 (approved).
If I don't use this against all documents in the database, how would I make the view selection formula to select documents that don't contain the Approval_date ?
Select Approval_date=""
It should be possible with
Select !@IsAvaliable(Approval_dat
but I've never managed to get it right with @IsAvailable...
It should be possible with
Select !@IsAvaliable(Approval_dat
but I've never managed to get it right with @IsAvailable...
ASKER
OK Sjef, I have a view that selects docs with no approval date.
Do documents track their own modified date? ( like it has it's own document unique id)
If not, how do I populate the "" approval date fields with the document's date created/ date modified?
How would I run the agent on the view selection?
Do documents track their own modified date? ( like it has it's own document unique id)
If not, how do I populate the "" approval date fields with the document's date created/ date modified?
How would I run the agent on the view selection?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
status=3; @Today;
"")
meaning: if it's a date value, leave it be, and the rest.