Problem with calculating data using different tables

[janak vyas]

WE HAVE A PROGRAM OF PAYROLL WRITTEN IN VISUAL FOX PRO WHERE IN WE HAVE A FEATURE TO CALCULATE ARREARS OF EMPLOYEES.
WE INPUT THE STARTING MONTH AND THE END MONTH FROM WHICH WE WANT TO CALCULATE THE ARREARS, WE THEN INPUT EMPLOYEE CODE -> IT SEARCHES THE EMPLOYEE DATABASE FOR EVERY MONTH AND GET HIS/HER SALARY AND CALCULATES THE DIFFERENCE AND PUTS THE AMOUNT IN ANOTHER DBF FILE.

THERE ARE FOUR DATABASE, XYZ WHICH HAS ITEM CODES SO DOES A,B,C HAVE ITEM CODES
WE HAVE TO COMPARE VALUE OF DB XYZ WITH A, B AND C
WHEN IT COMPARE IT ONLY COMPARES ONE ITEM. DOES NOT ITERATE THROUGH EVERY ITEM IN THAT DB.


IS THERE ANY WAY THAT WE CAN ITERATE WITH EVERY ITEM ?




( APOLOGIES AS I AM NOT ABLE TO EXPLAIN EXACTLY AS ENGLISH IS NOT MY FIRST LANGUAGE )
I WOULD SURELY HELP IN EXPLAINING IF YOU WERE NOT ABLE TO UNDERSTAND.

 

[Olaf Doschke]

From what I understand you have the options to UPDATE with a WHERE ITEMCODE=x that does not only act on the first record with the same item code. Even not using SQL you have the SCAN-Loop with FOR ITEMCODE=x clause to go through all records of the same item code. And if you now use LOCATE to find the first match. Read the help topic about the CONTINUE command to go through further records, too.

It would already help if you don't post in all caps. All lower case is better readable.

And then post your code that can be adjusted, because I can't address all ways that could be helpful and give you a lesson of SQL or VFP in general in one post. Especially when English already is a problem.

Knowing the code you have at hand - especially the relevant code lines - helps to limit recommendations, as I know already retranslating this will take some time for you.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mukesh Khandelwal]

From your post i understand that in your payroll software you have functionality to calculate arrears by giving "Start Month", "End Month" and "Employee Code". your program will search Employee table and calculate salary of every month and stores the difference in a table.

and then you talked about ITEM CODES stored in 4 databases and you want to compare values in XYZ database with database A,B,C.

I am not able to understand the question. please share your program here so that i can read the program and will try to help.

Thank you

Mukesh Khandelwal

 

[janak vyas]

USE MTHDES // month file having jan, feb , march and so on
GO MTO
FL= 'T' + MTH_NO+SUBSTR(COCD,1,3)+ TDYEAR+'.DBF'
USE '&FL'

REPLACE ALL ARRBASIC WITH 0
REPLACE ALL ARRHRA WITH 0
REPLACE ALL ARRCONV WITH 0
*BROWSE FIELDS e_name,arrbasic,arrhra,arrconv FONT 'arial bold',18



CLOSE ALL
SET EXACT OFF
*////////////////////////////////////REPLACE ZERO OVER/////////////////////////////////////////////////////////////////////////////////

*////////////////////////////////////VARIABLE STORAGE///////////////////////////////////////////////////////


start=1
end=4

STORE 0 TO Mbasic,Mhra,Mconv,Moth,Marrbasic,Marrhra,Marrconv,Marroth,Mcbasic,Mchra,Mcconv,McothST
STORE SPACE(30) TO ENAME


*/////////////////////////////////////VARIABLE STORAGE OVER///////////////////////////////////////////////////////




*////////////////////////////////////DATA BASE SELECION/////////////////////////////////////////////
SELECT C
*************************************************

use mthdes
GO MTO
MTHNM = MTH_NAME


SELECT 2
EMAST='EMAST'+COCD
USE '&EMAST'
SET FILTER TO DTOC(dt_leav)=' / / '
INDEX on e_name TO 'e'+cocd

GO top
*ecd=emp_no
ename=e_name
MCbasic=b->basic+b->hda && Current Rate of Salary
Mchra=b->hra_per && Current Rate of Salary
Mcconv=b->conv_all && Current Rate of Salary
Mcoth=b->oth_alow && Current Rate of Salary



