how to create daily time record? 1

[Mandy_crw]

Hi everyone! I have created a form which should display two "ins"and two "outs" in one row, with one date, one "IN" and one "Out" in morning sesion, and One "IN" and one "Out" in the afternoon sesion... but what ive got is as shown... Please help me achieve it... thanks and God bless....

 

[Chris Miller]

Well, if you have just one datetime field you can't have both values in one row, how?

How's the data stored? 1:! as seen? in three fields, one datetime, one char field for IN and one char field for OUT?

Actually all you need to store are single datetimes, the rest is context. Ie morning shift time portion should be 12 at max, perhaps, night shift 1am next day at max?

Assuming all datetimes are on the same day, the simplest generalization of table rules would be only allow 4 records per day, and then they are in datetime order morning IN/OUT, and afternoon IN/OUT. All further data would be redundant, but I could understand if circumstances like checking out and then getting called back, you could even have multiple in/out per session. or the out time is canceled and only min/max per morning/afternoon are the records determining the time.

I would suggest you display it like this, making IN/OUT columns.

IN                    OUT 
09/15/2021 11:29:51   09/15/2021 11:30:28
09/15/2021 20:32:11   09/15/2021 23:15:43

Maybe add a column alternating between morning and afternoon shift and have the date separated

Date       Shift    IN         OUT 
09/15/2021   AM     11:29:51   11:30:28
09/15/2021   PM     20:32:11   23:15:43

Before I suggest code, Could you please specify how do you want to display your data instead of how it is displayed?
Could you post what is stored? It's a fair assumption 1:1 what is listed, but it's not necessarily that way, as my suggestion shows. All you need is four datetimes to generate the output, as the rest is clear from the outset of having two shifts per day. The four datetimes must be restricted, two from AM, two from PM. And likely there will also be a field for the person/employee, so you have 4 records per person, but surely more per day.

How to set the rules will depend on all that, too. So better give us a more wholesome view on what you have and want.

Chriss

 

[Mike Lewis]

Just to add what Chris has told you ...

If you have not yet designed the table that holds this data - or if it is not too late to alter that design - you have two broad options:

1. Have one record for each event, an event being either an In or an Out. The record would have a single datetime field, and a flag (a logical field) to say whether that datetime is an In or an Out. You probably wouldn't need to store an indication of the shift (morning vs afternoon), as you would simply display the events in datetime order (in other words, you work on the basis that the afternoon always comes after the morning for a given day).

2. Alternatively, you have one record for each day. The record would hold four datetime fields: for morning In, morning Out, afternoon In and Afternoon Out respectively.

I would think that option 2 would be easier to program, and especially easier for reporting. But option 1 would be more flexible. It would work just as well if, at some time in the future, the client wanted to increase (or decrease) the number of shifts per day. It would also be more efficient in situations where some days only have one shift, or no shifts at all.

Have a thinj about which of the two options you prefer. It should then be easy to decide how best to do your reporting.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Gagnon]

If an employee forgets to punch out or in for a giving day is usually an issue.
Then there is also the issue of nightshift where an employee punches in before midnight and punches out after midnight, what date do you use for the record in general.
An employee punches in and out within a span of 2 minutes, but mistake, then what do you do with the third punch, is he in or out.
These issues can all be resolved but they need to be considered.


If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Chris Miller]

Yes,

I could understand if circumstances like checking out and then getting called back, you could even have multiple in/out per session

And there are more such cases to be considered.

I know a lot of projects and companies that ask for time reporting in terms of assigning time to projects rather than just punching in and out of an attendance system. I think we shouldn't overcomplicate scenarios before Mandy tells more precise what she wants. For example, not all solutions have to be software solutions. A good attandance system would perhaps only store a punch in or out after a timeout period expires in which you could revert your decision. So data cleaning isn't necessarily a job of just a software.

Chriss

 

[Mandy_crw]

Hi Chris and Mike and everyone... i have this as my database

and this is my code...

PROCEDURE Cmddtr.click()


Select * From rek ORDER BY time INTO CURSOR dtrMAINDATA

aydi=ALLTRIM(this.Parent.text1.value)
felter = MONTH(ctod(this.Parent.text10.value))

REPORT FORM dtr FOR aydi = ALLTRIM(dtrmaindata.idnum) AND felter = MONTH(dtrmaindata.time) TO PRINTER PROMPT PREVIEW



ENDPROC

 

[Chris Miller]

