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]

Mike, I even think it could make a day fit into one line. Espeically when printed in landscape instead of portrait.

Mandy, I even think in landscape orientation you could a day with both AM and PM shifts in one line. And it's simply just using an option in the print layout of a report.

Chriss

 

[Mandy_crw]

hi Chriss... yes i have program that if in case the employee forget to input his id, automatically it will copy the time of "IN" and put missing for the out....

Ok Mike i'll take note of that... i will not include the seconds in the time...

Thanks for all your inputs guys... God bless....

 

[Mandy_crw]

Hi Mike, Chriss and everyone.... I dont know but after i have executed the DTR button, and display the report, i can no longer access the cursor which i previouly created in the procedure load(). It says "cannot access the selected table. CsrDemo" Please help e what happen, and what to do... does it have something to do with the "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))" which i included in the DTR button? Thanks and God bless...

 

[Mike Lewis]

Mandy, are you sure that that is the correct wording of the error message? The reason I ask is that a "Cannot access the selected table" (which is error no. 1152) is usually associated with an invalid RowSource of a listbox or combo box. But you have not mentioned that you are using a listbox or combo box.

If that doesn't help, please let us know the line of code where the error occurs. Also, at the point of the error, please check to see if the cursor that you created in the Load still exists. You can do that by looking in the Data Session window, which you can access from the Standard toolbar or by typing SET in the command window.

If the cursor no longer exists, then it is likely that it was overwritten by another cursor or by a table being opened in the same work area. But that wouldn't explain the wording of that particular message.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Another thought: Are you by any chance printing the report to a PDF? If so, are you saving the PDF in a directory other than VFP's default directory.

If so, it is possible that the PDF driver is changing VFP's default directory. This is quite a common problem with certain drivers. The cure is to save the directory before you print the report, and then restore it afterwards:

* Save default drive and directory
lcDefault = SYS(5) + SYS(2003) 

* Print the report 
REPORT FORM ...

* Restore the drive and directory
SET DEFAULT TO (lcDefault)

Give it a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

If you CREATE CURSRO; that'll create it in an empty workarea, but the problem could simply be, that it changes the current workarea o the new crsShift cursor.

Any control or report field that just works with field names not alias.fieldname, will then not find the fields it was bound to.

That could be solved by SELECT CrsDEmo.

The question that rises is, if you are aware the crsShifts is now the data you need to print, so the report should not depend on crsDemo, should it? Also please check if your report uses a private datasession. Then there would be no point in creating the data to print in the form data environment. Either you let the report work without an own private datasession or you add code to create the cursor the repprt needs to print into the report.

All in all I think you have several logical flaws here, as sumarized in short:
1. crsShifts is now the data to print, notcrsDemo, that was never mentioned before and its meaning is totally obscure to us.
2. Creating report data before starting the REPORT FORM command means the report does not include creating, loading or opening data in its own private datasession, the data comes from outside and so the report has to work in the current datasession.
3. No code in the report should USE a table, if you want to use it after printing, especially if a report is fed with data from outside, it should also not care about close (tables) all or anything ike that or have its datasession set to close all data. If the data feeding is the job of the caller, any clearing also is its job.


Chriss

 

[Rick C. Hodgin]

If you can guarantee they'll be in consecutive 4-block entries by idnum + lname + date, then you can use code like this:

CREATE CURSOR c_data ;
( ;
    row         i, ;
    idnum       c(10), ;
    time        t, ;
    lname       c(35), ;
    kol         c(3), ;
    crc         i ;
)

* Create some sample test data over multiple days
FOR lnI = 17 TO 27
    INSERT INTO c_data VALUES (0, "0008080753", DATETIME(2021, 09, lnI, 21, 31, 09), "Smith", "IN", 0)
    INSERT INTO c_data VALUES (0, "0008080753", DATETIME(2021, 09, lnI, 21, 31, 18), "Smith", "OUT", 0)
    INSERT INTO c_data VALUES (0, "0008080753", DATETIME(2021, 09, lnI, 21, 32, 49), "Smith", "IN", 0)
    INSERT INTO c_data VALUES (0, "0008080753", DATETIME(2021, 09, lnI, 21, 34, 29), "Smith", "OUT", 0)
NEXT

* Update the row and calculate a CRC on each row
REPLACE ALL ;
    row WITH IIF(RECNO() % 4 = 0, 4, (RECNO() % 4)), ;
    crc WITH INT(VAL(SYS(2007, idnum + lname + DTOC(TTOD(time)))))

* Extract out each part into 4 reference cursors
SELECT * FROM c_data WHERE row = 1 INTO CURSOR c_data1 READWRITE
INDEX ON crc TAG main
SELECT * FROM c_data WHERE row = 2 INTO CURSOR c_data2 READWRITE
INDEX ON crc TAG main
SELECT * FROM c_data WHERE row = 3 INTO CURSOR c_data3 READWRITE
INDEX ON crc TAG main
SELECT * FROM c_data WHERE row = 4 INTO CURSOR c_data4 READWRITE
INDEX ON crc TAG main