*////////////////////////////////////////DATA BASE SELECTION OVER///////////////////////////////////////////////////////////////



DO WHILE .not. EOF()
ecd=emp_no
SKIP
enddo
*WAIT WINDOW emp_no+' '+e_name
*DO WHILE .not. EOF() &&.or. START<=END && .AND. B <= 9





*///////////////////////////////////////ARREAR CACLULATION BEGIN////////////////////////////////////////////////////////////
*DO WHILE FOUND(ECD)

DO WHILE START<=END && .AND. B <= 9

SELECT 2 && added
ecd=emp_no
MCbasic=b->basic+b->hda && Current Rate of Salary
Mchra=b->hra_per && Current Rate of Salary
Mcconv=b->conv_all && Current Rate of Salary
Mcoth=b->oth_alow && Current Rate of Salary



*WAIT WINDOW emp_no+' '+e_name


*GO TOP && added
*BROWSE && added


SELECT 3 && month master file

GO start
SELECT 3


FL= 'S' + '0'+ALLTRIM(STR(START)) +SUBSTR(COCD,1,3)+TDYEAR+'.DBF'


IF .NOT. FILE('&FL')
START = START + 1
LOOP
ELSE

USE '&fl'

INDEX ON EMP_NO TO ASDFASFSAFAS
SEEK '&Ecd'

*IF .NOT. FOUND()
*SKIP
*ENDIF

BROWSE && SKIPING AFTER TWO EMPLOYEES

*WAIT WINDOW emp_no+' '+e_name && TWO CONT EMPLYEE THAN RETURN

MBASIC=basic+hda
Mhra=hra_per
Mconv=conv_all
Moth=oth_alow

mARRBASIC=(MCBASIC-MBASIC)*WAGE_DAY/MONTH_DAY+mARRBASIC && sum arrear
Marrhra=((MChra-Mhra)*WAGE_DAY/MONTH_DAY) +Marrhra && sum arrear
Marrconv=(mcconv-mConv)*WAGE_DAY/MONTH_DAY+Marrconv && sum arrear
Marroth=(mcoth-Moth)*WAGE_DAY/MONTH_DAY+Marroth && sum arrear

ENDIF
START = START + 1

DO case
CASE start=12
start=1
CASE start=11
start=12
CASE start=10
start=11
endcase

SELECT 3
use mthdes

SELECT 2 && added
*SKIP && added
ecd=emp_no && added
ename=e_name
MCbasic=b->basic+b->hda && Current Rate of Salary
Mchra=b->hra_per && Current Rate of Salary
Mcconv=b->conv_all && Current Rate of Salary
Mcoth=b->oth_alow && Current Rate of Salary





SKIP
*BROWSE FIELDS emp_no,e_name,dt_leav FONT 'arial bold',14 && added
*LOOP && added



ENDDO

*ENDDO



*lno=21
*SET COLOR TO w+
*@ lno,01 say ename font 'arial bold',14
*
*@ lno,55 say mARRBASIC PICT '9999999.99' font 'arial bold',14
*@ lno,82 say mARRHRA PICT '9999999.99' font 'arial bold',14
*@ lno,112 say mARRCONV PICT '9999999.99' font 'arial bold',14
*@ lno,112 say mARRoth PICT '9999999.99' font 'arial bold',14
*lno=lno+1

*////////////////////////////ARREAR CALCULATION OVER////////////////////////////////






*////////////////////////////REPLACING ARREAR TO DAYS FILE//////////////////////

CLOSE ALL

SELECT a


USE MTHDES
GO MTO
FL= 'T' + MTH_NO+SUBSTR(COCD,1,3)+ TDYEAR+'.DBF'
USE '&FL'



DO WHILE .not. EOF()

SELECT a && destination file

REPLACE a->arrbasic WITH marrbasic
REPLACE a->arrhra WITH marrhra
REPLACE a->arrconv WITH marrconv

SKIP


marrbasic=0
marrhra=0
marrconv=0


enddo

*///////////////////////////REPLACING ARREAR OVER///////////////////////////////////////


INDEX ON E_NAME TO SDFASF
BROWSE FIELDS EMP_NO,e_name,arrbasic,arrhra,arrconv FONT 'arial bold',10

*ENDIF

 

[Mukesh Khandelwal]

Hi

I do not think this code is working correctly or is it working at all. i found following issues in it.

