View Selection in Lotus Notes
I created a View where I would like to be able to show Turnaround time. So far this is what I have.
That view selection works fine except I also would like to be able to include Work Orders that do not have Shipped Date (RptShipDate). I tried to use @if statement so I can specify which SELECT statement I can use but I couldn't get it to work. I was hoping that the code below would work but it doesn't. Any suggestion?
SELECT Form = "WORec" & SoftDelete != "Y" & (RptShipDate - CustomerApprovalDate) / 86400 > 2That view selection works fine except I also would like to be able to include Work Orders that do not have Shipped Date (RptShipDate). I tried to use @if statement so I can specify which SELECT statement I can use but I couldn't get it to work. I was hoping that the code below would work but it doesn't. Any suggestion?
noShipDate := SELECT Form = "WORec" & SoftDelete != "Y" & (@Today - CustomerApprovalDate) / 86400 > 2
withShipDate := SELECT Form = "WORec" & SoftDelete != "Y" & (RptShipDate - CustomerApprovalDate) / 86400 > 2
@If(@IsNull(RptShipDate); noShipDate; withShipDate)ASKER
If I want to use a selection list like in SQL:
City in ('Miami', 'Newton')
What is the equivalence of that in Formula language?
City in ('Miami', 'Newton')
What is the equivalence of that in Formula language?
ASKER
sjef, after I looked at the formula. I only want the @Today part to calculate when RptShipDate is null. The formula that you suggested doesn't work that way.
Okay, okay...
SELECT Form = "WORec" & SoftDelete != "Y" & (@If(@IsTime(RptShipDate);
SELECT Form = "WORec" & SoftDelete != "Y" & (@If(@IsTime(RptShipDate);
Two ways for the city problem:
@IsMember(city; "Miami":"Newton")
and the shorter version
city= "Miami":"Newton"
The colon ":" is the list-concatenation operator, it creates a multi-value list (which is like an array in LotusScript)
@IsMember(city; "Miami":"Newton")
and the shorter version
city= "Miami":"Newton"
The colon ":" is the list-concatenation operator, it creates a multi-value list (which is like an array in LotusScript)
Pleas AVOID the use of @Today if you want to have a fast responding database. See also: http://www.nsftools.com/tips/PerfTips.htm
@Today invalidates a view every milisecond, never mind if you are only interested in the day.
Alternatives: a status field which is set by a daily agent, which takes care to only update the necessary documents, then select on the status field in the view. Enjoy a world of difference in performance.
@Today invalidates a view every milisecond, never mind if you are only interested in the day.
Alternatives: a status field which is set by a daily agent, which takes care to only update the necessary documents, then select on the status field in the view. Enjoy a world of difference in performance.
ASKER
@labernstop: so if I understand you correctly, I could add a field in the form; lets call it 'todayDate'. Then I can write an agent that runs around midnight to select these forms and update that field?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sneakingly introducing @IsAvailable() into the discussion... And db.Search...
By the way, isn't it possible that some documents are stamped twice? :-P
By the way, isn't it possible that some documents are stamped twice? :-P
ASKER
Thanks lasberntrop for the code but I don't think that is what I would like to accomplish. My big goal is to be able to create an agent that will send out an Excel report based on a Notes view (this will be on another question). Right now I just want to be able to generate a view that does these things below:
1. Calculate the turnaround time which is the difference between the shipped date and approved date, if shipped date doesn't exist just calculate the difference between today's date and approved date. Then shows the ones that have turnaround time bigger than 14
2. Include only WORec
During this discussion, you mentioned that it is bad to include @Today in a view which is true because I tested it and it was slow.
What I am thinking now is I can use your code to write an agent that runs every night that looks for WORec that doesn't have Shipped date and added @Today to one of the field. And the view selection would be something like this:
1. Calculate the turnaround time which is the difference between the shipped date and approved date, if shipped date doesn't exist just calculate the difference between today's date and approved date. Then shows the ones that have turnaround time bigger than 14
2. Include only WORec
During this discussion, you mentioned that it is bad to include @Today in a view which is true because I tested it and it was slow.
What I am thinking now is I can use your code to write an agent that runs every night that looks for WORec that doesn't have Shipped date and added @Today to one of the field. And the view selection would be something like this:
SELECT Form = "WORec" & SoftDelete != "Y" & (@If(@IsTime(RptShipDate); RptShipDate; todayDate) - CustomerApprovalDate) / 86400 > 2
If you run the Excel creation just once every night, you don't even need the view: use db.Search instead. The formula is the same, and it has less overhead when used once a day.
@Stef, hey its the experts talking...
ASKER
@sjef: could you please show me how to start the code?
But it'll always be a slow view, because you use @Today: the view is refreshed every time it's visited, because of the "temporary" character of the formula.