* Construct what we want our output to look like, populating from time1 information
SELECT idnum, CTOT("") AS t1in, CTOT("") AS t1out, CTOT("") AS t2in, CTOT("") AS t2out, lname, crc ;
    FROM c_data ;
    WHERE row = 1 ;
    INTO CURSOR c_data_out READWRITE

* Relate our master record into each of the time slots
SET RELATION TO ;
    crc INTO c_data1, ;
    crc INTO c_data2, ;
    crc INTO c_data3, ;
    crc INTO c_data4

* Update all of the times
REPLACE ALL ;
    t1in    WITH c_data1.time, ;
    t1out   WITH c_data2.time, ;
    t2in    WITH c_data3.time, ;
    t2out   WITH c_data4.time

* Clean up
USE IN c_data
USE IN c_data1
USE IN c_data2
USE IN c_data3
USE IN c_data4

* The multiple rows are now in a horizontal field layout
BROWSE FIELDS idnum, lname, t1in, t1out, t2in, t2out LAST

--
Rick C. Hodgin

 

[Rick C. Hodgin]

Mandy, I think you could do four columns if you changed your format a little. Some of the row information is could be moved to column headers.

REPORT FORM sample.frx PREVIEW

See attached for the sample.frx layout.

--
Rick C. Hodgin

 

[Mandy_crw]

Hi Mike and Chriss.... to explain further... i have two buttons named DTR and UPDATE... the DTR button when clicked uses crsShifts cursor, and the UPDATE button uses CsrDemo cursor. When i clicked the update button it works well, when i clicked the DTR button, it shows the report with no problem. When i go back the UPDATE button, it will now show the error that "cannot access the selected table. CsrDemo"

Chriss... I've already tried using "select CsrDemo" after the generation of report, but it still says "cannot access the selected table. CsrDemo" when i click the UPDATE button that uses CsrDemo cursor....


Thanks Rick... I'll try to practice your codes.....Thanks....


Thank you everyone... God bless!

 

[Mike Lewis]

Mandy,

Did you consider the points I made in my last two posts, above? Neither of them is necessarily the solution, but it would be useful to know if you have considered them and eliminated them?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mandy,

what are you doing in the UPDATE button? Are you doing an SQL query INTO CURSOR crsDemo? Then you should instead do it to another cursor name, like crsTemp. ZAP in crsDemo and append dbf('crsTemp') to not get that error.

The reason is explained in the help of the error message to this error:

https://www.vfphelp.com/help/html/968fa1c6-2111-44f0-8111-e2f9f4b9fa57.htm

Even if you have crsDemo before and after the select, during the query the old crsDemo is closed before the new crsDemo is created and that causes that error. The solution that the help proposes also works, but the grid there could lose some properties you maintain by keeping crsDemo instead of setting rowsource empty.

It sounds like doubling the workload of first creating crsTemp and then appending all that to crsDemo. But the last step is mainly a local memory copy and takes almost no time. The actually fastest method would involve another empty temp cursor of the same structure as crsDemo to use as temporary rowsource, but that would only work if controlsources reference the field names only and not alias.fieldname. Besides that, I'm talking as if I was 100% sure about this as the root of your problem. You never talked of crsDemo beforehand. Is it displayed in another or even the same Grid that also shows the data for the report before you print it? Then another question would be why use separate cursors for the form than for the report?

Also that problem didn't came in by the DTR button change, it must have been that way before, too. The problems are unrelated.

Chriss

 

[Mandy_crw]

Hi Mike... yes mike the cursor no longer exist after i have chech the data session... and i also tried the second suggestion you made but it still look for the CsrDemo. Thanks...

 

[Mandy_crw]

Hi Chriss and Mike and everyone...i just made a database name shift in replacement of the cursor to avoid the error of closing the first cursor csrdemo... Thank you very much for always helping with my project... God bless...

 

[Chris Miller]

I wonder how that helped, but as it did: Congratulations on finding yet another solution to the problem.

Chriss

 

[Mandy_crw]

Hi everyone!! i have achieved it!! Thank you Chriss and everyone who have helped me....

Y = INPUTBOX("Enter ID Number!", "Authenticator","", 10000, 'Timed Out', 'Canceled')

SELECT rek

SEEK y

timy = HOUR(rek.time)
deyti = TTOD(rek.time)
alyn = rek.indicator

SELECT Shift

APPEND BLANK && only one APPEND per iteration
REPLACE IDNum WITH rek.IDNum
REPLACE LNAME WITH REK.LNAME
REPLACE ShiftDay WITH rek.dey