You have assigned following variables
*////////////////////////////////////VARIABLE STORAGE///////////////////////////////////////////////////////
Start=1
End=4
and on the basis of these variables program is trying to calculate Sum of Arrears for all 12 months. see below loop starting

Thank you

Mukesh Khandelwal

 

[Olaf Doschke]

Mukesh really has found a major bug.

Going to EOF in work area 2 (or b) is making the rest of the code quite useless.

First of all I'd say choose between addressing workareas by number or letter, 2 is B, so expressions like b->basic+b->hda refer to fields basic and hda in Workarea 2, the EMAST dbf. The code would already become a bit clearer if that would not be mixed up. At many other places only field names are used, which must exist in the current workarea. Overall this is old code likelywhen FP only had a limited set of workareas.

Effectively these lines bring you to EOF skipping AFTER the last record of EMAST:

SELECT 2
EMAST='EMAST'+COCD
USE '&EMAST'
SET FILTER TO DTOC(dt_leav)=' / / '
GO top
...
DO WHILE .not. EOF()
ecd=emp_no
SKIP
enddo

The way the FILTER is set you want to get the emp_no of the last row with no dt_leav date set. Well, you go past that row. EOF is AFTER the last record of a dbf not at the bottom row. But I don't know whether a SKIP -1 at that place would fix the logic. Or GO BOTTOM instead of the loop, or even simpler INDEX ... DESCending, ie indexing so the last row is sorted to the top, and then LOCATE or GO TOP. And I would also not try on production data, I hope you test on copies of data anyway.

As this code does not express to me, what it wants to achieve, it's not mendable without further knowledge of data structures and data and wanted results.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[janak vyas]

Thank you so much Mukesh!
It actually helped us to think out of the box, (although we scrapped *for now* the idea of having to calculate for all 12 months ) we instead worked around of just looking out for the previous month and giving the arrears in the current month.

With your help we got to know where the loop started coming out and the using of skip because of which the pointer was showing at the end of the file. All the other things were working good enough.

thank you so much!

 

[janak vyas]

Hey! Olaf, thank you so much more giving in your time ( also to Mukesh to going through all that ).

The thing is that we have never learned or referred a book to know about vfp or as matter of fact fp.
All that we have done is looked at old codes from fp at the time of 1995 and learned what code does what, we didn't had internet back then, gradually we just moved everything from the same code of fp to vfp and tried to works everything around the look.

It's because of that we at time to time get stuck not knowing what to do and what not to, we just get things to work ( though now that having a experience of looking at codes we previously had we get to know how things work )

I feel relaxed having to have a community such like this with active members.

Stay safe.

 

[Olaf Doschke]

Hallo Janak,

Good, it seems you fixed it with the answer from Mukesh Khandelwal already.

With your help we got to know where the loop started coming out and the using of skip because of which the pointer was showing at the end of the file

There you have another problem: If you look into a browse window after the DO WHILE loop went to EOF, the record pointer will be shown in the last record, the bottom record. Which means a browse will move the record pointer from EOF back one. That could mean you don't see a problem when you single step and watch what the code does in a browse but just running the code without looking it fails.

Here's a simple demo of the fact EOF and bottom record differ and how browse affects the record pointer:

Clear
_screen.FontName="Consolas"
Use labels
? '                   EOF     Recno  (record count is '+Transform(Reccount())+')'
? 'after use (open):', Eof(), Recno()
Go bottom
? ' after go bottom:', Eof(), Recno()
Skip 1
? '    after skip 1:', Eof(), Recno()
Browse nowait
? '    after browse:', Eof(), Recno()

When you see the browse window, don't do anything in it, just close it again. (I could have programmed closing it, too, but it would not be seen that way). Ideally, you convince yourself once you see the browse window you can only navigate within the records and never get to EOF.

I also printed the RECNO(), so you see another pitfall: The EOF location after all the records in a DBF has already the next record number, if you program a logic remembering the position of the record pointer by its RECNO() and the GO back to the record by its number, and you want to end up where you started after something was done, like inserting data, you could end up there instead of going back to EOF. And that's just one pitfall.

Foxpro is full of such specialties you have to know to demystify some strange behavior and differences in runtime vas design time. Including things you may do in between single stepping code in the debugger, even not intentionally and in full awareness. The language was developed before rules such as POLS (principle of least surprise) were taken more seriously. But it's profiting from being balanced for complexity vs abilities quite well.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name