VFP 6 -Splitting indexed table into parts 4

[webmonger]

I need to split an indexed table up into a variable number of parts. That is - to produced the same report for n number of items at a time to get around the creation of an enormous MSWord document.

I have a routine using a filter and record numbers.

SET FILTER TO (recno() >= mywordsplitstart AND recno() < (mywordsplitamount+mywordsplitstart))

I now remember that the indexed table has record numbers in any order based on the filter key so I get the records out of the expected index order.

Can anyone suggest a way to create a filter for say 25 records at a time irrespective of any filter that may be applied?

Thanks

Bryan

 

[baltman]

Something like this...

Brian

lnCounter=0

SCAN

if lnCounter=0 or lnCounter>25
 lnCounter=0
 *new document code
else
 lnCounter=lnCounter+1
 *continue document code
endif

ENDSCAN

 

[darrellblackhawk]

nReportCount = ceiling(reccount()/25)

go top

for i = 1 to nReportCount
copy next 25 to DocRept
select DocRept
Do DocOut

select MainTable
next

Darrell

 

[webmonger]

Darrell

I thought this was the answer but I find no records in the new table - I can't see the NEXT command in the definition of COPY TO.

Do you have any further thoughts

Bryan

 

[webmonger]

Darrell

Sorry - I do see NEXT under scope - but I still get an empty table?

Bryan

 

[alvechurchdata]

If you select your data into a new cursor in the order required then you can use your existing code to split it.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[darrellblackhawk]

Don't know why you are getting an empty table...

Maybe it's due to some filter you've set.

Darrell

 

[Olaf Doschke]

I now remember that the indexed table has record numbers in any order based on the filter key so I get the records out of the expected index order.

No, a Recno() belongs to a certain record and the records are not numbered in order, if you set an index, so setting the filter you set is not the way to go. Instead set the appropriate order, then you may use COPY TO with the Scope NEXT 25 records for each single part of 25 records until there are no more records. If you Select ... into cursor CurName Order ..., then you'll get a new cursor where records are of course renumbered in that order, but still using SET FILTER is not the best way and that intermediate cursor is not needed, if you already have the index you need on the main table.

some demo code:

cd getenv("TEMP")
set safety off
erase ("tabnames.*")
set safety on
create table tabNames free (cFirstname c(10), cLastname c(10))
select tabnames
index on cLastname+cFirstname tag tName
set order to tName

* fill in some random data
local nCount
for nCount = 1 to 100
  insert into tabNames values (sys(2015),sys(2015))
endfor nCount
 
* here it comes: Splitting:
go top
local lcTempPartAlias
lcTempPartAlias= sys(2015)
do while !eof("tabNames")
   select tabNames
   copy to (lcTempPartAlias) next 25
   Skip 1
   use (lcTempPartalias) In 0
   if reccount(lcTempPartAlias)>0
      select (lcTempPartAlias)
      browse
   EndIf
   use in Select(lcTempPartAlias)
   set safety off
   erase (lcTempPartAlias+".*")
   set safety on
enddo

use in Select("tabnames")

Now you should get a browse window with the first 25 records, if you close that get the next 25 records etc. Instead of using a browse you may start a report on that table. Notice, that I put an additional skip 1 after copy to ... next 25. Without that, the last record that was copied will be the first record of the next part, too. Then you'd get 5 pages with 4 records on the fifth page, although it's 100 records and should be exactly 4 pages.

Bye, Olaf.

 

[Olaf Doschke]

When setting the loop to genereate 101 records, you'll get an error at Skip 1, so please correct this to:

...
copy to (lcTempPartAlias) next 25
If !Eof("tabNames")
   Skip 1
EndIf 
...

Bye, Olaf.

 

[webmonger]

All working thankyou