insert record

[gfrlaser]

I have a table containing 75000 records.
the field is a numeric field with values already populated.
What I want to do is...

go top
do while not eof()
skip 49
insert 0000000 && this value into the new record
enddo

I want to insert a new record at that point, every 49 records. I get errors relating to buffers.
What is the way to accomplish this?

Thanks.

 

[Dan Freeman]

The command you want is INSERT BLANK BEFORE, but I wouldn't do it. With 75k records it will be glacially slow, and on every insert it has to copy and re-write everything from that point to eof().

It is HIGHLY unusual to be so reliant on exact physical location of specific records. If you explain a little more detail about what you're actually doing perhaps someone will be able to suggest a better approach.

 

[gfrlaser]

I have a database of 75000 records. They contain only numbers. the first number is 7050000. We inkjet these numbers onto a piece using inkjet machine and have to package them in fiftys. I want to throw in a dummy number to show where the break is at every 49. I would like to insert a record that has '0000000' or '9999999' every 49 records so that the operator can easily scoop up the pile at the break and give to another operator to shrink wrap them. Make sense?

Will

 

[gfrlaser]

By the way, if it takes time to process, thats not a problem. I always have lots of lead time on this.

 

[Olaf Doschke]

Hmm, are those random numbers? Are they sequential? I'd say it's much easier to generate output even without such a table, if numbers aresequntial. And if not, you could select ranges of 49 records, then print a feed or such a number.

Even if you insist on inserting this into rows like Dan said you'd copy the rest of the table everytime you insert a row, and thus in average would write half the table every 49 rows of the 75000, which means ca. 1530 times, so this approach is lie rewriting a byte amount the size of the whole table 765 times. I'm sure it would be about 765 times faster to rewrite that table once like this:

use theTable
Select * from theTable ito cursor curCopy Where .F. ReadWrite
Select theTable
Scan
insert into curCopy values (theTable.field)
if Recno("theTable")%49=0
   insert into curCopy values ('0000000')
endif
Endscan

Bye, Olaf.

 

[gfrlaser]

Yes, they are sequential and usually start with a number like 7050000 for example. You are right, rebuilding or creating a table and inserting as it builds is only reasonable solution. There are several ways to accomplish this no doubt but I will of course give your code a try.

Many thanks!

 

[Olaf Doschke]

If numbers are sequential, creating a cursor is even easier and faster like this:

Local lnCount, lnStart, lnNumberOfRows
lnStart = 7050000
lnNumberOfRows = 75000
Create Cursor curNumbers (cNumber C(8))
For lnCount=lnStart TO lnStart+lnNumberOfRows-1
  Insert Into curNumbers Values (Transform(lnCount,"@R 99999999"))
  If lnCount%49=0
     Insert Into curNumbers Values (Transform(0,"@R 99999999"))
  Endif
Endfor

Or you print directly, which demands a simplistic printer but still can work with a inkjet/laser printer (GDI), if you use the Generic Text Only printer driver.

Local lnCount, lnStart, lnNumberOfRows
lnStart = 7050000
lnNumberOfRows = 75000
Set Printer to "Generic / Text Only"
For lnCount=lnStart TO lnStart+lnNumberOfRows-1
  ??? Transform(lnCount,"@R 99999999")
  If lnCount%49=0
     ??? Transform(0,"@R 99999999")
  Endif
Endfor

Bye, Olaf.

 

[gfrlaser]

That will work perfect Olaf! I have to create the database for printing at a later time on a special ink jet machine for addressing envelopes, so its not going to be at runtime. My only question is how do I make this throw in the value at every 49 records? Your code put them in at intervals of 24.
Record# CNUMBER
1 7050000
2 7050001
3 7050002
4 7050003
5 7050004
6 7050005
7 7050006
8 7050007
9 7050008
10 7050009
11 7050010
12 7050011
13 7050012
14 7050013
15 7050014
16 7050015
17 7050016
18 7050017
19 7050018
20 7050019
21 7050020
22 7050021
23 7050022
24 0
25 7050023
26 7050024
27 7050025

Your code clearly says @49

Again, many thanks

 

[Olaf Doschke]

Yes, my code clearly says 49, but it depends on the Recno() and I assumed there are no deleted rows. If you SET DELETED OFF you might see more numbers in the original table. If that's the case do it with a counter instead of Recno(), that way you're safe:

Local lnCounter
Use theTable
Select * from theTable ito cursor curCopy Where .F. ReadWrite
Select theTable
lnCounter = 0
Scan
   lnCounter=lnCounter+1
   Insert into curCopy values (theTable.field)
   If lnCounter%49=0
      Insert Into curCopy Values ('0000000')
   Endif
Endscan

Bye, Olaf.

 

[gfrlaser]

Set deleted is off.

All I am doing is copying your code only into a new prg.
I am still not sure what to change to get this to work ever 49 records. Sorry to be a pain. At record 24 it inserts the new record.
Record# CNUMBER
1 7050000
2 7050001
3 7050002
4 7050003
5 7050004
6 7050005
7 7050006
8 7050007
9 7050008
10 7050009
11 7050010
12 7050011
13 7050012
14 7050013
15 7050014
16 7050015
17 7050016
18 7050017
19 7050018
20 7050019
21 7050020
22 7050021
23 7050022
24 0
25 7050023

 

[Olaf Doschke]

