0

I have an ID field that is a text datatype. The IDs in the field have a prefix, and are padded with zeros to always be a specific length. Examples:

  • INSP000098
  • INSP000099
  • INSP000100
  • INSP000101

I would like to use the Field Calculator to generate sequential IDs for new records (records are batch loaded from various sources by the thousands).

I have a python script that almost does this:

Modified from: Create sequential numbers in a field using Python in the Field Calculator

prefix = "INSP0000"
lastnumber=97

rec=0
def autoIncrement(): 
 global rec 
 pStart = 1  
 pInterval = 1 
 if (rec == 0):  
  rec = 1 + lastnumber
 else:  
  rec += pInterval 
 return prefix+str(rec)

autoIncrement()

Steps:

  1. Manually enter the prefix (with zeros for padding)
  2. Manually enter the last number in the existing IDs
  3. Run the script

Result:

Unfortunately, the tool isn't smart enough to dynamically adjust for the difference in the number of digits between numbers (99 to 100, etc.):

  • INSP000098
  • INSP000099
  • INSP0000100 << Problem: too many zeros/digits
  • INSP0000101

How can I use the field calculator to generate sequential, prefixed IDs that are padded with zeros to a specific length?

I'm not married to Python; VBScript would be an acceptable alternative.

2
rec=97
def withPads():
 global rec
 rec+=1
 return 'INSP%s' %str(rec).zfill(6)
#------------
withPads()

OUTPUT:

enter image description here

  • That's pretty slick. – Wilson Aug 22 '17 at 20:33
  • Feel free to post your own answer, do not amend my one please, because calculator is a pain to work with sometimes. I prefer to keep it short – FelixIP Aug 22 '17 at 20:58
  • Ok. I don't know how to cancel my edit. But I will post my own as requested. Thanks again. – Wilson Aug 22 '17 at 21:00
2

Here's a slight twist on FelixIP's answer. It's a bit more user friendly for non-python people.

The user edits the variables at the top, rather than trying to interpret and edit the code body.

prefix="INSP"
numlength=6
lastnumber=97

rec=lastnumber
def seqID():
 global rec
 rec+=1
 return prefix+'%s' %str(rec).zfill(numlength)

#------------

seqID()
1

Add some if logic at the end to evaluate the rec number length, try:

prefix = "INSP0000"
lastnumber=97
rec=0
def autoIncrement():
 global rec
 pStart = 1
 pInterval = 1
 if (rec == 0):
  rec = 1 + lastnumber
 else:
  rec += pInterval
 if len(str(rec)) == 2:
  return "INSP0000" + str(rec)
 elif len(str(rec)) == 3:
  return "INSP000" + str(rec)
 elif len(str(rec)) == 4:
  return "INSP00" + str(rec)
 else:
  return 'add more elif conditions'
  • Thanks. Is there any way to do this dynamically, without hard coding in multiple IF statements? – Wilson Aug 22 '17 at 20:06
  • That may be hard to do within the field calculator since there is no way to get the total feature count before iterating through all features to help dynamically figure out the max rec length. I would recommend using arcpy for this (get count and update cursor). – artwork21 Aug 22 '17 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.