Solved

View Formula for Contract Expiry

Posted on 2004-10-20
10
295 Views
Last Modified: 2013-12-18
Hi everyone,

I have 2 views, one of them should show contracts expiring next month, and the other view should show contracts expiring within 2 months. for example today is October 20, 2004. I need the first view to show contracts expiring before November 30, 2004.  Second view should show contracts expiring before December 31, 2004.

How shall I write the formula for each view ?


Many thanks in advance.

Kindest Regards
0
Comment
Question by:Faraj1969
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 14

Expert Comment

by:p_partha
ID: 12361734
select form = "<formname>" & expirydate = @Adjust(@today;0;2;0;0;0;0)

But this will make the view very slow , especially when the database size is big

Partha
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 150 total points
ID: 12361736
Assume the contract expiration date field is name ContractExpires.  The SELECT formula would be:

start := @Adjust(@Today; 0; 1; 1 - @Day(@Today); 0; 0; 0);
end := @Adjust(start; 0; 1; -1; 0; 0; 0);
ContractExpires >= start & ContractExpires < end;

start := @Adjust(@Today; 0; 2; 1 - @Day(@Today); 0; 0; 0);
end := @Adjust(start; 0; 1; -1; 0; 0; 0);
ContractExpires >= start & ContractExpires < end;

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12361763
Oh, wait, better...

dateToMatch := @Adjust(@Today; 0; 1; 1 - @Day(@Today); 0; 0; 0);
contractMonth := @Adjust(@Today; 0; 0; 1 - @Day(@Today); 0; 0; 0);
dateToMatch = contractMonth

dateToMatch := @Adjust(@Today; 0; 2; 1 - @Day(@Today); 0; 0; 0);
contractMonth := @Adjust(@Today; 0; 0; 1 - @Day(@Today); 0; 0; 0);
dateToMatch = contractMonth



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Faraj1969
ID: 12361963

Many Thanks p_partha and qwaletee, it worked very nicely.
Now, here in this company there's 2 types of contracts, Appointment and Assignment, each employee have both contracts, Appointment with HQ and Assignment with Branch. Expiry date field for appointment is dP1_Expire, and
Expiry date field for assignment is dP2_Expire.

The formula I have now is like this

start := @Adjust(@Today; 0; 2; 1 - @Day(@Today); 0; 0; 0);
end := @Adjust(start; 0; 1; -1; 0; 0; 0);
SELECT ( Form = "Staff Record" & tStatus = "On Board" & dP1_Expire >= start & dP1_Expire <= end )

How can I revise SELECT statement to show the Staff if the appointment or the assignment gonna expire within the same period ?

Cheers

0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 12362095
Is this R6 or R5 ?  Because in R6, it's better to use the new 'editable' ViewSelection  (use an agent to set the view selection), to avoid using @today in a view selection.  This would improve performance somewhat, because @today prevents efficient view-indexing (the view index will always be recalculated)

cheers,

Tom
0
 

Author Comment

by:Faraj1969
ID: 12362116
unfortunately we still use R5, since we have many applications and we need to revise them for compatibility before moving to R6.

Cheers
0
 

Author Comment

by:Faraj1969
ID: 12362153

I believe my question now worth more.. so I increased the points... lol
0
 
LVL 15

Accepted Solution

by:
Bozzie4 earned 100 total points
ID: 12362198
start := @Adjust(@Today; 0; 2; 1 - @Day(@Today); 0; 0; 0);
end := @Adjust(start; 0; 1; -1; 0; 0; 0);
SELECT ( Form = "Staff Record" & tStatus = "On Board" & ((dP1_Expire >= start & dP1_Expire <= end ) | (dP2_Expire >= Start & dP2_Expire <= End))

cheers,

tom
0
 

Author Comment

by:Faraj1969
ID: 12362273
Many thanks everyone, I'll split the points.

Cheers.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12367542
Just a suggestion: combine those views, and make the documents categorized by the number of months before expiration. Adding a view for 3 months would be simply reduced to adapting the select-statement.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

729 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