topic Re: Populate a field as sequential number using autoIncrement in field calculator in Data Management Questions

Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Wed, 09 Apr 2014 14:29:31 GMT

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?

Re: Populate a field as sequential number using autoIncrement in field calculator

TimWitt — Wed, 09 Apr 2014 14:37:47 GMT

Thomas,

This might help?

Tim

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Wed, 09 Apr 2014 14:55:29 GMT

Thanks this is interresting. Sadly I've never run a python script so I don't know how to do.
Could you explain the basic idea of it?

1) sort my shapefile as fit me
2) open python console (is this the python I can find in my windows start menu?)
3) then copy the following in the console
rows = arcpy.UpdateCursor("your attriubute table")

counter = 1
for row in rows:
row.NEW_ID = counter
rows.updateRow(row)
counter += 1

What should I insted of "your attriubute table"="C:\GIS\Shapefiles\interp\interp_polygon.dbf" ???

Re: Populate a field as sequential number using autoIncrement in field calculator

TimWitt — Wed, 09 Apr 2014 16:07:39 GMT

Thomas,

This should help you find where you need to input this code and here is an example of an update cursor (You can see they first define FC as the path to their table)

Tim

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Wed, 09 Apr 2014 18:37:08 GMT

Your original post has the correct code. However, you did not check the Parser radio button at the top of the Field Calculator dialog to perform a Python calculation. The code won't work as a VB Script calculation, which is what you were doing. So go back and input that code and change the Parser radio button setting to "Python" and it will uniquely number your records.

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Thu, 10 Apr 2014 07:58:06 GMT

Hi, thank you so much for your help already.
I'm not there yet but hopefully it will work soon.
Let's talk python script within field calculator first:

So to summarize:
1)I have a field called LABEL (short integer) within a attribute table of a polygon shapefile
2)I start editing and open the attribute table
3) In field calculator I input that code

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
Which I have tried with or without the indentations

4) I input it the same way as above but ticking the python parser tick box (screengrab was wrong)
5) It doesnt work and got these results:

=> I really cannot see what is wrong in there
=> Can it be my arcgis 10.0 (build 2414) which is doing some problem?
=> Due to IT support I'm not admin on my machine and can't do much changes is this could affect me?

I'll have a read throught the other solution you have offer see if I'm more lucky.
Thanks again for taking time to answer.

Thomas

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Thu, 10 Apr 2014 08:02:04 GMT

Don't know if this is any help but here it is too.

Thanks

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Thu, 10 Apr 2014 08:28:12 GMT

I have also tried the python windows without much success:
Here are a few exemples:

Hope the screen grab can help, sorry they are quiet large displayed on the screen.
Is my python just not working?

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Sat, 11 Dec 2021 19:36:51 GMT

Here is the Field calculator set up specifying each item in the Field Calculator set up dialog in order. Always include the indentations when using Python code exactly as written, indentations are essential to Python. Capitalization can also be important in Python. This code works in ArcGIS 9.3 and up. No special set up or rights is required. Paste the code exactly as written below within the code tags. I just ran the code as shown and it worked without error.

Parser: Python

Show Codeblock: checked

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

Expression (Label): autoIncrement()

The screen shot shows how the Field Calculator set up should look (I have scrolled down in the Codeblock window to show the indentation required in the lower half of the code, which your screenshot did not show.)

The error being reported is a parsing syntax error, indicating that you did not indent the code correctly according to Python requirements. But since you have not pasted your code within code tags to preserve your indentation I cannot tell where the error is located.

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Sat, 11 Dec 2021 19:36:54 GMT

Here is the required indent for the Python window code. Indentations must be included as shown below for Python to work. You also had the wrong field name (NEW_ID) for the 6th line. It should read row.Label = counter.

import arcpy
rows = arcpy.UpdateCursor("C:/gis/yourgeodatabase.gdb/mag_interp_polygon") 

counter = 1
for row in rows:
    row.Label = counter
    rows.updateRow(row)
    counter += 1

Most likely you have input the incorrect path and feature class name into the cursor input of the second line. Your feature class is named "mag_interp_polygon" according to the field calculator error, not Label (the field name, which is not included in the cursor input of line 2). I don't know the actual path or geodatabase name you are really using, but they are almost certainly wrong in what you have written and they are wrong above, since I don't know these essential pieces of information. The path, gdb name (if applicable) and file name or the layer name must be correct for the code not to error with a 99999 error. Screen shot the Layer Properties of the feature class you are updating on the Source Tab page to tell us the actual correct path and feature class name you need to input to the cursor. You may also be able to input just the layer name shown in your map instead of its on disk path and file name.

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Thu, 10 Apr 2014 10:27:06 GMT

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.

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Thu, 10 Apr 2014 13:22:32 GMT

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.

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Sat, 11 Dec 2021 19:36:56 GMT

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

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Fri, 11 Apr 2014 10:46:48 GMT

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.

Re: Populate a field as sequential number using autoIncrement in field calculator

thomasdesalle — Fri, 11 Apr 2014 10:48:38 GMT

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

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Fri, 11 Apr 2014 15:55:35 GMT

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.

Re: Populate a field as sequential number using autoIncrement in field calculator

MikeFroese — Thu, 03 Sep 2015 19:18:24 GMT

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

Re: Populate a field as sequential number using autoIncrement in field calculator

RichardFairhurst — Fri, 04 Sep 2015 16:00:25 GMT

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()