I see, so this is for a report.

Then the solution may be in the report designer. Using 4 report columns. In report properties, in the page layout tab you can set 4 columns and set column print order to "left to right".
But now the detail design with is only 1/4 of the page, for the obvious reason you print 4 records horizontally and then go down one detail area height.

It's usually intended to be used for label sheets, but you can use that to organize your printing.

I don't think this is a good idea, but it would be the simplest solution without any change of data or querying multiple rows into one.

You also have to rely on 4 records to exist per day.

Chriss

 

[Mike Lewis]

Mandy,

I would do all the difficult stuff in the cursor, before you get to the report. Give me a few minutes, and I'll try to come up with a way to do that.

But first, can you guarantee:

- There will be exactly two shifts (per person) every day?

- Shifts won't overlap (in other words, you will never have an IN directly between another IN and an OUT)?

- And, in the same way, you will never have an OUT directly following another OUT (without an intervening IN)?

By the way, your Dey field appears to be redundant. You can derive the information from[tt] CDOW(Time)[/tt]. But let's not worry about that for the moment.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Something like this:

CREATE CURSOR csrShifts (IDNum C(10), ShiftStart T, ShiftEnd T, AMPM C(2), LName C(24))
SELECT Smsrec
SCAN
  SELECT csrShifts
  APPEND BLANK 
  REPLACE IDNum WITH Smsrec.IDNum, ShiftStart WITH Smsrec.Time, ;
    AMPM WITH IIF(HOURS(Smsrec.Time) < 12, "AM", "PM" ), ;
    lName WITH Smsrec.lName
    
  SELECT Smsrec
  SKIP
  
  SELECT csrShifts
  REPLACE ShiftEnd WITH Smsrec.Time
  
  SELECT Smsrec
  SKIP -1
ENDSCAN

This will create a cursor named csrShifts, which you can then use to drive your report.

Note that I wrote this very quickly and haven't tried to test it. There may well be syntax errors.

The code assumes that the input data is correct, that is, that there no overlapping shifts, each IN is directly followed by the corresponding OUT, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to follow up my previous post, Mandy. It's likely your report would have the following columns:

- IDNum, from your cursor's IDNum field

- Lname, from the Lname field

- Date, from TTOD(ShiftStart)

- Time In, from TTOC(ShiftStart)

- Time Out, from TTOC(ShiftEnd)

- Day, from CDOW(ShiftStart)

Another asumption is that no shift extends over midnight.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

let me finish it up by testing. I think it'll not need the SKIP -1, you really want to process two records per iteration.

CREATE CURSOR Smsrec (IDNum C(10), Time T, Lname C(20), Kol C(3))
INSERT INTO Smsrec VALUES ('1111111111', DTOT(DATE())+36000,'Lewis','IN')
INSERT INTO Smsrec VALUES ('1111111111', DTOT(DATE())+36900,'Lewis','OUT')
INSERT INTO Smsrec VALUES ('1111111111', DTOT(DATE())+72000,'Doschke','IN')
INSERT INTO Smsrec VALUES ('1111111111', DTOT(DATE())+72900,'Doschke','OUT')

CREATE CURSOR csrShifts (IDNum C(10), ShiftStart T, ShiftEnd T, AMPM C(2), LName C(24))

SELECT Smsrec
SCAN
  SELECT csrShifts
  APPEND BLANK 
  REPLACE IDNum WITH Smsrec.IDNum, ShiftStart WITH Smsrec.Time, ;
    AMPM WITH IIF(HOUR(Smsrec.Time) < 12, "AM", "PM" ),;
    lName WITH Smsrec.lName
    
  SELECT Smsrec
  SKIP
  
  SELECT csrShifts
  REPLACE ShiftEnd WITH Smsrec.Time
ENDSCAN

I had SQL in mind:

SELECT Idnum, MIN(halfshift.Time) as Shiftstart, ;
   MAX(halfshift.Time) as Shiftend, AMPM, ;
   MIN(halfshift.Lname) as Lname;
   FROM (Select *, IIF(HOUR(Smsrec.Time)<12,'AM','PM') as AMPM FROM Smsrec) as halfshift;
   GROUP BY Idnum, AMPM;
   INTO CURSOR Shifts

But that depends on the both times of shift start and end being either AM or PM. Whereas you only set AMPM once with the shifts start time, so AMPM of a shifts end don't matter.

Chriss

 

[Mike Lewis]

