adspmo
asked on
How to do a between in script <>??
Hi All
I have the requirement of Using the CarriedOver days before July, the carried over value is between 1 and 5. If the person is requesting time off prior to June and they are requesting more days than is in the CarriedOver then the balance of the days would come from there allowd Holidays
If Carried over is 0 then it comes right off the holidays
E.g.
I have 15 days holiday and 4 carried over this =19
It is prior to June and I take 7 days holiday
The carriedover field loses it 4 and the Total field loses 7
Now here is where it gets tricky if the Approved vacation is cancelled I have to repoulate the fields to the value prior to approval
Carried over gets back 4 and total gets seven
This so the managers can track the carried over days and make sure they are used first.
Here is what I have so far
If doc.CarriedOverAH(0)= note.NewDaysTotal(0) Then
doc.CarriedOverAH = doc.CarriedOverAH(0) - note.NewDaysTotal(0)
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
The If statment should read something like
If doc.CarriedOverAH(0)is between 1 and 5 and If note.type(0) = "Vac" And Month(note.StartDate(0)) < 6 Then
doc.CarriedOverAH = doc.CarriedOverAH(0) -between 1 and 5
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
Regards
James
I have the requirement of Using the CarriedOver days before July, the carried over value is between 1 and 5. If the person is requesting time off prior to June and they are requesting more days than is in the CarriedOver then the balance of the days would come from there allowd Holidays
If Carried over is 0 then it comes right off the holidays
E.g.
I have 15 days holiday and 4 carried over this =19
It is prior to June and I take 7 days holiday
The carriedover field loses it 4 and the Total field loses 7
Now here is where it gets tricky if the Approved vacation is cancelled I have to repoulate the fields to the value prior to approval
Carried over gets back 4 and total gets seven
This so the managers can track the carried over days and make sure they are used first.
Here is what I have so far
If doc.CarriedOverAH(0)= note.NewDaysTotal(0) Then
doc.CarriedOverAH = doc.CarriedOverAH(0) - note.NewDaysTotal(0)
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
The If statment should read something like
If doc.CarriedOverAH(0)is between 1 and 5 and If note.type(0) = "Vac" And Month(note.StartDate(0)) < 6 Then
doc.CarriedOverAH = doc.CarriedOverAH(0) -between 1 and 5
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
Regards
James
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ANother point:
The calculation code should NOT need to account for which part of teh year it is. Instead, you should have some maintenance code that on July 1 at 1:00 AM, goes out and zeroes the caryyOver values.
The calculation code should NOT need to account for which part of teh year it is. Instead, you should have some maintenance code that on July 1 at 1:00 AM, goes out and zeroes the caryyOver values.
ASKER
The requirement is quite rigid
The carried over have to be used by the end of June and this only applies if Type = "Vac"
The carried over have to be used by the end of June and this only applies if Type = "Vac"
Change this
if month(note.StartDate(0)) < 6
to
if month(note.StartDate(0)) < 6 and note.type(0) = "Vac" then
if month(note.StartDate(0)) < 6
to
if month(note.StartDate(0)) < 6 and note.type(0) = "Vac" then
If you are looking for between function ?? It is not available rather how you do it is like this
1 < doc.CarriedOverAH(0) and doc.CarriedOverAH(0) > 5
1 < doc.CarriedOverAH(0) and doc.CarriedOverAH(0) > 5
ASKER
This is with the approved but the same question applies I would like to do a between because 0 is always less than something it should read >=1 and <=5
if month(note.StartDate(0)) < 6 and note.type(0) = "Vac" then
If note.NewDaysTotal(0)>=1 and <=5 Then
doc.TotalDaysOffCalc = note.NewDaysTotal(0) - doc.HolidaysAH(0)
doc.CarriedOverAH = doc.CarriedOverAH(0) - note.NewDaysTotal(0)
If note.NewDaysTotal(0) >doc.CarriedOverAH(0) Then
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.CarriedOverAH = 0
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
if month(note.StartDate(0)) < 6 and note.type(0) = "Vac" then
If note.NewDaysTotal(0)>=1 and <=5 Then
doc.TotalDaysOffCalc = note.NewDaysTotal(0) - doc.HolidaysAH(0)
doc.CarriedOverAH = doc.CarriedOverAH(0) - note.NewDaysTotal(0)
If note.NewDaysTotal(0) >doc.CarriedOverAH(0) Then
doc.TotalDaysOffCalc = note.TotalDaysOff(0)
doc.CarriedOverAH = 0
doc.RemainingCalc = doc.TotalAH(0) - doc.TotalDaysOffCalc(0)
OOps what was I thinking ?
It is
1 <= doc.CarriedOverAH(0) and doc.CarriedOverAH(0) <= 5
I don't think it is a good idea to rely on static numbers. Rather I would do a manipulation so that my carriedover is not over/under utilized.
It is
1 <= doc.CarriedOverAH(0) and doc.CarriedOverAH(0) <= 5
I don't think it is a good idea to rely on static numbers. Rather I would do a manipulation so that my carriedover is not over/under utilized.
The requirement may be rigid, but the software design to fulfill the requirement need not be. Overall, pardon me for saying so, but you have a poorly designed system in a number of ways, and this insistence on coding it as a "between" instead of an easily-understood logic sequence only demonstrates it further.
ASKER
Thats why I am here. I am trying to accomplish both goals.
So james, Where are we now ? Is it working or any problems ?
ASKER
I am trying to come at it from a different way. I have been asked to break it down it to multiple fields
e.g.
Alloted Used Remaining
This will actually make it easier to restore values if an approved leave is cancelled
Right now I am rewriting the script to accomplish this
I am sure I will be in touch later
Thanx
James
e.g.
Alloted Used Remaining
This will actually make it easier to restore values if an approved leave is cancelled
Right now I am rewriting the script to accomplish this
I am sure I will be in touch later
Thanx
James
Let me suggest you something here.
Try to set raw fields/variables and not compute anything in the approval end. ComputeWithForm should do rest of its work like calculation in the leave form.
Let me explain it with eg;
Say Leave form has
Raw fields
========
TotalLeaveForYr -- 15
LeaveApproved -- varies and set from Approval form
LeaveDenied --varies and set from Approval form
UseCarried -- Flag to whether use carried or not 1-Yes, 0-No, set from Approval form.
This will be computed fields
===================
TotalLeaveTaken = TotalLeaveTaken + LeaveApproved - LeaveDenied
Carried = Default from prev yr and the calculated from this @If(UseCarried; @If(Carried >= LeaveApproved; Carried - LeaveApproved; 0); 0);
Remaining = TotalLeaveForYr + Carried - TotalLeaveTaken
This is what I was suggesting when you started of with this application. That way I don't have to maintain my complex calc in approval form.. But it always depends on you as you are the one who facing the customer.
Try to set raw fields/variables and not compute anything in the approval end. ComputeWithForm should do rest of its work like calculation in the leave form.
Let me explain it with eg;
Say Leave form has
Raw fields
========
TotalLeaveForYr -- 15
LeaveApproved -- varies and set from Approval form
LeaveDenied --varies and set from Approval form
UseCarried -- Flag to whether use carried or not 1-Yes, 0-No, set from Approval form.
This will be computed fields
===================
TotalLeaveTaken = TotalLeaveTaken + LeaveApproved - LeaveDenied
Carried = Default from prev yr and the calculated from this @If(UseCarried; @If(Carried >= LeaveApproved; Carried - LeaveApproved; 0); 0);
Remaining = TotalLeaveForYr + Carried - TotalLeaveTaken
This is what I was suggesting when you started of with this application. That way I don't have to maintain my complex calc in approval form.. But it always depends on you as you are the one who facing the customer.
ASKER