Preventing Overlapping time

[signal]

I have created a time clock/labor entry database with start and stop time and dates which works fine. Trying to figure out the easiest way to do two things:

1. Prevent the same user from creating a time charge on the same day that overlaps with another.

2. Prevent the user from creating a time charge for a period of time that they are not clocked in.

If I can't do it with a validation script (which I would prefer), I would be open to having a script that would flag the time "invalid". One stickler is that people leave for lunch so there is more than one clock in and out per day.

[Kurt Knippel]

quote:

---

Originally posted by signal:

I have created a time clock/labor entry database with start and stop time and dates which works fine. Trying to figure out the easiest way to do two things:

1. Prevent the same user from creating a time charge on the same day that overlaps with another.

2. Prevent the user from creating a time charge for a period of time that they are not clocked in.

If I can't do it with a validation script (which I would prefer), I would be open to having a script that would flag the time "invalid". One stickler is that people leave for lunch so there is more than one clock in and out per day.

---

Oceanwest did a pretty nice thing with validating time recently. E-mail him and see if he will share this.

[Ocean West]

Thanks Kurt...

Ok Here goes

Create a self relationship using a field that includes the date and the user ID

Goto related records, show only related.

go to layout with portal (set portal to sort by start time)

This portal should show only the records entered for the day.

Next create a script step that

loops through each record

setting a global start and a global end field

if the contents of the next records is less than the end time

then set a field on this record to flag an overlap.

although it doesn' t provide an exact way to prevent users

from entering overlaped data, you can in post processing

inform them there is an error on their record.

hope this helps.

[ April 04, 2001: Message edited by: Ocean West ]

[signal]

Funny that I get frustrated with people not searching the forum before posting a question and here I go doing it. The pot calling the kettle black I suppose.

Thanks for your idea. I'll try it. Any ideas on the second part of my post regarding posting time if you are clocked out?

Guess I could loop through the related records, use your method for overlap and then check if each "start time" is greater than a "clock in" time but less than a "clock out" time, flag it "OK" , then see if the end time is less than the clock out time and do the same thing.

Also, using that realationship, I could probably verify entry with an agregate calculation that the "start time be" > "clock in" and < "clock out" and > max("end time")...

I'm getting a headache.

Another unusual thing. I had checked "email notification" of response and wasn't mailed that anyone had posted anything about my topic. Wonder why?