I think it'll not need the SKIP -1, you really want to process two records per iteration.

You're absolutely right, Chriss. Thanks for spotting that. Mandy, you will need to need to leave out the [tt]SKIP -1[/tt] if you use my code.

That said, I think it might crash if the input file happens to contain an odd number of records, as the SKIP might take us to EOF. I'm not sure about that. In any case, it should never happen - in theory.

The SQL solution is interesting. I tried to come up with some SQL myself, but couldn't get my head round it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Hello Chris, Mike, Mike Gagnon, and myearwood I am reading well all your suggestions... i am overwhelmed and happy by the info and answers that you have given... right now i am trying my best to undestand and apply the solutions through you help... I'll come back once i have perfectly done it.... Thank you so much you are all kind and very generous.... God bless!

 

[Chris Miller]

You could APPEND, if only ShiftStart would be named Time.

Or maybe you can educate me how the FIELDS list you can specify in APPEND FROM is not just listing target field names (also for the case to append an ARRAY) but also which source file fields to map to them.
Working with calculated fields (SET FIELDS TO ShiftStart=Time) doesn't work. In fact the Hacker guide says they haven't used Set Fields much, because its use case scope is quite limited:

https://hackfox.github.io/section4/s4g059.html

But lets take it as one other option to name ShiftStart Time:

