Annoying date problem

I have a view which displays documents.
An agent runs on a daily basis and creates documents which are displayed by this view:

Sub Initialize
      Dim ns As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      Dim newdoc As NotesDocument
      
      Set db= ns.CurrentDatabase
      Set view= db.GetView("amview2")
      'view.AutoUpdate= False
      Set doc= view.GetFirstDocument
      Do Until doc Is Nothing
            Set newdoc= New NotesDocument(db)
            Call newdoc.ReplaceItemValue("Form", "manningform")
            Call newdoc.ReplaceItemValue("Manning_Date", Today)
            Call newdoc.ReplaceItemValue("Second_Name", doc.Name)
            Call newdoc.ReplaceItemValue("Status", "")                            
            Call newdoc.Save(True, False)
            Set doc= view.GetNextDocument(doc)
      Loop
End Sub

This all works fine. My problem when the user creates a new document manually the date is out of order from the dates created by the agent. i.e. my view looks something like this:

Year          Month             Date

2006          
                September
                                      06/09/06
                                      07/09/06
                                      05/09/06
                                      06/09/06

If I open a document from 07/09/06, change the date to 06/09/06 then change it back again and save and close, this will then become a separate category from the other 07/09/06 documents. Refreshing the documents via an action has no effect.

How can I prevent this from happening please?
LVL 21
shuboarderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
You should be doing as 09/Sep/06 ...

The above approach which you took always gives problem. Since when document is saved, it takes the local machine format and 09/06/2006 may be as 9th month or 6th month. Notes cannot differentiate in this.

So, always try to get Server Date and Time. In this, you can have similar format all across.

Hope it helps..

rgds,
maddy
Sjef BosmanGroupware ConsultantCommented:
Is your third column sorted??
shuboarderAuthor Commented:
Sjef, Third column is categorised descending.

Madheeswar, I understand where you are comin from. Can you suggest how I might change this so that both the agent and user are using the same date/time format?

Thanks
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Sjef BosmanGroupware ConsultantCommented:
> Third column is categorised descending.
Then how come you have the dates not ordered in that column??

If I understand you right, the problem is only that manual dates differ from copied dates?

Or am I completely missing the point here?? :-S
shuboarderAuthor Commented:
you've pretty much got it....

When manual dates are entered the don't appear in order. It's as if the documents created by the agent are somehow using a different date system? i.e. 07/09/06 manually entered will not be categorised under the same date created by the agent...
Sjef BosmanGroupware ConsultantCommented:
What formulas do you use for those date/time fields? Are they date/time fields?

Maybe there's little you can do about this, if your users are both in Europe and in America. 7/9/2006 is 7 September over here in France (same notation!), but July 9th in the US. That's what Madheeswar pointed at, I think.
madheeswarCommented:
Sjef is correct.

I assume this is only for Notes Client. For this, always use @Created ...i.e., creation date of the document.

So, you will always be storing one format..that is Server date format. Since Created will always get date from Server..

Hope it helps.
shuboarderAuthor Commented:
The user and server are both in Europe.
The user inputs the date using the calendar time control.
Can I use @created in the agent above somehow?
madheeswarCommented:
yes..you can use created in your agent.

Try using below code...

Dim ss as new notessession
dim db as notesdatabase
dim createdoc as notesdocument, curdoc as notesdocument

set db=ss.currentdatabase
set curdoc=ss.documentcontext
set createdoc=db.createdocument
createdoc.Form="somedummyFormname"   'May not be existing in the db..

curdoc.createddatefieldname=createdoc.created

thats it...the above will always fetches Server date & Time details..

hope the above helps..

with rgds,
maddy
p_parthaCommented:
Also try using @trim in the first column, it might be a blank space

partha

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shuboarderAuthor Commented:
Partha...

I think you are getting somewhere.

@trim gave me error unexpected operator text expected...
So I used @text(@trim(manning_date)
Now I can see the reason why the dates are not categorised together.

there is 06/09/06 00:00:00 and 06/09/06
The dates with the zero time value are created by the agent. I assume @today does this.
Is there any way of getting the agent to only create the date and not the time?

Thanks.
shuboarderAuthor Commented:
Got it!

@text(@trim(@date(manning_date)))

Thanks for the tips!
shuboarderAuthor Commented:
@trim(@text(@date(manning_date)))

even. I don't think the trim is necessary, but it's not doing any harm and will prevent the same problem if somehow someone does manage to enter a space before the date.
p_parthaCommented:
Good that you found a solution

Partha
Sjef BosmanGroupware ConsultantCommented:
What may cause this is that ONE value in the database is text and all the others are date/time. I don't know what conversion Notes does in this case.

Did you try with just
    @date(manning_date)
shuboarderAuthor Commented:
Partha's suggestion of using @trim
led me to use a formula in the column:

@trim(@text(@date(manning_date)))

However, as Sjef has suggested the @trim and @text may not be necessary
Sjef BosmanGroupware ConsultantCommented:
Question solved? Assign the points! Your lead, maestro...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.