I’m trying to run an agent using the following document selection criteria:
SELECT Form = “New Surplus Equipment” & Ehdate < @Adjust(@Today;0;0;-90;0;0;0)
I need to have the agent to select those documents that have a date in the EhDate field that is 90 days or older than today’s date.
The problem is that this formula is returning all the documents, i.e., those with a date greater than and less than today’s date.
Can anyone tell me what I’m doing wrong?
Subject: Document Selection By Date
Never Ever use @Today or @Now in a view selection formula, it will kill performance becasue the view will always be seen as needing to be refreshed. There are some ways around this and one I use is to run a daily agent that sets a Flag in the document if the Date is >= than Today and <= Today + X. Then have your agent refresh the view index in you script.There are some problems with this though - it does not account for time zones (may or may not be an issue) and depending on the nature and size of the DB may increase replication time. However, the plus is that it will not degrade performance.
There have been many threads in this forum on view optimization and the biggest single killer is @Today and @Now.
Search here and you will find some other possible solutions to your problem. Mine is not the only one.
Subject: RE: Document Selection By Date
Thanks Bill. I will try what you suggested. Just an FYI. I was not using this for view selection, rather I was using this as document selection to run a reminder agent. So all documents that had not been modified for 90 days have an e-mail reminder sent to the docowner.
Subject: Document Selection By Date
Wadi,
Aside from the performance impact (see many other posts and articles on view performance), is EhDate date/time or just text? And is it multi-value or single value?
Just a couple thoughts …
regards,
raphael
Subject: RE: Document Selection By Date
It’s a computed date/time field and just a single value.