Create Cursor Shifts (IDNum C(10), [highlight #FCE94F]Time[/highlight] T, ShiftEnd T, AMPM C(2), Lname C(24))
Append From Smsrec For recno()%2=1 && or DBF("Smsrec") when appending from the workarea Smsrec.
Set relation to recno()*2 into smsrec
Replace all ShiftEnd with smsrec.Time, AMPM WITH IIF(HOUR(Time) < 12, "AM", "PM" )

And indeed it's not necessary to create an AMPM field. A report could also print the expression IIF(HOUR(Time) < 12, "AM", "PM" ), if that would be wanted.

Chriss

 

[Mandy_crw]

Hi Chris and Mike... I tried deriving from you answer and i came up with this code..
PROCEDURE Cmddtr.click()

CREATE CURSOR csrShifts (IDNum C(10), LName C(24), ShiftStartAM T, kolinam c(3), ShiftEndAM T, koloutam c(3), ;
ShiftStartPM T, kolinpm c(3), ShiftEndPM T, koloutpm c(3), AMPM C(2))

SELECT rek

SCAN

deyti = SUBSTR(DTOC(rek.time),1,10)
timy = HOUR(rek.time)

SELECT csrShifts


APPEND BLANK

IF deyti = dtoc(rek.time)

REPLACE IDNum WITH rek.IDNum
REPLACE LNAME WITH REK.LNAME
*replace kolinam WITH rek.kol

ENDIF

IF deyti = dtoc(rek.time)
SKIP -1
replace koloutam WITH rek.kol
ENDIF

IF rek.kol = "IN" AND timy <= 12
replace ShiftStartAM WITH rek.TIME
REPLACE AMPM WITH "AM"
replace kolinam WITH rek.kol
ENDIF

IF rek.kol = "OUT" AND timy <= 12
replace ShiftEndAM WITH rek.TIME
REPLACE AMPM WITH "AM"
replace koloutam WITH rek.kol
ENDIF

IF rek.kol = "IN" AND timy => 12
SKIP - 1
replace ShiftStartPM WITH rek.TIME
REPLACE AMPM WITH "PM"
replace kolinpm WITH rek.kol
ENDIF

IF rek.kol = "OUT" AND timy => 12
SKIP - 2
replace ShiftEndPM WITH rek.time
REPLACE AMPM WITH "PM"
replace koloutpm WITH rek.kol
ENDIF


ENDSCAN

Select * From csrshifts

May database smsrec.dbf contains

and i got an output (CsrShifts) as this...

please help me align... Thanks and God bless....

 

[Chris Miller]

Well, the way you did it you skip back so often, that you only get forward 1 record each loop.

What you define as csrShifts would actually put 4 records into 1, not just 2. So within your scan loop you start with the first IN record in rek (Smsrec), then you need to SKIP 1 to the first OUT, SKIP 1 once more for the second IN and finally SKIP 1 once more to get to the second OUT rek. Then the ENDSCAN skips 1 more and you skip forward 4 records per SCAN/ENDSCAN loop iteration.

And you only add one record to crsShifts then. It will not be necessary to have the AMPM field, in fact it would only be one of them, but whn you have 4 times you have both AM and PM shift in one record, so it would need to be both AM and PM.


I gave you a totally different approach of setting up columns in a report, that would still also work. Especially in landscape printing you could get 4 records of your original data from left to right.

Okay, finally I could have got what you want from your thread title, meaning one daily record. The arguments me and others gave are that it's not so sure you'd always have 4 records, sometimes less, when the day is not past, for example, sometimes more.

But then again all our solutions also depend on 4 records per day, so it's all just idea collection, not a final solution. Given your code, I think just mending the SKIPs would let it work:

CREATE CURSOR csrShifts (IDNum C(10), LName C(24), ShiftStartAM T, kolinam c(3), ShiftEndAM T, koloutam c(3), ;
ShiftStartPM T, kolinpm c(3), ShiftEndPM T, koloutpm c(3))

SELECT rek
SCAN
   * expecting IN,OUT,IN,OUT in rek.kol
   * expecting same date in rek.time for this record and the following 3 records

   * one record in crsShifts for 4 records in rek:
   SELECT csrShifts
   APPEND BLANK  && only one APPEND per iteration

   REPLACE IDNum WITH rek.IDNum
   REPLACE LNAME WITH REK.LNAME
   REPLACE ShiftStartAM with rek.time
   REPLACE kolinam WITH rek.kol && will always be "IN", so why store it? For checking whether data is complete?
   * unused data from rek: Dey, but that simply is CDOW(rek.time) or CDOW(ShiftStartAM)

   SELECT rek
   SKIP 1
   SELECT csrShifts
   REPLACE ShiftEndAM with rek.time
   REPLACE koloutam WITH rek.kol && will always be "OUT"
   * unused data from rek: Lname (assumed to be the same for all 4 records. If not, you need LNameAM and LNamePM.
   * also unused: Idnum and Dey assumed to be same.

   SELECT rek
   SKIP 1   
   SELECT csrShifts
   replace ShiftStartPM WITH rek.time
   replace kolinpm WITH rek.kol
   * unused data from rek: Idnum,Lname,Dey

   SELECT rek
   SKIP 1   
   SELECT csrShifts
   replace ShiftStartPM WITH rek.time
   replace kolinpm WITH rek.kol

   SELECT rek
   SKIP 1   
   SELECT csrShifts
   REPLACE ShiftEndPM WITH rek.time
   REPLACE koloutpm WITH rek.kol
ENDSCAN

Select * From csrshifts

I removed all your IFs, partly because of assuming they never change anything, partly because they were simply not checking sensible things.
If you want to check data while you copy it over to be correct, then it even starts at the first record in each SCAN iteration. This needs to have a different date then the previous 4 records. Perhaps only a different LName for the same date also would be correct.

One way to not need these checks would be to add in missing rows. For example, if only 3 records exist while the PM shift didn't end, you add a 4th record without time, either with the empty date or better NULL. If you do that as preparation step, then you also don't need checks during this loop.

The major change I made was skipping 1 each time in rek, don't go back -1 or even -2, why? If you expect records out of order then just sort them by Idnum and time and they are in chronological order. Mike Lewis Skip -1 was an error.

If you expect more than 2 rek records for the AM or PM shifts, you'd need to pick out the extreme two times for the IN and OUT record or manually check, or even have two AM shifts with a break in between. And in that case your idea of 4 times per line of the report fails or you summarize them into one shift without a break.

There would be other strategies to use, when your data isn't sorted. You could always see what date they are for and move to the record, if you already have it or add one. Then see if the time is AM or PM. If it's the first AM time you have store it as ShiftStartAM, if its the second AM time store it as ShiftEndAM, but if it's before the time you already stored in ShiftStartAM swap them out. And so on. If you do it that way the data can also be unordered and you'd finally have the earliest AM, latest AM, earliestPM and latest PM time in the four time fields for the day.

Chriss

 

[Mandy_crw]

hi everyone i really cant do the four columns so this what i made, i hope its correct...

CREATE CURSOR csrShifts (IDNum C(10), LName C(24), ampm c(2), ShiftDay c(9), ShiftStartAM T, kolinam c(7), ShiftEndAM T, koloutam c(7))

SELECT rek

SCAN

deyti = SUBSTR(DTOC(rek.time),1,10)
timy = HOUR(rek.time)

SELECT csrShifts
APPEND BLANK && only one APPEND per iteration

REPLACE IDNum WITH rek.IDNum
REPLACE LNAME WITH REK.LNAME
replace ShiftDay WITH rek.dey

IF HOUR(rek.time) =< 11 AND rek.kol = "IN"
REPLACE ShiftStartAM with rek.time
REPLACE kolinam WITH rek.kol
REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

IF HOUR(rek.time) => 12 AND rek.kol = "IN"
REPLACE ShiftStartAM with rek.time
REPLACE kolinam WITH rek.kol
REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

IF rek.kol = "OUT"
REPLACE ShiftEndAM with rek.time
REPLACE koloutam WITH rek.kol
*REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

IF rek.kol = "Missing"
REPLACE ShiftEndAM with rek.time
REPLACE koloutam WITH rek.kol
*REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF


SELECT rek
SKIP 1
SELECT csrShifts


IF HOUR(rek.time) => 12 AND rek.kol = "IN"
REPLACE ShiftStartAM with rek.time
REPLACE kolinam WITH rek.kol
REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

IF rek.kol = "OUT"
REPLACE ShiftEndAM with rek.time
REPLACE koloutam WITH rek.kol
*REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

IF rek.kol = "Missing"
REPLACE ShiftEndAM with rek.time
REPLACE koloutam WITH rek.kol
*REPLACE ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )
ENDIF

ENDSCAN

Select * From csrshifts INTO CURSOR maindeta
*Select * FROM csrshifts
aydi=ALLTRIM(this.Parent.text1.value)
felter = MONTH(ctod(this.Parent.text10.value))

IF EMPTY(aydi)
COUNT FOR aydi = ALLTRIM(maindeta.idnum) TO lncount
ELSE
COUNT FOR aydi = ALLTRIM(maindeta.idnum) AND felter = MONTH(maindeta.shiftstartam) TO lncount
ENDIF

IF lnCount = 0
MESSAGEBOX("There are no DATA to show for this Report! Click OK to return!",0,"Student's Account")
ELSE
REPORT FORM dtr FOR aydi = ALLTRIM(maindeta.idnum) AND MONTH(maindeta.Shiftstartam) = felter TO PRINTER PROMPT PREVIEW
ENDIF

ENDPROC

hence this output...

 

[Chris Miller]

It's hard to see whether this is okay. The output seems correct. Now you also have records that have "Missing" in rek.kol. That really puzzles me, if there is a recorded time it would be IN or OUT, wouldn't it? How can it be "Missing" and have a specific datetime?
Edit: I see, the time for "Missing" is copied. You better leave that empty.

The way you process rek, I can only hope it's sorted by datetime. You'd hardly get this output, if it wasn't, but I'd explicitly sort rek before going into the SCAN loop.

The rest is details, as you now still have 2 rows per day and an ampm field, ShiftStartAM and ShiftEndAM should just be called ShiftStart and ShiftEnd, whether AM or PM shift will be specified by the AMPM column. It actually also is clear from the times, you could drop the ampm field and display IIF(HOUR(ShiftStart)<12'AM','PM'), remember ShiftStartAM would be renamed to ShiftStart only.

Chriss

 

[Chris Miller]

I see, the time for "Missing" is copied

At a second glance: Yes, but also there are records in rec which have "Missing", so rek must be populated from some original data. The records logged by an attendance system don't have "Missing", they must either be IN or OUT. The only way I could imagine "Missing" as kol value is if the system automaticcally creates records for incomplete AM or PM shifts at noon or midnight. But they should have 12:00 or 0:00 as time, shouldn't they?

What is the origin of the rek records? How does a kol="Missing" get into the rek.dbf?

Chriss

 

[Mike Lewis]

Mandy, just to add one small point to those that Chris has just given you ...

Your two time columns show the IN and OUT times to the nearest second. If you have a good reason for showing these times with such precision, that's fine. But otherwise, I suggest you just show them to the nearest minute. This will reduce clutter and make the information that little bit easier to assimilate.

To achieve that, set the expressions in the corresponding report fields to [tt]LEFT(ShiftStartAM, 5)[/tt] (replacing ShiftStartAM with the name of the relevant field in each case). If the underlying fields are datetime data types, first convert them to character strings like so: [tt]LEFT(TTOC(ShiftStartAM, 2), 5)[/tt].

This is not a particularly important point. But in general it's a good rule that a report should show all the information that a user needs - and no more.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads