• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

View Formula for Contract Expiry

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
Faraj1969
Asked:
Faraj1969
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
p_parthaCommented:
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
 
qwaleteeCommented:
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
 
qwaleteeCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Faraj1969Author Commented:

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
 
Bozzie4Commented:
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
 
Faraj1969Author Commented:
unfortunately we still use R5, since we have many applications and we need to revise them for compatibility before moving to R6.

Cheers
0
 
Faraj1969Author Commented:

I believe my question now worth more.. so I increased the points... lol
0
 
Bozzie4Commented:
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
 
Faraj1969Author Commented:
Many thanks everyone, I'll split the points.

Cheers.
0
 
Sjef BosmanGroupware ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now