Autoincrementing Python Script is over writing all fields versus just fields that are null

Question

Using ArcMap and the Field Calculator I have a python script that should autoincrement the FacilityID field if it is null. It works the first time through but the second run causes it to overwrite all of the fields versus just retreiving the last value that was used and only inserting the id to only the null fields. Can this be done in the field calculator?

Here is my Python Script:

import arcpy
nullvalues = ["", " ", None, 0]
fac_id_values = []
def autoIncrement():
 # Loop through and get Max value
 fc= "C://GIS//Nambe_Data//nambedatabase//Nambe_ExistingInfrastructureSurvey_v1x0.gdb//CommunicationInfrastructure//cTelcomLine"
 with arcpy.da.SearchCursor(fc, ['FacilityID']) as cur:
     fac_id_values = [int(row[0][-4:]) for row in cur if row[0] not in nullvalues]

 try:
     max_value = max(fac_id_values)
 except:
     # Make an allowance for fac_id_values to be empty
     max_value = 0
 # Loop through and Calculate new values
 with arcpy.da.UpdateCursor(fc, ['FacilityID']) as ucur:
     for row in ucur:
      if row[0] in nullvalues:
          max_value += 1
          row[0] = "COMLI1" + str(format(max_value,'04d'))
          print row
          ucur.updateRow(row)

If you look at the first entry for FacilityID it should be COMML11001 but its COMML110346. The script should not overwrite the FacilityID's that aren't null.

EDIT:

import arcpy
#nullvalues = ["", " ", None, 0] #not sure about none in a list lookup
#The where clause to subset just the records to be calculated
WhereClause = "FacilityID IS NULL or FaclityID in (\"\",\" \",\"0\")"
NotWhereClause = "FacilityID IS NOT NULL or FaclityID not in (\"\",\" \",\"0\")"
fac_id_values = []
def autoIncrement():
 # Loop through and get Max value
 fc= "C://GIS//Nambe_Data//nambedatabase//Nambe_ExistingInfrastructureSurvey_v1x0.gdb//CommunicationInfrastructure//cTelcomLine"
 with arcpy.da.SearchCursor(fc, ['FacilityID'],NotWhereClause) as cur:
     fac_id_values = [int(row[0][-4:]) for row in cur if row[0] not in nullvalues]
     #What's happening here? I can't tell.. is something missing??
 try:
         max_value = max(fac_id_values)
 except:
     # Make an allowance for fac_id_values to be empty
     max_value = 0
 # Loop through and Calculate new values
 # Use the where clause to just get the rows you want
 with arcpy.da.UpdateCursor(fc, ['FacilityID'], WhereClause) as ucur:
     for row in ucur:
      #if row[0] in nullvalues or row[0] == None: #That's how I'd do it, but it can be skipped
          max_value += 1
          row[0] = "COMLI1" + str(format(max_value,'04d'))
          print row
          ucur.updateRow(row)

Ok I ran the script again today and this is the error I got.

