Solved

Assistance needed with formula for a Lotus Notes view

Posted on 2002-07-15
23
205 Views
Last Modified: 2013-12-18
Hello,

I am working on a view in Notes [4.6a] and am having trouble with a formula. The formula I have now is:

SELECT Form="Sample Login"&deliver!="China"&(DateReceived>=[07/01/2002])

Which works well, but the manager wants a little more added to it...

Specifically, I need to NOT display any records that have "completed" or "released" in the Status field - but after 2 weeks from todays date...

So if today is 7-1-02, then on 7-14-02 I would expect all records that have 'Released' or 'Completed' NOT to be displayed.

I think the pseudo-code would something like:
IF status ="Released" or "Completed" AND (CompletionDate + 14 days) <= Today THEN display information
ELSE IF status="Released" or "Completed" AND (CompletionDate + 14 days) > Today THEN do not display information.

The only advice I've received thus far says I will need an agent for the date calculations, which I hope isn't true since I have no experience at all with agents.

If anyone can provide any insight into this I would be VERY grateful!

Thanks in advance!!
-jim
0
Comment
Question by:yim11
  • 9
  • 4
  • 4
  • +3
23 Comments
 
LVL 13

Expert Comment

by:CRAK
ID: 7155562
I can't really test it myself right now, but give this one a try. Basically it's the same as your pseudo code with only a few adjustments:
CompletionDate + 14 days can be calculated using @Adjust
You were supposed to show "Realeased" or "Completed" only if.... I assumed that meant that you can also show the document if the status is different than either one of those values, regardless of the date.


Select
(
  Form="Sample Login"
& deliver!="China"
& DateReceived>=[07/01/2002]
)
& (
    status="Released":"Completed"
  & @Adjust(CompletionDate;0;0;14;0;0;0)<=@Today
  )
| (
    !status="Released":"Completed"
  )
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 7155576
You may not need an agent to do this, but it will slow down the view considerably since you will be depending on the Today function for the calculations of your view.

You need a formula.


IF ((status ="Released" )or (status="Completed)and
(@Adjust(@today;0;0;14;0;0;0)> CompletionDate)

On the other hand, because this is in a select statement, you may indeed need an agent to do this in the end.

I hope this helps !
0
 
LVL 13

Expert Comment

by:CRAK
ID: 7155580
About that agent:
You don't have to, but it's better (faster) if you did.

By using data/time values in your selection, the selection formula virtually changes every msec. This results in the blue refresh arrow in the tip left corner of the view.

An agent could evaluate each document (or specific subsets if you put more effort in) and either set or reset a flag (an additional field, with a value either 0 or 1). You could run that agent every night (after 00:00 am) and select all documents in the view with that flag (field) set to 1.

You could either write that agent in lotusscript or normal code. The choise is yours!
Keep it in mind for a future challenge....
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7155825
Helo jim,

how about this formula:
SELECT
(Form="Sample Login") &
(deliver!="China") &
(DateReceived>=[07.01.2002]) &
(((status="Released") | (status="Completed")) &
(@Adjust(CompletionDate;0;0;14;0;0;0) < @TextToTime("Today")))

It does not have the "blue refresh arrow" :)

Regards,
zvonko



0
 
LVL 10

Accepted Solution

by:
zvonko earned 500 total points
ID: 7155828
I forgot the others...

Here the complete formula:
SELECT
(Form="Sample Login") &
(deliver!="China") &
(DateReceived>=[07.01.2002]) &
(((status="Released") | (status="Completed")) &
(@Adjust(CompletionDate;0;0;14;0;0;0) < @TextToTime("Today")) |
((status!="Released") & (status!="Completed")))


0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7156587
Listening...
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7156740
Hi JM.

It's boring without jack-of-all-trades-Arun :(

The boys here are all sooooo polite :)

