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
Solved

View Formula for Contract Expiry

Posted on 2004-10-20
10
292 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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 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