use of "do while" 3

[jblomme]

I have a problem using the "do while"-command.
In fact, I have a table (magazines.dbf) that consists of 900.000 records, each representing sales figures of a magazine.

There are 800 different magazines in the table.
Each record has a unique (numeric key - magnumber) that serves as a proxy for the name of a magazine (numbers start from 100 through 900).

What I want to do, is to add records from a given magazine to another table (report.dbf), run a foxpro report (retail.prg) and repeat this until the final number of 900 is reached.

I tried this :

cnt=100
do while cnt <= 900
use report
delete all
pack
append from magazines for magnumber = cnt
do retail.prg
cnt = cnt +1
enddo

The report is provided for the records with magnumber=100 but at the line "cnt = cnt+1" I receive the message "variable CNT is not found".

Can you tell me what's wrong ?

 

[JanBucek]

Maybe "release all" or "clear all" or something simillar in RETAIL.PRG?

insert "list memory to file memory.txt" after "do retail.prg" and check the file "memory.txt" if variable CNT exists.

 

[Mike Lewis]

Hi Jblomme,

I agree with Jan that there's probably something in RETAIL.PRG that is clobbering the Cnt variable (or, conceivably, something in the report).

The best solution is to declare the variable as local in both programs:

LOCAL Cnt

In fact, it's good practice always to declare all your variables in that way, unless you have a good reason to do otherwise.

A couple of other points:

- You can make the code a little more concise by using FOR / ENDFOR rather than DO WHILE:

FOR cnt = 100 to 900
....
ENDFOR

Also, do you really need to pack the table each time round the loop? Provided you have SET DELETED ON, the deleted records will be ignored. Packing can take a long time. Or, it might be more efficient to do a ZAP, rather than DELETE ALL and PACK.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

What you're doing seems like a really roundabout way to accomplish your goal. Based on what you say, you've created the report in code rather than using the Report Designer, so the easiest solution (create a cursor in the right order and do a single report) won't work for you.

Nonetheless, it would be easier for you to use a query to put the data for a given magazine into a cursor, which Retail.PRG could then report on:

SELECT * ;
FROM Magazines ;
WHERE MagNumber = m.Cnt ;
INTO CURSOR Report

Put this in the loop in place of your copy and delete code.

Tamar

 

[Olaf Doschke]

you could also make use of the FOR-clause of the REPORT FORM command, eg if all retail.prg does would be to call REPORT FORM with the report-Cursor:

select magazines
for m.cnt=100 to 900
    report form ... for magazines.magnumber = m.cnt
endfor

even better to avoid reports on magazines with no record and/or to be independant on the magnumber range in the future:

select distinct magnumber from magazines order by magnumber;
into cursor curScan nofilter

select curScan
scan
   select magazines
   report form ... for magazines.magnumber = curScan.magnumber
endscan

Bye, Olaf.