mod 49 surely works. I can't say what, but you must be making something wrong, or the 0 comes from the table.

plus you should have deleted ON, if you only want to copy the records that are not deleted.

And you're surely not copying my code 1:1, as you insert a 0, not a 0000000.

Please double check, what you're doing and where what data comes from. This for example will introduce a second column telling you if the value was copied or generated:

Local lnCounter
Use theTable
Select *, space(10) as csource from theTable into cursor curCopy Where .F. ReadWrite
Select theTable
lnCounter = 0
Scan
   lnCounter=lnCounter+1
   Insert Into curCopy values (theTable.field,'copied')
   If lnCounter%49=0
      Insert Into curCopy Values ('0000000','generated')
   Endif
Endscan

Also, surely your table is not named "theTable". So perhaps simply post your code here, and I'll see what makes the difference.

Bye, Olaf.

 

[Olaf Doschke]

I see, 7050022%49 is 0, you should NOT apply %49 on the sequential number you already have, for it to work it must be applied to a SEPERATE counter starting at 1!!!

In my code theTable.field is NOT lnCounter, there is a seperate counter variable. Please adjust this and it will work.

Bye, Olaf.

 

[gfrlaser]

Here is what I just did. I created a new empty folder c:\olaf

set defa to c:\olaf
modi comm olaf
Paste in the following and run prg
do olaf


Local lnCount, lnStart, lnNumberOfRows
lnStart = 7050000
lnNumberOfRows = 75000
Create Cursor curNumbers (cNumber C(8))
For lnCount=lnStart TO lnStart+lnNumberOfRows-1
Insert Into curNumbers Values (Transform(lnCount,"@R 99999999"))
If lnCount%86=0
Insert Into curNumbers Values (Transform(0,"@R 99999999"))
Endif
Endfor



This created exactly what I wanted, table of 75000 records with sequential numbers except instead of record 51 containing a '0' record 24 contains a 0, ( a 0 is ok because I only need to isolate the record whether a 0 or a 999999) but I need it every 49 records. The next break is Record 111 which contains the next 0.

I need to figure out:
record 1 = 7050000
---
record 51 = 7050049
record 52 0
record 53 = 7050059
record 54 = 7050060
---
record 101 = 705099
record 102 = 0
record 103 = 705100

and so on

Thanks for your patience.

 

[Olaf Doschke]

okay, I see, my error.

To insert a record every 49 records it must stay 49, what's wrong is working on the counter, we need a counter starting at 1, so do it this way:

Local lnCount, lnStart, lnNumberOfRows
lnStart = 7050000
lnNumberOfRows = 75000
Create Cursor curNumbers (cNumber C(8))
For lnCount=1 TO lnNumberOfRows
  Insert Into curNumbers Values (Transform(lnCount+lnStart-1,"@R 99999999"))
  If lnCount%49=0
     Insert Into curNumbers Values (Transform(0,"@R 99999999"))
  Endif
Endfor

Bye, Olaf.

 

[Olaf Doschke]

and use "@RL 99999999" in the transform, if you want leding zeros.

Bye, Olaf.

 

[Olaf Doschke]

Ah, and it seems you don't want a 0 every 49 but every 50 records. So make it %50. The 0 or 7050000 counts too, 7050049 is already the 50th row, not the 49th, gfrlaser.

Bye, Olaf.

 

[gfrlaser]

I am learning some new stuff, thank you for that.

What I did was adjust like this

If lnCount%50=0

does the trick!

Thanks again.

 

[gfrlaser]

One final question if someone may. I am seriously trying to understand the % operator in your code. ?50 % 10 = 0 conversly, ?36 % 10 = 6 ? I cannot find laymans terms.

when I change this number for instance to 10, the results are not every 10 records with a 0.

I am not a math genius but, I can see how this could help me in future snippets, just cant seem to grasp how it works.

 

[Olaf Doschke]

% operator is the mod or modulus operator. a%b is calculating the remainder (modulus) obtained by dividing a by b (in a whole-number division). You find it in the help by searching % operator.

You're not seeing every 10th record changing to 0 if you change the code to %10, because we're adding a record every time the condition is fullfilled, therefore it gives 10 normal records, then an 11th 00000000 record.

But in fact it's 0 for every a which is a multiple of b, since then there is no ramainder or the remeinder of a whole-number division then is 0.

If you for example want to display progress of an operation and know the final count, you can do counter%100=0 checks to display the processing of every 100th row or something like that. It's only important you start counting at 1, because 0%N will be 0 already and you want it to be 0 at the Nth iteration. You could alternatively start at m*N+1, but that doesn't help much.

In your case I erroneously used the counter for the number sequence to also compute when 49 records were processed. But as that did not start at 1, you were getting the 0 after row 705022 because 7050022%49 is 0. And in fact you wanted a 0 after every 50th record.

You can easily cope without % If you simply use a seperate counter and if it arrives at 50 do whatever you want done and reset that counter to 0, but mod does the trick without resetting the counter, it simply restarts at 0 at that moment, mod always goes from remainder 0 to remainder N-1.

In fact resetting the var could be much faster than calculating mod, so even if you understand you might not need it.

Bye, Olaf.

 

[gfrlaser]

I see. that makes alot of sense. Thanks for the good explaination of the operator and for helping me finally get on with my program.

a star for you.. how do you give stars? LOL