How to manipulate table FIELDNAME? 4

[Rock4J]

Hi,

I have a table that looks like this:

Tablename: Dchkroll.dbf
Fieldname -> Name , Empno , D01, D02, D03, D04, D05, D06, ... , D29, D31
(D01 to D31 is referring to working days which holding working-hours for the day.)

I want to manipulate the D01-D31 (1 to 31 without the 'D') from the above table to compare it with 'HOLDATE' (DAY) value in the following table:

Tablename: Holiday.dbf
Fieldname -> Month , Holdate , Holdesp , PH
('HOLDATE' values is in DATE format)

The code that i'm trying to write is something like this:

[using do-while/for-next/scan-endscan to read from Dchkroll.dbf]
if the 1-31 of the D01-D31 fieldname is equal to day(HOLDATE) then
*store data into a cursor cursor_table_name (name, empno, holdate, hours)
end if

I'm actually trying to create a report to list out employees who have worked on holiday.

Probably there is other way to write this code that would be much better.

Hopefully my explanation is clear. Please help.

Thanks.

Regards,
Rocky

 

[Rock4J]

For reference:

Regards,
Rocky

 

[JRB-Bldr]

Probably there is other way to write this code that would be much better.

Before we get into considering any code, let's look at your proposed Data Table Architecture - it needs work.

You need to consider Data Normalization.
Do a Google Search for: data normalization or database normalization

Tablename: Dchkroll.dbf
Fieldname -> Name , Empno , D01, D02, D03, D04, D05, D06, ... , D29, D31

This should be 2 tables
Something like:
Employee: Empno, Name
And Workdays: Empno, Date, WorkHrs, <also maybe a Logical Field named Holiday>

Now its a relatively simple code task to:
create a report to list out employees who have worked on holiday.

Good Luck,
JRB-Bldr

 

[mm0000]

The following code might do what you require. However, it has not been tested.

CREATE CURSOR repcursor (name c(30),empno c(10),holdate d,hours n(2))

SELECT 0
USE holiday

SELECT 0
USE dchkroll

LOCATE

SCAN
   FOR icount = 1 TO 31
       mcdate = 'D'+PADL(icount,2,'0')
       STORE &mcdate TO m.nhours
       STORE icount TO m.icount
       STORE name TO m.cname
       STORE empno TO m.cempno
       SELECT holiday
       SCAN
          IF m.nhours # 0
             IF m.icount = DAY(holdate)
                STORE holdate TO m.dholdate
                SELECT repcursor
                APPEND BLANK
                replace name WITH m.cname
                replace empno WITH m.cempno
                replace holdate WITH m.dholdate
                replace hours WITH m.nhours
             ENDIF
          ENDIF  
          SELECT holiday 
       ENDSCAN
    ENDFOR  
    SELECT dchkroll
ENDSCAN

 

[Rock4J]

Thanks jrbbldr, I do agree with you and I believe it would be better. But I could not change the structure of the tables yet for now. Because the Dchkroll.dbf is more like the master table of the application and it's related to another application.

And thanks too mm0000.. I think your code much more like it.. I will try to use your code.. by the way, could I change it this way:

USE dchkroll -to- USE J:\Apps\Chkroll\DATA\Dchkroll.dbf

(J: is a mapped network drive)

because I want to make the report in "J:\Apps\Payroll\payroll.exe" and the Dchkroll.dbf is in "J:\Apps\Chkroll\Data\".

THANKS GUYS !!

Regards,
Rocky

 

[Olaf Doschke]

You've got your solution. I'd add some useful functions:

DAY(holdate) - you know that already.
FIELD(N) - gives the fieldname of field number n of a table
VAL(str) - extracts the value of a string containing a number
CHRTRAN(FIELD(N),"D","") - would remove "D" from a field name
"D"+PADL(5,2,"0") - creates "D05" (as mm0000 used it already)

I would always aim to compare dates, when you want to compare dates, so I would rather not just compare DAY(holdate) to the number xx of the Dxx field. The Dchkroll.dbf should also contain fields about year and month, if it's not even a table per month, otherwise the D fields could also refer to another month. So put together a date to compare with holdate. For that matter you have DATE(year,month,day) or if you have the first of the month in a date field can compute firstofmonthdate+Days-1 to get another day of the month.

Bye, Olaf.

 

[Rock4J]

Thanks Olaf.. I agree that it's better to compare the date specifically.. I cannot do it yet.. but it's a good information..

By the way, actually I have tested the code and it was ok when compiling.. but there was an error appear that looks like this 'network error - file in use'. This error probably coded by the previous programmer to prevent the table being used by too many user. I'll try to check it first.

Regards,
Rocky

 

[Rock4J]

Oh yea, i forgot to mention that I use:

USE J:\Apps\Chkroll\DATA\Dchkroll.dbf

but I cannot change the 'select dchkroll' in the following area:

...
...
ENDSCAN
ENDFOR
SELECT dchkroll <<------ is there a way that i can use the full path?
ENDSCAN

Regards,
Rocky

 

[Olaf Doschke]

I think we alredy had the difference between SELECT-SQL and SELECT workarea.

Select dchkroll is selecting he workarea with alias name dchkroll, not the file with name J:\Apps\Chkroll\DATA\Dchkroll.dbf.

If you have a fullpath and want to be sure you work on it, and there are many dchkroll.dbfs in differente paths, you can check if the workarea has the table you want via DBF("dchkroll") you get the full file name of the table open in workarea dchkroll, also you can of course close dchkroll and (re)open the file you know is the correct one, eg in one command via

USE (yourfilename) IN SELECT("dchkroll")

