• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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