Solved

View Selection in Lotus Notes

Posted on 2011-09-06
13
504 Views
Last Modified: 2013-12-18
I created a View where I would like to be able to show Turnaround time. So far this is what I have.
 
SELECT Form = "WORec" & SoftDelete != "Y" & (RptShipDate - CustomerApprovalDate) / 86400 > 2

Open in new window


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?

 
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)

Open in new window

0
Comment
Question by:coronoahcoro
  • 5
  • 5
  • 3
13 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36490809
SELECT Form = "WORec" & SoftDelete != "Y" & ((@Today - CustomerApprovalDate) / 86400 > 2 | ((RptShipDate - CustomerApprovalDate) / 86400 > 2)

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.
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36490962
If I want to use a selection list like in SQL:

City in ('Miami', 'Newton')

What is the equivalence of that in Formula language?
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36491005
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36491045
Okay, okay...

SELECT Form = "WORec" & SoftDelete != "Y" & (@If(@IsTime(RptShipDate); RptShipDate; @Today) - CustomerApprovalDate) / 86400 > 2
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36491080
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)
0
 
LVL 10

Expert Comment

by:larsberntrop
ID: 36493911
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:coronoahcoro
ID: 36495421
@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?
0
 
LVL 10

Accepted Solution

by:
larsberntrop earned 500 total points
ID: 36496139
sure, no problem.  

Better would be to create a status field, and select and modify that.  That is because the stus probably doesn't change every day, and I always aim for the least amount of updates, gives you best performance.

So your agent would Search the db for documents as needed, and only set the status field if needed.  Then view only needs to select on statusfield:
SELECT showTurnaround=1

Open in new window

lotusscript code snippet
Dim ses As New NotesSession
Dim db As NotesDatabase, dc As NotesDocumentCollection
Const stsField = "showTurnaround"
Const searchForUnset = {Form = "WORec" & SoftDelete != "Y" & @If(@IsAvailable(RptShipDate); RptShipDate; @Today) - CustomerApprovalDate) / 86400 > 2 & showTurnaround != 1}
Const searchForSet = {Form = "WORec" & SoftDelete != "Y" & @If(@IsAvailable(RptShipDate); RptShipDate; @Today) - CustomerApprovalDate) / 86400 <= 2 & showTurnaround = 1}
'set status where appropiate
Set dc = db.Search(searchForUnset, Nothing, 0)
Call dc.StampAll(stsField, 1)
'Clear status where appropiate
Set dc = db.Search(searchForSet, Nothing, 0)
Call dc.StampAll(stsField, 0)

Open in new window







0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36496541
Sneakingly introducing @IsAvailable() into the discussion... And db.Search...

By the way, isn't it possible that some documents are stamped twice? :-P
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36496633
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:

 
SELECT Form = "WORec" & SoftDelete != "Y" & (@If(@IsTime(RptShipDate); RptShipDate; todayDate) - CustomerApprovalDate) / 86400 > 2

Open in new window

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36496771
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.
0
 
LVL 10

Expert Comment

by:larsberntrop
ID: 36497556
@Stef, hey its the experts talking...
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36498283
@sjef: could you please show me how to start the code?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email Message Background Color 4 125
Lotus Notes default email application problem 6 115
Lotus Domino Server 9 4 82
Why the url within notes is no action 6 45
  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…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

16 Experts available now in Live!

Get 1:1 Help Now