how to auto-increment numbers in a field 1

[namax]

Hi all. I have a table which has 20 000 plus records and want to insert values from 1-50 to records which I filter which meets certain conditions.For instance,I want the first record to have value 1,next 2,3 up to 50 in the column name "rowno" given certain conditions. I assume a DO WHILE..ENDDO loop can sort that out but I am not really sure how to insert expressions in the loop.

Any help will greatly be appreciated.

 

[JRB-Bldr]

We can offer you suggestions on how to do what you describe, BUT it might be that what you are trying to achieve is not the way to go.

Maybe if you describe for us what you want to accomplish on a more over-view manner, we might have other approaches that could work better for you.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If you mean you have opened a table and set order and set filter, you can then set a rowno field simply in a scan..endscan. First Init a m.ncounter variable to 1 and within the lopop increment it after a REPLACE rowno WITH m.nCounter.

But I agree with jrbbldr, we can just guess here, why you need your rowno field.

Every record has a recno(), that'll most probably not be from 1 to 50, if you set an order and a filter. But introducing your own rowno would just introduce a temporary row numbering, if records are inserted sorting somewhere in between by means of the sort index, the row nmbering of course will get invalid.

And also, you can use an index for sorting on any field or Expression you index on.

Bye, Olaf.

 

[Mike Lewis]

I agree with the others. Your question is not completely clear. If you explain your objectives in a little more detail, I'm sure we can help.

For now, I'll assume that you know that there are exactly 50 records that you want to number, that these are scattered around the table, and that you have some criterion for identifying them. If that's the case, your code will look something like this:

lnCount = 1
SELECT MyTable
SCAN FOR <my criterion>
  REPLACE MyField WITH lnCount
  lnCount = lnCount + 1
ENDSCAN

where "<my criterion>" stands for the condition on which you want to select the records for numbering.

If my assumptions are not correct, or if the above code doesn't meet your needs, please clarify what you are trying to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike has put my text into code, differing in using the FOR clause and setting no order. One thing still is prerequisite: The table must be open, so beforehand USE MyTable, if it's not open already.

Instead of SCAN FOR, you can also SCAN without any clause, when doing a SET FILTER TO <my criterion>, eg this would look this way:

LOCAL lnCount
USE MyTable IN 0
SELECT MyTable
SET ORDER TO TAG <tagname>
SET FILTER TO <my condition>
lnCount = 1
SCAN
  REPLACE rowno WITH lnCount IN MyTable
  lnCount = lnCount + 1
ENDSCAN

Using the FOR clause of SCAN has the advantage you won't need to reset the FILTER, the condition is just checked during the scan loop. But to scan records in a certain order to number them in that order you need an index. Another possible way is to generate the rowno field in a query, especially if the table has no rowno field yet or you don't want that to be permanent:

SELECT MyTable.*, Cast(0 as I) as rowno FROM MyTable INTO CURSOR curSortedData WHERE <my condition> ORDER BY <my sort expression> READWRITE

Now you can set the rowno field in an UPDATE curSortedData SET rowno=RECNO(), but indeed the data in the cursor will be in sort order and have RECNO() from 1 to N, it's a seperate new temporary table (that's what a cursor really is) with just the queried data in it in the order given by ORDER BY. So there would be no need to introduce the rowno field to temporarily number a certain filtered result, a rowno in that sense simply is the RECNO() of a query result. So instead of the added computed field, simply use RECNO("curSortedData") as the rowno of the current record. In a grid with that cursor as recordsource you may set a column's controlsource to (RECNO()) to display that row number, no need to add this as a field to the cursor or table.

Bye, Olaf.