0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7156791
Hello, I was interested in this question too, because I've done something similar in a few applications of mine.
I was wondering why the @TextToTime function is different from regular @Today.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7156839
It has not to be recalculated all the TIME :)

Only on request when the view is opened (according to view index refresh rules).

0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7157048
This certainly opens perspectives for some dynamic views I need. Thanks for the tip!
0
 
LVL 5

Expert Comment

by:snocross
ID: 7157187
I'm here to take over for Arun and provide witty comment.  Man, I'm late, I miss all the easy ones!!!
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 10

Expert Comment

by:zvonko
ID: 7157288
...finally :)

I saw this trick on KeySolutions site but never understood why they did it so complicated in two lines:
http://www.keysolutions.com/NotesFAQ/canstop.html

0
 

Author Comment

by:yim11
ID: 7157451
Works like a champ!!!

*THANKS*

-jim
0
 

Author Comment

by:yim11
ID: 7157457
Works like a champ!!!

*THANKS*

-jim
0
 
LVL 5

Expert Comment

by:snocross
ID: 7157464
Wow!  Good job.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7157538
Ufff!!! :)

This one for Arun!

Did you see? He is back :) That did not last long...

Uhhps, Jim, thank you very much for the points.

I hope next time to be again of help.

So long,
zvonko

0
 
LVL 13

Expert Comment

by:CRAK
ID: 7157757
Jim... whap happened?! You just wasted an excellent opportunity to make my day!  ;-(

Well, never mind! I've learned something again!

Thanks zvonko, I didn't know about that time thingie.... (I wasn't the only one too!)
Mind if I adopt it in the future?

Congrats!
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7157989
Hey Crak, this is Experts Exchange!
Thanks, and sorry for stilling your points :)

Really, you can see from my postings that I looked from your formula what I missed in mine. At least I was ashamed to steal your list compare functionality that most people miss to use (me too :)

Good luck next time!
zvonko

0
 

Author Comment

by:yim11
ID: 7158078
Zvonko,

Its not working as expected, when the status is changed to "Complete" it removes the entry from the view immediqately instead of waiting the 14 before removing the entry from the view... was wondering if you had any thoughts?

Crak,

Since the solution is based on your original post, if you want to reply to the 500 pt request in the other forum I posted this question to I can give you 500 also that way <?>


THANKS!!
-jim
0
 

Author Comment

by:yim11
ID: 7158118
Zvonko,

Its not working as expected, when the status is changed to "Complete" it removes the entry from the view immediqately instead of waiting the 14 before removing the entry from the view... was wondering if you had any thoughts?

Crak,

Since the solution is based on your original post, if you want to reply to the 500 pt request in the other forum I posted this question to I can give you 500 also that way <?>


THANKS!!
-jim
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7158199
This was as I understood the requirement, sorry.

But if you like to see Closed and Completed only when younger then 14 days then use this version:

SELECT
(Form="Sample Login") &
(deliver!="China") &
(DateReceived>=[07.01.2002]) &
(((status="Released") | (status="Completed")) &
(@Adjust(CompletionDate;0;0;14;0;0;0) > @TextToTime("Today")) |
((status!="Released") & (status!="Completed")))

You see, only the lower-then operator changes to greater-then. That's all.

To be honest, I was wondering about the requirement, but tough for some reason you would know why you like it such strange :)

Give it a try.

So long,
zvonko

0
 
LVL 13

Expert Comment

by:CRAK
ID: 7159257
Don't worry about it Jim,  I'm only kidding!
Do delete that other question and get your points refunded. Who knows you might need them some day.....
Besides, its not even working properly: it got the "<" and ">" mixed up!

Thanks for reminding me, EXPERT zvonko!  ;-))
You know we'll get even.... some day..... (well perhaps not that even!).
Enjoy the list compare.... you're free to use or quote it!
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7159722
Thanks crack CRAK :)
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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

911 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

23 Experts available now in Live!

Get 1:1 Help Now