Messages
Executing: CalculateField "Communication\Telecommunications Line" FacilityID autoIncrement() PYTHON_9.3 "import arcpy\n#nullvalues = ["", " ", None, 0] #not sure about none in a list lookup\n#The where clause to subset just the records to be calculated\nWhereClause = "FacilityID IS NULL or FaclityID in (\"\",\" \",\"0\")"\nNotWhereClause = "FacilityID IS NOT NULL or FaclityID not in (\"\",\" \",\"0\")"\nfac_id_values = []\ndef autoIncrement():\n # Loop through and get Max value\n fc= "C://GIS//Nambe_Data//nambedatabase//Nambe_ExistingInfrastructureSurvey_v1x0.gdb//CommunicationInfrastructure//cTelcomLine"\n with arcpy.da.SearchCursor(fc, ['FacilityID'],NotWhereClause) as cur:\n     fac_id_values = [int(row[0][-4:]) for row in cur if row[0] not in nullvalues]\n     #What's happening here? I can't tell.. is something missing??\n try:\n     max_value = max(fac_id_values)\n except:\n     # Make an allowance for fac_id_values to be empty\n     max_value = 0\n # Loop through and Calculate new values\n # Use the where clause to just get the rows you want\n with arcpy.da.UpdateCursor(fc, ['FacilityID'], WhereClause) as ucur:\n     for row in ucur:\n      #if row[0] in nullvalues or row[0] == None: #That's how I'd do it, but it can be skipped\n          max_value += 1\n          print row[0] + " to COMLI1" + str(format(max_value,'04d')\n          "
Start Time: Tue May 20 09:44:24 2014
Failed to execute. Parameters are not valid.
ERROR 000989: Python syntax error: Parsing error SyntaxError: invalid syntax (line 25)
Failed to execute (CalculateField).
Failed at Tue May 20 09:44:24 2014 (Elapsed Time: 0.00 seconds)

Answer 1

Yes, that should be possible. Expanding on the previous answer, you can do it with the where clause (optional) see documentation.

Here is your code back again with only a few minor changes, look at my comments.

import arcpy
#nullvalues = ["", " ", None, 0] #not sure about none in a list lookup
#The where clause to subset just the records to be calculated
WhereClause = "FacilityID IS NULL or FaclityID in (\"\",\" \",\"0\")"
NotWhereClause = "FacilityID IS NOT NULL or FaclityID not in (\"\",\" \",\"0\")"
fac_id_values = []
def autoIncrement():
 # Loop through and get Max value
 fc= "C://GIS//Nambe_Data//nambedatabase//Nambe_ExistingInfrastructureSurvey_v1x0.gdb//CommunicationInfrastructure//cTelcomLine"
 with arcpy.da.SearchCursor(fc, ['FacilityID'],NotWhereClause) as cur:
     fac_id_values = [int(row[0][-4:]) for row in cur if row[0] not in nullvalues]
     #What's happening here? I can't tell.. is something missing??
 try:
     max_value = max(fac_id_values)
 except:
     # Make an allowance for fac_id_values to be empty
     max_value = 0
 # Loop through and Calculate new values
 # Use the where clause to just get the rows you want
 with arcpy.da.UpdateCursor(fc, ['FacilityID'], WhereClause) as ucur:
     for row in ucur:
      #if row[0] in nullvalues or row[0] == None: #That's how I'd do it, but it can be skipped
          max_value += 1
          row[0] = "COMLI1" + str(format(max_value,'04d'))
          print row
          ucur.updateRow(row)

Your code should have been skipping the rows with values but just in case I've filtered them out with the where clause, so now only the rows with no value, an empty string or a space will be returned by the cursor.

Answer 2

Here's my workaround that involves little python:

On the top left of your table select the drop down and select "Select by Attribute"

Under method: Create New Selection For the SQL query you want to type:

FacilityID IS NULL

Now when you look at your table selection you should only see entries selected that contain NULL. If you run a modified python script within ArcMap it should only run on the selected features. Myself, I wouldn't even use python because there is a nifty Sort and Calculate Unique Value Python Toolbox that can do it for you (I use it for almost everything), input the highest ID as the start, select your increment and it should output everything you want.

EDIT Pythonically Recommended reading on search cursors I've modified @Michael Miles-Stimson's code as well as used some ideas from this thread

import arcpy

WhereClause =''' "FACILITYID" IS NULL OR "FACILITYID" = '0' OR "FACILITYID" = '' '''
notNull = ''' "FACILITYID" IS NOT NULL'''
 # Loop through and get Max value
fc= r"C:\Your\FeatureClass.shp"

high_id = 0
with arcpy.da.SearchCursor(fc, ['FACILITYID'],notNull) as cursor:
    for row in cursor:
        LastFive = row[0][-5:]   #this selects the last five characters in your field (so the number part)
        if high_id < LastFive: #Compare each row to high_id, if next row is higher than the last it is now high_ID
            high_id = LastFive
    #this selects the last five characters in your field (so the number part)
with arcpy.da.UpdateCursor(fc, ['FacilityID'], WhereClause) as ucur:
    i = int(high_id)
    for row in ucur:
        i+=1
        row[0] = "COMLI1" + str(format(i))
        print row
        ucur.updateRow(row)