Foxpro interleave program 1

[Darknyss]

Is there a freeware or shareware program, or a way to do this with simple command lines that can interleave foxpro records? Say I had a table with 500 records numbered sequentially from 1-500, and I wanted to interleave them 2up. When the program finished the sequence numbers should be 1,251,2,252,3,253...etc. Any help would appreciated, thanks! If you guys can, keep it at basic level stuff, I'm still really new to this.

 

[SnyAc]

use mytable
select * from mytable where .F. into table newtable
select mytable
lnRecs = reccount()
scan while recno() <= lnRecs / 2
  lnrecno = recno()
  scatter memvar
  insert into newtable from memvar
  skip lnRecs/2
  scatter memvar
  insert into newtable from memvar
  goto lnRecno
endscan
close tables all

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[Olaf Doschke]

Are you really trying to accomplish a physical rearrangement of the data?

You could also use an index to sort the data that way. Even thugh you'd need to redo the index every time the reccount() changes, or you get weird sorting, but in general it could be done this way:

Create Cursor curTest (interleave Int)
For lnCount = 1 To 500
   Insert Into curTest Values (lnCount)
Endfor lnCount
Index On Iif(Recno()<=Reccount()/2,Recno()-.5,Recno()-Reccount()/2) Tag xsort

Bye, Olaf.

 

[MrDataGuy]

Need to be carefull of input tables that have an Odd number of records, e.g. 3,5,7. The code suggestions may leave off the last record. Here is my suggestions for how to do it:

CLOSE DATABASES ALL 

&&-- Creating a new table to hold the new information
SELECT * FROM InputData WHERE .F. INTO TABLE NewTable

SELECT InputData	&& Table being read from

&&Figure out the half way point of the number of records
m.Half_Table_Size = INT( RECCOUNT( [InputData] ) / 2)

&& Going to loop 1/2 of the records 
FOR I = 1 TO m.Half_Table_Size
  &&-- Loading a lower Half record
    SELECT InputData  from
    GOTO I	&& Goto the record of interest
    SCATTER NAME oData MEMO && Make a copy of the data
		
    SELECT NewTable	
    APPEND Blank           && Prepare a new blank record
    GATHER NAME oData Memo && Data for new blank record

  &&-- Loading a Upper Half Record	
    SELECT InputData		
    GOTO I + m.Half_Table_Size
    SCATTER NAME oData MEMO 
		
    SELECT NewTable	
    APPEND Blank		
    GATHER NAME oData Memo
		
ENDFOR &&-- Repeat for more records

&&-- Important!!!!
&&-- In case the input table has an odd number of records
&&-- One final record needs to be added
IF RECCOUNT( [InputData] ) <> RECCOUNT( [NewTable] ) 
  SELECT InputData
  GO bottom
  SCATTER NAME oData MEMO 
		
  SELECT NewTable			
  APPEND Blank			
  GATHER NAME oData Memo	
Endif

 

[Darknyss]

Let's see, I'd have to change the mytable to whatever table I was using, right? And then the newtable is the output where the data would be interleaved? What would I need to change to make it divide the table into fourths instead of halves, or even eighths? I tried just changing the numerics in the program to 4 or 8 respectively, but there's probably something I'm missing in the math it's doing. I know the first part would read

"do this while the record you're working on is less than the total number of records divided by half"

So it would make sense to change that one to four or eight to make it divide the table into smaller amounts. I guess where I'm getting into trouble is where it's doing

Skip lnRecs/2

It seems like it would make sense to change that to, say 4, if you were dividing the table into fourths, but it would also have to skip the records divisible by two? I dunno, all this math is making my head hurt lol. Thank you a whole bunch for helping me out though, especially with such a quick response.

 

[MrDataGuy]

all this math is making my head hurt lol"
Well welcome to programming <wink>! Here is my solution for any # of interleaves that you might need. As before the final odd/orphaned records have to be taken care of.

	CLOSE DATABASES ALL 
	&&-- Creating a new table to hold the new information
	SELECT * FROM Test_Data WHERE .F. INTO Cursor Table

	SELECT Test_Data	&& Table being read from
	
	=Interleave_Data( 8 )

RETURN

&&------------------------------
PROCEDURE Interleave_Data( pDivisions )

	LOCAL I, J, Split_Size
	m.Split_Size = INT( RECCOUNT( [Test_Data] ) / pDivisions )
	FOR m.I = 1 TO RECCOUNT( [Test_Data]) / m.pDivisions
		FOR m.J = 0 TO m.pDivisions - 1
			Copy_Record( m.I + ( m.j * m.Split_Size ) )
		ENDFOR
	ENDFOR

	&&-- Copying any final 'orphan' records
	SELECT Test_Data
	FOR m.I = RECCOUNT( [NewTable] ) + 1 TO RECCOUNT( [Test_Data] ) 
		Copy_Record( m.I )
	Endfor
		
ENDPROC &&--  Interleave_Data
&&------------------------------
PROCEDURE Copy_Record( pRecord_of_Interest ) 
	&&-- Loading a lower Half record
		SELECT Test_Data			&& Table that has the data being read from
		GOTO pRecord_of_Interest	&& Goto the record of interest
		SCATTER NAME oData MEMO 	&& Make a copy of the data
		
		SELECT NewTable		&& Select the table about to added into
		APPEND Blank			&& Prepare a new blank record
		GATHER NAME oData Memo	&& Copy the Copy into the new blank record
				
EndProc
&&------------------------------

 

[MrDataGuy]

Mistake in the code above!


Change:
SELECT * FROM Test_Data WHERE .F. INTO Cursor Table


