Solved

How to do a between in script <>??

Posted on 2003-11-13
14
214 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:adspmo
  • 6
  • 5
  • 3
14 Comments
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 100 total points
ID: 9742362
Hi adspmo,

I honstely don't know why you are doing it this way... changing the value based on the request, and changng back if request denied.  Instead, why not wait to deduct until after the request is approved?!

Anyway, here's the basic logic:

pendingCarryOver = empRecord.carryOver(0)
pendingHolidays = empRecord.holidays(0)
request.PendingCarryOver = pendingCarryOver
request.pendingHolidays = pendingHolidays
allAvailable = pendingCarryOver + pendingHolidays
requestedAmount = request.requestedAmount(0)
if requestedAmount > allAvailable Then
  'reject request as impossible?
  Exit sub
end if
remainingCarryOver = pendingCarryOver - requestedAmount
If remainingCarrOver < 0 then
  remainingHolidays = pendingHolidays + remainingCarryOver 'which is negative
  remainingCarryOver = 0
else
  remainingHolidays = pendingHolidays
end if
empRecord.carryOver = remainingCarryOver
empRecord.holidays = remainingHolidays



'to "put it back" if request is denied:
empRecord.carryOver = request.pendingCarryOver
empRecord.holidays = request.pendingHolidays

Best regards,
qwaletee
0
 

Author Comment

by:adspmo
ID: 9742381
The intent is the that request can be cancelled after it is approved
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 250 total points
ID: 9742439
I believe I have already mentioned on how to accomodate the carriedover but here you are trying to release it back.. Right !

Here is something that you might want to take a look

If note.Status(0) = "Approved" and note.NewStatus(0) = "Cancel" then

if month(note.StartDate(0)) < 6 then
  doc.CarriedOverAH(0) = doc.TotalAH(0) - Here substitute the field which has total vacation reserved for that year

if doc.TotalDaysOff(0) < doc.CarriedOverAH(0) then
  doc.CarriedOverAH(0) = doc.CarriedOverAH(0) + note.TotalDaysOff(0)
else
  doc.TotalDaysOffCalc = 0
  doc.RemainingCalc = doc.TotalAH(0) + note.TotalDaysOff(0)
end if
 
end if


End if   ' satus recall


~Hemanth
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9742485
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.
0
 

Author Comment

by:adspmo
ID: 9742592
The requirement is quite rigid

The carried over have to be used by the end of June and this only applies if Type = "Vac"

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9742719
Change this

if month(note.StartDate(0)) < 6

to

if month(note.StartDate(0)) < 6 and note.type(0) = "Vac" then
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9742933
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

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:adspmo
ID: 9743028
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)
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9743138
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.

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9743254
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.
0
 

Author Comment

by:adspmo
ID: 9747600
Thats why I am here. I am trying to accomplish both goals.


0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9748063
So james, Where are we now ? Is it working or any problems ?
0
 

Author Comment

by:adspmo
ID: 9748702
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9749523
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.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a bat file fo Note.ini 4 199
Validating Email Address 13 283
Email Forward: Get Around Relay Issue 3 109
Outlook Contact Export as .msg trying to import in Lotus Notes 5 98
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now