SELECT rek

SCAN NOOPTIMIZE FOR y = rek.idnum

IF alyn =< 4 or deyti = TTOD(rek.time)

DO case

CASE rek.indicator = 1
REPLACE shift.shiftam with rek.time
REPLACE shift.kolinam WITH rek.kol
REPLACE shift.ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 2
REPLACE shift.ShiftEndAM with rek.time
REPLACE shift.koloutam WITH rek.kol
REPLACE shift.ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 3
REPLACE shift.shiftpm with rek.time
REPLACE shift.kolinpm WITH rek.kol
REPLACE shift.pmam WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 4
REPLACE shift.Shiftoutpm with rek.time
rEPLACE shift.koloutpm WITH rek.kol
REPLACE shift.pmam WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

ENDCASE

ELSE

SELECT Shift

APPEND BLANK && only one APPEND per iteration
REPLACE IDNum WITH rek.IDNum
REPLACE LNAME WITH REK.LNAME
replace ShiftDay WITH rek.dey

deyti = TTOD(rek.time)
alyn = 3

SELECT rek

DO case

CASE rek.indicator = 1
REPLACE shift.shiftam with rek.time
REPLACE shift.kolinam WITH rek.kol
REPLACE shift.ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 2
REPLACE shift.ShiftEndAM with rek.time
REPLACE shift.koloutam WITH rek.kol
REPLACE shift.ampm WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 3
REPLACE shift.shiftpm with rek.time
REPLACE shift.kolinpm WITH rek.kol
REPLACE shift.pmam WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

CASE rek.indicator = 4
REPLACE shift.Shiftoutpm with rek.time
rEPLACE shift.koloutpm WITH rek.kol
REPLACE shift.pmam WITH IIF(HOUR(rek.Time) < 12, "am", "pm" )

ENDCASE

ENDIF

ALYN = ALYN + 1

ENDSCAN

*lcDefault = SYS(5) + SYS(2003)

Select * From shift ORDER BY shiftam INTO CURSOR maindeta

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

IF EMPTY(Y)
COUNT FOR Y = ALLTRIM(maindeta.idnum) TO lncount
ELSE
COUNT FOR Y = ALLTRIM(maindeta.idnum) AND felter = MONTH(maindeta.shiftam) TO lncount
COUNT FOR Y = ALLTRIM(maindeta.idnum) AND felter = MONTH(maindeta.shiftpm) 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 dtr2 FOR aydi = ALLTRIM(maindeta.idnum) AND MONTH(maindeta.shiftam) = felter ;
OR MONTH(maindeta.shiftpm) = felter TO PRINTER PROMPT PREVIEW

*SET DEFAULT TO (lcDefault)

ENDIF

SELECT shift

GO top

DO WHILE NOT EOF()
DELETE
SKIP
ENDDO

ENDPROC

 

[Mike Lewis]

Good to see you have got it working, Mandy. Thanks for posting your code.

May I make some minor comments on your code:

Y = INPUTBOX("Enter ID Number!", "Authenticator","", 10000, 'Timed Out', 'Canceled')

Two issues here. First, I would strongly advise against using a time out in an input box. It is very user-unfriendly. Imagine that you spend a few seconds thinking about what to enter, you move your fingers to the keyboard .. and the input box suddenly disappears. It is especially inappropriate here as you are not taking any special action in the event of a time out. You will actually be seeking a record whose key is 'Timed Out', which I feel sure is not what you want.

Also, you should be testing for the user cancelling the input box. You need to test for EMPTY(Y), and exit the procedure if it is .T.

*lcDefault = SYS(5) + SYS(2003) 
...
*SET DEFAULT TO (lcDefault)

The reason I suggested this code was in case the user chooses to print to a PDF driver. As I explained, if the driver displays a dialogue for selecting an output file, and if the user uses that dialogue to navigate to a different directory, then that will sometimes cause VFP's default directory to change, and that in turn could muck up what comes next. Because of that, I always bracket every REPORT FORM with the above pair of commands.

MESSAGEBOX("There are no DATA to show for this Report! Click OK to return!",0,"Student's Account")

A very minor point: You don't need to tell the user to "Click OK to return". In this case, there will only be one button in the message box. There is nothing else they can do other than clicking OK.

As I said, these are minor points, and do not detract from the overall code, but they are things that you might want to keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Ok Mike.... well noted... i will do what you have suggested... thank you and God bless Mike....

 

[Mandy_crw]

Hi everyone again... i've realised, i want the daily time record to be complete in one month, how to do i include those dates that the employees did not log in? I mean the complete dates from 1-31 in month should be reflected in the report, not only those dates that the employess have logged in... it shoudl show blank in the report.... I dont know where to inlcude it in my code.. thanks and God bless...