To:
SELECT * FROM InputData WHERE .F. INTO TABLE NewTable


Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Olaf Doschke]

What do you actually want to achieve by this? Shuffling data?

Bye, Olaf.

 

[Darknyss]

Yes, I was trying to shuffle the data around for printing purposes. We have this ancient dos program that does this, but it also does all kinds of fun stuff as a result of being as old as it is, like truncating characters at 60, and stripping the field names when it outputs data. We're trying to print in an exact order so when we print 2up, or 4up or w/e, if it was in normal sequential order we would have to manually resort the entire mailing by hand, which would take....well, a long time, I'm sure. Especially if you're dealing with a 10000 or more piece mailing.

 

[Darknyss]

Oh, I forgot, I wanted to thank you all for your very helpful responses!

 

[MrDataGuy]

Darknyss,
if one or more of the posts were helpful assign a 'star' to it. e.g look at the bottom of the post and click on the "Thank <Poster> for this valuable post" This helps others in finding the best answers etc. to your questions.

"but it also does all kinds of fun stuff as a result of being as old as it is, like truncating characters at 60, and stripping the field names when it outputs data"

I suspect that this program has been doing these things since it was written, nothing to do with the fact that it is old. Some of my clients have 15+ year old programs written by me that work fine with no changes on Windows 7, when they started on Windows 3.1 (Ok there was that issue with CPUs being too fast to fox years ago but I am not counting that. )

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Olaf Doschke]

This works portioning 512 records in 1/8th (64 records) so results in 1,65,129,...,449,2,66,130,...

Create Cursor curTest (interleave Int)
For lnCount = 1 To 512
   Insert Into curTest Values (lnCount)
Endfor lnCount
Index On Str(Evl(Recno()%64,64))+Str(Recno()) Tag xsort

For the initial problem you'd need to replace 512 with 500 and 64 with 250.

Bye, Olaf.

 

[Darknyss]

Well, yes, you are correct. It isn't a direct result of the program being old, it was coded that way, and likely performed adequately for the time it was programmed. The input data, and output necessities have changed quite a bit since then. I'm guessing the post office's "smart" barcode system didn't exist at the time this code was written...it's probably older than me So I guess it was more that the programmers that worked here before me didn't have a crystal ball that worked well enough to anticipate future needs. C'est la vie.

 

[Olaf Doschke]

Actually printer drivers support this kind of printing of pages and duplex, but anyway, I've not seen this for DOS.

Bye, Olaf.

 

[MrDataGuy]

I was playing with this idea: What about creating a cursor (or Table) that has a 'special order' column. Fill in this order column, index or sort on it and there is the information you need. I was trying to figure out how to populate the special column with a Replace all command. But like you said the Math makes my head hurt, well really I need to get back to my paying work. Anyhow here is my (not working) work in progress. Maybe someone can use it as a hint for the correct solution.

**>>>>>> Warning this code DOES NOT produce the wanted solution <<<<
m.Divisions = 2

SELECT *, 000000 as Special_order FROM Test_Data INTO CURSOR qNew readwrite
*replace ALL Special_order;
      WITH MOD(Recno()-1, m.Divisions ) * ( INT(RECCOUNT()/m.Divisions ) );
      + INT(RECNO ()/m.Divisions)

replace ALL Special_order WITH MOD(Recno(), INT(RECCOUNT()/m.Divisions ) )

GO Top
BROWSE normal

INDEX on Special_Order TAG Temp
SET ORDER to Temp

GO Top
BROWSE normal

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Darknyss]

Oh, I can quite well understand. I probably need to get on some backed up work myself, but I thank you heartily for taking time off your day to help with this. Don't work too hard, eh? And Olaf, thank you as well. I'm not sure if our imaging program and the guy who works with it either A. is possible within the boundries of the program he works with, or B. not really sure he would know how to do it if it was possible. I'm not that familiar at all with the program or how it operates myself, as I've never had to work in that dept. It's some later version of Quark is about all I know.

 

[MrDataGuy]

Darknyss,
you are welcome, and when possible pay it foward as I am trying to do for the wonderful help I have received over the years.

( I rememmber when I had to dig the BITs out of the hard frozen ground and carry them back to my computer in a leaking bucket leaving bloody foot prints in the snow from my bear feet, them were the days! )

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Darknyss]

Oh boy, that's rich MrDataGuy. And I thought my grandma had it hard walking uphill both ways for two miles in the snow every day to school! She just didn't know how easy she had it

 

[Olaf Doschke]

Well Evl() is a function introduced in VFP9, but you may as well use

Index On Str((Recno()-1)%64)+Str(Recno()) Tag xsort
or
Index On Str(Mod(Recno()-1,64))+Str(Recno()) Tag xsort

This is actually even simpler and better.

Once data is indexed this way and that index tag is active, it 's sorted and get's printed in that order.

64 is reccount()/divisions

Bye, Olaf.

 

[Darknyss]

That is really simple in form, and it seems to work very well as far as I can tell, Olaf. Two questions, at least one of which is gonna sound really newbish.

1. If you have a remainder left when you do reccount()/divisions, do you round up, or down?

2. How does sorting strings work exactly? I've done that once or twice on a numerical field that was being displayed as characters at the time, and it seems like it sorts on the number of characters in the field, regardless of numerical superiority. Say for instance that it would sort 263480 as being less than 26408. I'm not exactly sure what it's doing when it does this...I only know from it that it's a really bad idea to try to sort numbers as characters. Not disparaging your solution at all, because like I said, yours works perfectly. I just want to know *how* it does what it does.