If a workarea has the alias dchkroll SELECT("dchkroll") returns the workarea number, if no it returns 0, which is good for USE IN workarea, as 0 is addressing the next free, unused workarea. So this closes any dchkroll and opens the one you want, no matter if there is one open already or not.

Finally, if you also want to select that DBF a the currently active workarea, you still need the SELECT dchkroll.
And last not least, there is no need to change SELECT dchkroll to USE (yourfilename) IN SELECT("dchkroll"), if you know what DBF is open as dchkroll anyway, as you opened it in the same code a few lines up. There is no reason to fear a workarea changes to display data of a different file on it's own. Once a table is opened, it's opened. It's gone when a datasession closes, it's also not available, if you change datasession, but it's still open in the datasession you left. And you change datasession, when you call code of a form with a private datasession, for example, but in the normal case your code handling a table is in one prg, procedure, function or method and you don't have to consider scenarios in which SELECT dchkroll addresses the wrong file. You have the control about what DBF you open in the first place, and that's it, then, isn't it?

Bye, Olaf.

 

[Olaf Doschke]

And another thing:

...
...
ENDSCAN
ENDFOR
SELECT dchkroll <<------ is there a way that i can use the full path?
ENDSCAN

The position of SELECT dchkroll short before ENDSCAN points to it being the table you want to be selected at the start of the next iteration, at the first line after the corresponding SCAN. You have to know SCAN always selects the alias it scans, so when you have SELECT alias / SCAN ... ENDSCAN the SCAN always will select alias and in the first line of iteration code alias is the selected workarea, there is no need to SELECT alias at the end of a loop. Indeed, if this is not the alias you scan, then SELECT otheralias MUST be the begin of the iteration code so you override which alias is active.

"network error - file in use" is no naive foxpro error, just the "file is in use" part of it. So search code putting "network error" in front of a message (eg via Codereferences search for network). You may simply have a flaky network to fight with and nothing else.

Bye, Olaf.

 

[Rock4J]

Okay.. now there is another error.. the error message is:

Variable 'D02' is not found
STORE & mcdate TO m.nhours

not sure where is the problem from here.

Regards,
Rocky

 

[Olaf Doschke]

...I looked it up to make sure.

There is no error "file in use", just "file is in use", and there are three errors in detail:
108 - File is in use by another user.
3 - File is in use.
2191 -File is in use in another data session

Assumed your error is any of these, SELECT alias is never causing it, as SELECT alias is not using a file, it's mereley selecting a workarea containing an already opened file.

And if your overall goal is to iterate several dchkroll files in a loop, I'd expect code to use such a file at the begin of an outer loop and not at the end of it, anyway.

Bye, Olaf.

 

[Rock4J]

Okay Olaf.. SELECT workarea and SELECT SQL.. Yea.. I guess i will take time to get use to it, and being able to differentiate between those two..

at the mean while, please help me to determine the last error that i submitted.

Variable 'D02' is not found
STORE & mcdate TO m.nhours


THANKS !



Regards,
Rocky

 

[Olaf Doschke]

Variable D02 is not found"

Well, pretty much looks like the currently active workare has no field D02, you selected or opened some other DBF not containing that field name.
Because: The order of search for a name is: first search fields of the current work area. If non e is found, search a variable of the same name, if nne is found error "variable d02 not found".

The way foxpro allows to adress a field just by it's name is causing this confusion, but variables also only have a name. And since foxpro looks for variables last and not first, this is the error you end up with.

Bye, Olaf.

 

[Rock4J]

I think the 'D02 error' also have something to do with 'File is in use error'.. because the D02 is a fieldname in the Dchkroll.dbf.. I will try the USE (yourfilename) IN SELECT("dchkroll") first.. I got to get off for now.. I'll get back here as soon as possible.. Thanks in advance!

Regards,
Rocky

 

[Olaf Doschke]

An appropriate way to overcome this problem is what I explained here: thread184-1706929
Address a field by tablename.fieldname. So set mcdate = "dchkroll.d"+padl(icount,2,"0")

Bye, Olaf.

 

[Olaf Doschke]

>I think the 'D02 error' also have something to do with 'File is in use error'

Well, you've shown the d02 is a field name of that table, sure. But select dchkroll points out this table is already opened in code previous to the loops. Or should be. So I don't see how that error is related, unless you fail to open a table, ignore i and continue. Of course you get all kind of follow up errors, if you ignore an error.

And don't open a table at the end of a loop. I can't imagine any situation this would be good. Put that at he start. Natural order of reading always is the better order to understand code in the first place.

Bye, Olaf.

 

[mm0000]

I've noticed a slight error in the code. Change the code to :

CREATE CURSOR repcursor (name c(30),empno c(10),holdate d,hours n(2))

SELECT 0
USE holiday

SELECT 0
USE dchkroll

LOCATE

SCAN
   FOR icount = 1 TO 5
       mcdate = 'D'+PADL(icount,2,'0')
       STORE &mcdate TO m.nhours
       STORE icount TO m.icount
       STORE name TO m.cname
       STORE empno TO m.cempno
       SELECT holiday
       SCAN
          IF m.nhours # 0
             IF m.icount = DAY(holdate)
                STORE holdate TO m.dholdate
                SELECT repcursor
                APPEND BLANK
                replace name WITH m.cname
                replace empno WITH m.cempno
                replace holdate WITH m.dholdate
                replace hours WITH m.nhours
             ENDIF
          ENDIF  
          SELECT holiday 
       ENDSCAN
      SELECT dchkroll
    ENDFOR  
ENDSCAN

 

[dbMark]

mm0000 moved a line so that the dchkroll table is always selected when the FOR/ENDFOR loops.

However, you may want to keep: FOR icount = 1 TO 31

 

[mm0000]

Yes, icount should be 31. I had changed it to 5 for testing and forgot to change it back to 31.