Faraj1969
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
cheers,
Tom
ASKER
unfortunately we still use R5, since we have many applications and we need to revise them for compatibility before moving to R6.
Cheers
Cheers
ASKER
I believe my question now worth more.. so I increased the points... lol
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks everyone, I'll split the points.
Cheers.
Cheers.
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.
But this will make the view very slow , especially when the database size is big
Partha