Checking Existing Time/Date

[Charles Hoens]

I have a user who I have already created a table and input browse where he enters employee name, then the date they started work, then the time, then the date they stopped and the time. Based on this A5 does the calculations of total time.

But he recently showed me where he had so many time slips coming in, that at times, one might be doubled. Or perhaps, an employee might submit an old timeslip with different times worked. What he wants is a way to check this input and if it looks like a duplicate, pop-up a message advising him and then allowing him to force the entry through if he chooses to do so. The complexity is the time hours worked.

Let's say that EmployeeA submits a timeslip for X date saying he worked from 06:00 to 15:00. Later he submits a timeslip saying he worked from 09:00 to 19:00 on the same day. How would I find this?

I tried to do this by using the OnChange event in the input browse, but where I got hung-up was when I am entering values and at the same time checking them against the same table. I attempted to solve this issue by using a function and sending the values to that, but it's still not working.

Thanks for any ideas.

[martinwcole]

Re: Checking Existing Time/Date

well ..... most likely an employe has a unique identifying number, like employee_num. this makes it easy to determine if the employess already has a record for that day, using either dbcount() or tablecount().

Now the employee may be working at different locations, like job 1 and job 2, which is a bit more complicated

But if he is only working at one location, and there are two timeslips, as described, with apparently fradulent or at best conflicting entries, I would then reject both entries, and point out the discrepency to the employee, and require same to resubmit a single time slip.

With the entries you described, if you accept only one, the employess is robbed of time; if you accept both the company is robbed.

[Ray in Capetown]

Re: Checking Existing Time/Date

Charles I set up a table of bookings from A..to B dates. With most problems the solution occurs as simple until you try. This was not too difficult.
Created a filter algorithm line to check for any date conflict, and filter the existing booking/s on where there is conflict with any proposed period for the person.
My example is a demonstration of the principle for date/time. If this is what you are after.
Using a browse in IW

tb=table.current()
per="KIOPL.CO"
s1="20/08/2013"
s2="21/08/2013"
filt="cust="+quote(per)+" .and. ((adate<={"+s1+"} .and. bdate>={"+s1+"}) .or.  (adate<={"+s2+"} .and. bdate>={"+s2+"}))"
reslt=tb.query_create("",filt)
if reslt.records_get() >0 ' there is a conflicting existing record
     DefaultBrowse_booking.Resynch()
end if

The table fields are naturally Cust, Adate and Bdate

[G Gabriel]

Re: Checking Existing Time/Date

Is it possible someone went to work, left for some reason, and came back the same day?
Is it possible that someone worked > 1 shift?
What are the typical work hours? 9-5? or around the clock?

[martinwcole]

Re: Checking Existing Time/Date

It is not reasonable that he worked from 06.00 to 15.00 AND 9.00 through 17.00

[Charles Hoens]

Re: Checking Existing Time/Date

Thanks for the suggestions and ideas. I guess what I am looking for is how to lookup something which is being input within the table you are inputting to. I was thinking something along the lines of using the exist function within the field rules along with the between function.

As for how this could happen, I thought at first that it was kind of impossible along the lines of what Martin was saying. However, in this company, they have hundreds of employees scattered around the State of NJ, and some people, quite frankly forget that they already sent in a timesheet for certain times. I've even seen ones where the employee changes the original times that they were already paid for. That's why they asked me to come up with some kind of solution, so that when the employees fax in their timesheets, the person doing the input would be helped in finding a possible duplicate entry.

It's just one of those cases where, just when you thought you saw everything, something else pops up to test your abilities. My fallback position is to flag anything that has the same date during input.

I'd be interested in any other ideas or approaches.

Thanks!

[G Gabriel]

Re: Checking Existing Time/Date

I understand it is unreasonable to work these hours, but that was just one example. My questions were general ones to determine the simplest approach.
If the work hours are day-time only, not around the clock, and
If there is only one shift per day, and
If an employee does not leave and return
Then:
The simplest approach is to match the employee with the date. Since only one shift could be worked per day, no need to bother with matching overlapping times.
If otherwise, then you treat it like a calender to check for overlapping time and date.
My questions were simple and general and yet to be answered.

[Ted Giles]

Re: Checking Existing Time/Date

Simplest is the Validation rule that EmployeeID and Date must Not Exist in a Table. Unless they work overnight as G has questioned and try to put it on one timesheet.
This will force the input person to suspend processing. You might want to add a field showing acceptance or in dispute somewhere to list all disputed items.
If the Validation fails, the inputter can find and change the original if approprite.

[GGandhi]

Re: Checking Existing Time/Date

I know this is desktop side.
I have implemented this on my web where the sign in is by the employees. when they select a date and their name, as ted says, I look into the table and display what times they already signed in for and if not I say "you are not signed in". then they can proceed to sign in. reading the first post it appears that the time slips are given by the employees and the employer enters the data directly into a browse. in my approach the time slips are entered and added to the employee work table by the employees, since they already know what times they are signed in, this will prevent repeat entry. and you can add a comment field to add any comments by them why certain slips are different or duplicated.
then before the payroll on Monday I review a print out of the time grouped by employees and date. if there is any discrepancy I contact the employee and delete the appropriate record.

[Ray in Capetown]

Re: Checking Existing Time/Date

Thanks for the suggestions and ideas. I guess what I am looking for is how to lookup something which is being input within the table you are inputting to. I was thinking something along the lines of using the exist function within the field rules along with the between function.

I'd be interested in any other ideas or approaches.

Thanks!

Charles hello!
That is precisely what that code in post 3 will do.
When validating the input time range that code can open a second instance of the table you are using to look up - simply substitute tb=table.current() with table.open(...
reslt.records_get() >0 ' tells you immediately that there is and number of conflicting existing overlappaing record/s for that employee for that time range.
I find it soooo strange that you appear to choose to absolutely ignore a fully prepared solution then ask again for any other ideas.
Just ask if you want explicit alteration for your particular example - I've tested it, live, it works as you describe.

[GGandhi]

Re: Checking Existing Time/Date

I tested the same method I use in web on the desktop side using a form (not browse) for entry and it works just fine using onChange event of one of the fields ( second to enter of the two).

[martinwcole]

Re: Checking Existing Time/Date

It would be fairly simple - it would require a script at the cansave record event in the embedded browse

[john michaels]

Re: Checking Existing Time/Date

For goodness sakes.
Make the time entry into an xdialog.
Then have the checks of the data from the xdialog done against the table before anything gets saved and present whatever message needs to appear.

[Ted Giles]

Re: Checking Existing Time/Date

For goodness sakes.
Make the time entry into an xdialog.
Then have the checks of the data from the xdialog done against the table before anything gets saved and present whatever message needs to appear.

Show us how you would do that please John. Others will be interested.

[Ray in Capetown]

Re: Checking Existing Time/Date

Glad you mentioned show others Ted.
It appears that Charles is not really interested in suggestions nor of the help given or... ??
I have now used the snippet I devised (#3) in my own scheduling application so I'm ok Jack.