Populate a field as sequential number using autoIncrement in field calculator

[thomasdesalle]

Hi all,
I need to populate a field with sequencial numbers.
I could go the easy way and input in the field calculator: FIELD = 1+ [FID]

But I want my data to be first sorted so what i would like would be more something like
1) Sorting my data
2) FIELD = ROW
And so once sorted the FID is not relevant to the row number...


I've read things online and it seems that autoIncrement is my solution but I can't seems to have it working.
Here is the code I got from the help:

Expression:
autoIncrement()

Expression Type:
PYTHON_9.3

Code Block:
rec=0
def autoIncrement():
global rec
pStart = 1 #adjust start value, if req'd
pInterval = 1 #adjust interval value, if req'd
if (rec == 0):
rec = pStart
else:
rec = rec + pInterval
return rec

Here how I input it:


Here's my error message:


My field i'm trying to populate is called label and is a short integer.

Any ideas?

[RichardFairhurst]

The Label field should be a Long Integer field for good measure.  If you auto increment more than 32,767 records the Field Calculator will throw an error.  There is really no reason to limit yourself to that few number of records in any data set as far as I am concerned.  What if this data was still being tracked in the year 3,000 with a complete history back to your database?  Will 32,767 records really be enough?

Unlike in the movies, in programming "Failure is always an option" as you clearly can see.  In fact with computer programming failure is always the expected result at some point in the program unless every single step is done correctly and no unanticipated conditions arise.

[RichardFairhurst]

I recommend that you only use the Field Calculator option to maintain sequential numbers.  The script option as written will overwrite every sequential number in the Label field every time it is run.  It does not use any layer that is in any map or any selection in a layer to limit the records that it overwrites.  That is because you are supplying a path to the feature class on disk, not a connection to the layer in your open map.  A few other lines of code would be needed to connect to the layer in your currently open map and any selection it may contain.

The Field Calculator option is designed to work on a layer in your currently open map and will respect any selection of records you may have chosen.  This allows you to avoid overwriting previously assigned sequential numbers and continue the numbering only for records that have no Label value.  So even if you get both to work, I would only go with the Field Calculator after the initial sequential number assignment for any continuing maintenance.

[thomasdesalle]

Paste the code exactly as written below within the code tags.Pre-Logic Codeblock:

rec=0
def autoIncrement():
 global rec
 pStart = 1 #adjust start value, if req'd 
 pInterval = 1 #adjust interval value, if req'd
 if (rec == 0): 
  rec = pStart 
 else: 
  rec = rec + pInterval 
 return rec

First of all, thank you so much to spend so much time pointing me to the right direction.
So from what I read on your post I got some positive output and some negative one too.

So the positive:
The python code with the right indentation works!
Which I don't understand why he was wrong in the first place because I copied it straight from the help of arcgis...

The negative:
It works and create a string but not what I want it create the same than
label=[FID]+1
and not a
label=[raw ofter sorting]+1
i wanted

see the result

[thomasdesalle]

The Label field should be a Long Integer field for good measure.

Thanks for the tip, in this case the label is for a group of 340 features which shouldn't expend to any crazy number that why I went for short integer. But you're probably right that as a default long integer is probably better.

Finally as I had only 340 entry I filled the label field manually. I had already lost too much time trying to do this automatically.

[thomasdesalle]

I recommend that you only use the Field Calculator option to maintain sequential numbers.  The script option as written will overwrite every sequential number in the Label field every time it is run.  It does not use any layer that is in any map or any selection in a layer to limit the records that it overwrites.  That is because you are supplying a path to the feature class on disk, not a connection to the layer in your open map.  A few other lines of code would be needed to connect to the layer in your currently open map and any selection it may contain.

The Field Calculator option is designed to work on a layer in your currently open map and will respect any selection of records you may have chosen.  This allows you to avoid overwriting previously assigned sequential numbers and continue the numbering only for records that have no Label value.  So even if you get both to work, I would only go with the Field Calculator after the initial sequential number assignment for any continuing maintenance.

I haven't had time to try the code option further. Thanks again for your help. I don't use GIS very much at all and this is very instructive.

Cheers.

Thomas

[RichardFairhurst]

The negative:
It works and create a string but not what I want it create the same than
label=[FID]+1
and not a
label=[raw ofter sorting]+1
i wanted

I assume you meant to type:

label = [row after sorting] + 1

That is a limitation of the Field Calculator method.  It operates on the records only in ObjectID order and not based on any layer sorting criteria.  Because ObjectIDs are not necessarily sequential and can skip values due to record deletions, the code is primarily useful for reestablishing an unbroken sequence of numbers.  So while it does not achieve your objective it still has value for what it is designed to do.

To use sorting, the scripting option with the cursor is the only available approach, or you actually have to output the data to a new physically resorted feature class with the Sort tool.  A cursor can apply a sorting order on the data prior to operating on it, but the sort criteria has to be fed directly to the cursor set up as a string.  Record sorting slows the cursor performance significantly and should only be used where it is absolutely necessary.

Nothing I have seen can use the layer sort criteria you set up in ArcMap directly to do any ordered manipulation of data, since the Table View order does not actually exist anywhere but in the Table View.  The Table View sort is primarily an in memory manipulation that has no effect on the actual record order read by any tool.  Manually entering data using that sorted view is the only option that can take advantage of a Table View sort.

With only 340 records a manual approach is practical and what I would have recormmended in the first place.  Or copy the data to Excel, do the sort and numbering and then bring that back into ArcMap and join it to the table to transfer the numbers into the table with the Field Calculator. 

Using the field calculator to calculate the original ObjectID values of your source into a new long field, then using the Sort tool, and finally Joining the sorted output back to the original data source on the field you calculated and using the Field Calculator to transfer the new sorted ObjectIDs of the sorted output back to the source is the geoproccessing approach to this problem that works.

If you ever need to to do the same type of operation with a significant data set (over 25,000 records), then the scripting approach will be worth every minute you spend on it and you won't regret any part of the learning curve needed to master Python or .Net.

[MikeFroese]

AM following this post and am having sequential numbering problems.

Simply I need to take about 5000 features all labeled XTLF_XTLF0001000000* and make them read

XTLF_XTLF00010000001.........XTLF_XTLF00010004934

Thanks in advance

[RichardFairhurst]

The code below should work to sequence the numbers in the format you want as long as it is for a single prefix and the sequence does not restart for different groups of records:

Parser:  Python

Show Codeblock: checked

Pre-Logic Codeblock:

rec=0 

def autoIncrement(): 

global rec 

pStart = 10000001 # adjust start value, if req'd 

pInterval = 1 # adjust interval value, if req'd 

if (rec == 0): 

  rec = pStart 

else: 

  rec = rec + pInterval 

return "XTLF_XTLF{011d}".format(rec)

Expression (Label): autoIncrement()