sequence number with a pattern

[rene316]

Hello,

I am in a bind here and could really use some help. I have a file that I need to append a sequence number to. However, it needs to increment in a patter of 7,7,14,7,7,14, etc... ie, 1000, 1007, 1014, 1031, 1038, 1045, 1062. If any knows how, can you please post the code for me or at least point me in the right direction. I would really appreciate it.

thx in advance,
Rene

 

[Gandalf23]

7,7,17 or 7,7,14?

 

[Gandalf23]

Also need more input. Is it a file you are appending to or a table? And are you going to do this in 1 process, or is some user going to add a record once in a while?

 

[rene316]

sorry the sequence jumps 7, 7, then 17 then back to 7. say you start at 1000, the first number in the sequence would be 1000, then 1007(this is the first increment of 7), then it would go to 1014,(this is the second increment of 7), then it would go to 1031,(this is the increment of 17), then it starts over to the increment of 7, hence 1038, then 1045, then 1062, then 1069, then 1086, then over again. I can't think of any other way to explain it, so I hope that clarifies it for you. And yes, it will be one process and it is a table.

thx,
Rene

 

[csr]

Well, put another field within the file (call it seq_spot) and keep track of which position you are at in the sequence with that record.

For example.... the 1000 would show seq_spot = 1, the 1007 would show seq_spot = 2, the 1014 would show seq_spot = 3, the 1031 would show seq_spot = 4, the 1038 would show seq_spot = 1, etc ....

Then you could simply check the last record (you have to do that anyway) and noting the seq_spot, you would know by how much to increment that last value to get the next one.


Don
dond@csrinc.com

 

[DSummZZZ]

How about:

STORE 1000 TO nInc
STORE 1 TO nCounter
SCAN
IF nCounter = 3
nInc = nInc + 17
nCounter = 0
ELSE
nInc = nInc + 7
nCounter = nCounter + 1
ENDIF
REPLACE MyTable.SeqNum WITH nInc
ENDSCAN
Dave S.

 

[Gandalf23]

Asume we have a table "sequence" with field "nr"


USE sequence
SELECT sequence
lnCounter = 993

FOR i = 1 TO 100
INSERT INTO number (nr) VALUES (IIF(MOD(RECNO(),3) <> 0, lnCounter + 7,lnCounter+14))
lnCounter = nr
ENDFOR

you can adjust it like you want and add the fields/values you need.

 

[Gandalf23]

Sorry, the &quot;insert into number&quot; must be &quot;insert into sequence&quot;

 

[rene316]

thank you all.

 

[DSummZZZ]

INSERT INTO...

As I understood it, this would be an update not adding new records(?).

Dave S.

 

[nirajj]

INSERT appends a new record to the table

for update the syntax is UPDATE table SET column = value

As per the help :
INSERT – SQL Command
Appends a record to the end of a table that contains the specified field values.

UPDATE - SQL Command
Updates records in a table with new values.

 

[Gandalf23]

You could be right Dave, that Rene wants to update a table instead of appending new records.

using a scan and replace would do it also.

 

[AllanB1]

In a form, you can use this method to replace an existing field with the desired increment. I don't know if you're populating the entire table all at once or doing it one record at a time. Either way, use this method.

Call it from a procedure / method using the following syntax.

Position record pointer on the record you want to update and execute this:
*.....................................
SKIP -1
PREVNUM = SEQNO
SKIP
REPLACE SEQNO WITH THISFORM.INC7717(PREVNUM)
*.....................................
*Place this code in a form method named INC7717.
*.....................................
LPARAMETERS LASTNUM

IF TYPE(&quot;LASTNUM&quot = &quot;C&quot;
LASTNUM = VAL(LASTNUM)
ENDIF

CODE = CHR(7)+CHR(7)+CHR(17)

IF PARAMETERS() = 0
LASTNUM = 1000
ENDIF

IF FILE(&quot;INC7717.MEM&quot
RESTORE FROM INC7717 ADDITIVE
ELSE
SEQ = 1
ENDIF

INC = ASC(SUBSTR(CODE,SEQ))
SEQ = IIF(SEQ = 3,1,SEQ + 1)
SET SAFETY OFF
SAVE TO INC7717 ALL LIKE SEQ

RETURN LASTNUM+INC


Al

 

[rene316]

dave or gandalf,
sorry, I wasn't to clear on it. dave, you are right I am just updating a field in the record called seq, not appending new records. sorry for the confusion.
thx,
Rene