need a set of new eyes please.

i am trying to match one record from the first file to one in the second.
when a match i found, i need to see if any of the second file matches meet
the long 'if', if they do not i want to place a .f. in the first file. i can
tell why its not right, i just know it isnt working properly.
Tom

USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
USE c:\testdata\checkhdr IN 0 SHARED
SELECT lopdptfl
GO TOP
scan
REPLACE ALL ACTIVE WITH .T.
bkey = (PADL(city,6,"0")+PADL(dept,3,"0"))
SELECT fulopmas
DO WHILE .T.
IF EOF()
EXIT
ENDIF
IF (PADL(fulopmas.agency,6,"0")+PADL(fulopmas.dpt,3,"0")=(bkey) and
(fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND
!INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR
(fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND
YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
AND fulopmas.currwg <> 0))
SKIP
loop
ENDIF
replace lopdptfl.ACTIVE with .F.
ENDDO
endscan
*!* select lopdptfl
*!* set order to dept
*!* report form c:\testdata\inactivedpts.frx to printer for
lopdptfl.active=.F. noconsole
*!* close data

MichelRoy
Tue Dec 06 12:00:04 CST 2005

where is the relation between fulopmas and lopdptfl?

before
replace lopdptfl.ACTIVE with .F.
you should
SELECT lopdptfl && and seek/locate proper record

shouldn't you have a skip or exit to get out of your loop?



"Tom" wrote:

> i am trying to match one record from the first file to one in the second.
> when a match i found, i need to see if any of the second file matches meet
> the long 'if', if they do not i want to place a .f. in the first file. i can
> tell why its not right, i just know it isnt working properly.
> Tom
>
> USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
> USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
> USE c:\testdata\checkhdr IN 0 SHARED
> SELECT lopdptfl
> GO TOP
> scan
> REPLACE ALL ACTIVE WITH .T.
> bkey = (PADL(city,6,"0")+PADL(dept,3,"0"))
> SELECT fulopmas
> DO WHILE .T.
> IF EOF()
> EXIT
> ENDIF
> IF (PADL(fulopmas.agency,6,"0")+PADL(fulopmas.dpt,3,"0")=(bkey) and
> (fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
> OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND
> !INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
> AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR
> (fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
> MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND
> YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
> AND fulopmas.currwg <> 0))
> SKIP
> loop
> ENDIF
> replace lopdptfl.ACTIVE with .F.
> ENDDO
> endscan
> *!* select lopdptfl
> *!* set order to dept
> *!* report form c:\testdata\inactivedpts.frx to printer for
> lopdptfl.active=.F. noconsole
> *!* close data

MichelRoy
Tue Dec 06 12:05:02 CST 2005

oops, i need new eyes too

You have a scan followed by a replace ALL.
if you scan on lopdtfl
you only need to replace the current record ACTIVE with .T.
otherwise you end up at end of file before your endscan

"Tom" wrote:

> i am trying to match one record from the first file to one in the second.
> when a match i found, i need to see if any of the second file matches meet
> the long 'if', if they do not i want to place a .f. in the first file. i can
> tell why its not right, i just know it isnt working properly.
> Tom
>
> USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
> USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
> USE c:\testdata\checkhdr IN 0 SHARED
> SELECT lopdptfl
> GO TOP
> scan
> REPLACE ALL ACTIVE WITH .T.
> bkey = (PADL(city,6,"0")+PADL(dept,3,"0"))
> SELECT fulopmas
> DO WHILE .T.
> IF EOF()
> EXIT
> ENDIF
> IF (PADL(fulopmas.agency,6,"0")+PADL(fulopmas.dpt,3,"0")=(bkey) and
> (fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
> OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND
> !INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
> AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR
> (fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
> MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND
> YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
> AND fulopmas.currwg <> 0))
> SKIP
> loop
> ENDIF
> replace lopdptfl.ACTIVE with .F.
> ENDDO
> endscan
> *!* select lopdptfl
> *!* set order to dept
> *!* report form c:\testdata\inactivedpts.frx to printer for
> lopdptfl.active=.F. noconsole
> *!* close data

Bernhard
Tue Dec 06 12:19:07 CST 2005

Hi Tom

> scan
> REPLACE ALL ACTIVE WITH .T.
After this command, the record pointer points to eof() and your scan loop will
not scan your table.

> bkey = (PADL(city,6,"0")+PADL(dept,3,"0"))
since you are at eof(), bkey will never contain a useful value.

Regards
Bernhard Sander

Tom
Tue Dec 06 15:09:02 CST 2005

Bernhard, yep your right, i changed that. But let me ask you this. i changed
it and decided to use the set relation to... in this case i want to find the
one matching record in lopdptfl, then see if any of the matching records in
fulopmas meet my criteria set, then replace .active with a .f., do i still
have to use a bkey= "something"

or like i have i written, it seems like it spinning through every record and
i could make this run faster... ideas?

USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
USE c:\testdata\checkhdr IN 0 SHARED
SELECT lopdptfl
REPLACE ALL ACTIVE WITH .F.
SET RELATION TO (PADL(city,6,"0")+PADL(dept,3,"0")) INTO fulopmas
GO TOP
scan
SELECT fulopmas
DO WHILE
.T.&&(PADL(lopdptfl.city,6,"0")+PADL(lopdptfl.dept,3,"0"))=(PADL(fulopmas.agency,6,"0")+PADL(fulopmas.dpt,3,"0"))
IF EOF()
EXIT
ENDIF
IF ((fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND
!INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR
(fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND
YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
AND fulopmas.currwg <> 0))
SKIP
loop
ENDIF
replace lopdptfl.ACTIVE with .T.
skip
ENDDO
endscan
select lopdptfl
set order to dept
report form c:\testdata\inactivedpts.frx to printer for lopdptfl.active=.F.
noconsole
close data


"Bernhard Sander" wrote:

> Hi Tom
>
> > scan
> > REPLACE ALL ACTIVE WITH .T.
> After this command, the record pointer points to eof() and your scan loop will
> not scan your table.
>
> > bkey = (PADL(city,6,"0")+PADL(dept,3,"0"))
> since you are at eof(), bkey will never contain a useful value.
>
> Regards
> Bernhard Sander
>

Bernhard
Wed Dec 07 04:06:56 CST 2005

Hi Tom

it seems to me, there is a 1:n relation from LOPDPTFL to FULOPMAS. This is the
right place for SET SKIP TO.
With SET SKIP TO FULOPMAS, any SKIP in LOPDPTFL first looks, wether there are
related records in FULOPMAS. If yes, then the record in LOPDPTFL remains the
same and the next record in FULOPMAS is selected. If no, the next record in
LOPDPTFL is selected and FULOPMAS positioned accordingly.

Throw away all lines starting with **--

*-------
USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
USE c:\testdata\checkhdr IN 0 SHARED

SELECT lopdptfl
REPLACE ALL ACTIVE WITH .F.
SET RELATION TO (PADL(city,6,"0")+PADL(dept,3,"0")) INTO fulopmas

** this line coordinates all your "inner loop" stuff
** so that a single outer loop is enough
SET SKIP TO fulopmas

SCAN ALL && same as : GO TOP + SCAN
**-- SELECT fulopmas
**-- DO WHILE .T.
**-- IF EOF()
**-- EXIT
**-- ENDIF
IF ((fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND
!INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR
(fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND
YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
AND fulopmas.currwg <> 0))
**-- SKIP
LOOP
ENDIF
REPLACE lopdptfl.active WITH .T.
**-- SKIP
**--ENDDO
ENDSCAN
select lopdptfl
set order to dept
report form c:\testdata\inactivedpts.frx to printer for lopdptfl.active=.F.
noconsole
close data
*----------

In your big IF you refer to CHECKHDR, but you don't go to a certain record in
this table. Of course, at USE CHECKHDR, the first record is automatically the
current record, if this is what you want...

##############
the only action that happens in the loop: if the big IF is NOT true, then
replace lopdptfl.active with .T.
This can be written shorter:
*----------
USE c:\testdata\lopdptfl IN 0 SHARED ORDER TAG agdept2 ALIAS lopdptfl
USE c:\testdata\fulopmas IN 0 SHARED ORDER TAG agdept2 ALIAS fulopmas
USE c:\testdata\checkhdr IN 0 SHARED

SELECT lopdptfl
REPLACE ALL ACTIVE WITH .F.
SET RELATION TO (PADL(city,6,"0")+PADL(dept,3,"0")) INTO fulopmas

SET SKIP TO fulopmas

SCAN ALL FOR NOT (;
((fulopmas.iactcode<>"T" AND fulopmas.iactcode<>"D");
OR (fulopmas.iactcode="T") AND (fulopmas.termcd < 60 AND ;
!INLIST(fulopmas.termcd,30,31,50,20,51)) OR ((fulopmas.termcd >= 60;
AND fulopmas.termcd<>98) AND fulopmas.RETWAGES <> 0) OR ;
(fulopmas.termcd=98) OR (fulopmas.termcd=30 AND;
MONTH(fulopmas.dtref)=MONTH(checkhdr.procdate) AND ;
YEAR(fulopmas.dtref)=YEAR(checkhdr.procdate);
AND fulopmas.currwg <> 0)) ;
)
REPLACE NEXT 1 lopdptfl.active WITH .T.
ENDSCAN

set order to dept
report form c:\testdata\inactivedpts.frx to printer for lopdptfl.active=.F.
noconsole
close data
*----------

SCAN [condition]
REPLACE NEXT 1 ...
ENDSCAN
could also be written in a single REPLACE statement:
REPLACE ALL [condition] ...
but the loop is more suitable for multi user environment

Regards
Bernhard Sander

Andrew
Wed Dec 07 05:03:01 CST 2005

"Tom" <Tom@discussions.microsoft.com> wrote in message
news:BFD246D1-37EA-4D27-8A88-E205EB2BD5F1@microsoft.com...
>i am trying to match one record from the first file to one in the second.
> when a match i found, i need to see if any of the second file matches meet
> the long 'if', if they do not i want to place a .f. in the first file. i
> can
> tell why its not right, i just know it isnt working properly.

Your first opening bracket matches with the closing one here
"fulopmas.RETWAGES <> 0)"
I have a feeling that is not right, you can ease the parenthesis matching
with a coloured syntax text editor (if VFP doesn't do this then my
recommendation is SciTE: http://www.scintilla.org/SciTE.html )

--
HTH
Andrew Howell