1

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)

enter image description here

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)
  • Could you provide an example of what is occurring and what you hope to accomplish? – Aaron May 16 '14 at 16:50
  • I added a screenshot but all the details are there Aaron. – yams May 16 '14 at 17:03
  • Basically the script should only insert a FacilityID if the FacilityID is null. – yams May 16 '14 at 17:04
  • 1
    What if you just selected the NULL fields with SQL query and then ran your python code to autoincrement? Or alternatively, if you know what the last number is you could select them via SQL query and have them start from last number + increment via python. – GISKid May 16 '14 at 17:06
  • Well the script does pickup the last known number. Are you talking about doing a query inside the Field Calculator? – yams May 16 '14 at 17:07
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.

  • It's the use of the with statement (rather than the use of arcpy.da per se) that negates the need for cleaning up. – PolyGeo May 17 '14 at 9:52
  • Thanks @polygeo, so it's similar to the way that C# does it. It's ok for a singleton but when you start using lots of things (search cursor, update cursor, file...) the file goes far to the right and becomes a little unreadable. Can it still be done the old way? – Michael Stimson May 17 '14 at 9:58
  • Yes - this may be hard to read without formatting but I just tried it successfully: cursor = arcpy.da.SearchCursor(r"C:\polygeo\Natural Earth\ne_10m_admin_0_countries.shp",["name","pop_est"]) for row in cursor: print row[0] + " - " + row[1] – PolyGeo May 18 '14 at 0:23
  • I tried it and it still overwrote all the values. – yams May 19 '14 at 16:19
  • Can you update your question with your code as it currently stands, just in case there's a transposition error. That should work, I've done it this way successfully many times. There is more code that's run as this is a procedure. How about commenting out the row[0] =... and ucur.updateRow and change print row to print row[0] + " to COMLI1" + str(format(max_value,'04d')) then review the output before anything is changed.. even better if you put it into a text file and see if it's this routine or somewhere else. – Michael Stimson May 19 '14 at 22:33
0

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)
  • @GISKid Doesn't the Sort Tool (found in Data Management Tools - General - Sort) do the same thing? It makes a new Shapefile/FC to do it, so maybe if one wants to keep OBJID the same your method works best. – user23715 May 16 '14 at 22:10
  • 1
    @user23715 Similar, but with this tool you can set the increment as well as where to "start" from – GISKid May 20 '14 at 15:56
  • I ran it and I get a Bad File Descriptor on line 21 – yams May 20 '14 at 18:56
  • Ok I removed the print statements and got Unbound Local Error local variablle 'row' referenced before assignment. – yams May 20 '14 at 19:06
  • I have comments on #21, what do you have? Did you try my other less pythonic solution? – GISKid May 20 '14 at 19:31

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.