Scripting a Sequential ID based on highest value

DavidBuehler · ‎08-25-2016

I what i am trying to accomplish is a script that generates a concern ID that is based on a the last highest number that will run as a scheduled task. An example would be C100, C101, (C102, C103, and C104 were deleted), C105 where the next ID would be C106 and not overwrite C105 as C102 and continue to C103.

I found main part of this code somewhere, and I like how it concatenates a letter along with a number. I am up for adding fields that store a number and then concatenates the final ID later, or whatever it takes so long as it quick. Example would be: ConcernID, PrefixCode, LastNumber (all fields used in script). PrefixCode would be a default value of C and the LastNumber field would hold the number portion, and combine them to form the ConcernID.

This will be eventually used with an enterprise geodatabase feature class. Any suggestions would be greatly appreciated.

import arcpy

# Create update cursor for feature class
fc = r'C:\Requests\Test\Seq.gdb\GeoReportingManagement\GeoReporting'
fields = "ConcernPrefix; Point; ConcernID"
sort_fields="OBJECTID"

# Workspace is the connection to the database where fc is stored - DMB
workspace = r'C:\Requests\Test\Seq.gdb'

# Starting an Edit Session - DMB
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, True)
edit.startOperation()

rows = arcpy.UpdateCursor(fc, fields=fields, sort_fields=sort_fields)
previousPoint = None
i = 0
for row in rows:
 munic = row.getValue('ConcernPrefix')
 # point = row.getValue('Point')
 if not previousPoint:
 i += 1
 elif previousPoint <> point:
 i = 1
 else:
 i += 1

 row.setValue('ConcernID','-'.join([str(munic),str(i)]))
# previousPoint = point
 rows.updateRow(row)

# Closing the Edit Session - DMB
edit.stopOperation()
edit.stopEditing(True)

DavidBuehler · ‎08-26-2016

Joshua,

The idea is to use the Crowdsource reporter app to allow people to place features, and using GeoEvent Processor fire off emails with a concernID within a reasonable amount of time after someone submits something. So short answer, more frequently than not, like every 10 minutes or so, and the dataset will grow over time. From my understanding GeoEvent does not (correct me if I am wrong) does not handle versioned data very well. So the data is not versioned.

I researched a bit on sequences and triggers, and that seems like the way to go, and I also found where the original script came from. The link to the script is: arcgis desktop - How to auto increment a field in a feature class? - Geographic Information Systems .... I also found this link where it shows pretty much what I want done: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server.

What I cannot get my head wrap my head around are couple things:

- the object ID is useless to attach to or join on because of way ESRI sets up their "locking" of object IDs for editing.

- Basically, each entry has to be has to be on its own merit when creating that concern ID.

- Attribute assistant isn't that just a desktop tool?

So I am kind of at loss here on how to proceed. Thoughts?

AllisonMuise1 · ‎08-29-2016

buehlerd‌

Attribute Assistant is a desktop tool, and you are correct that it won't work for what you are trying to do here. What might work, though, is mimicking parts of its setup.

AA keeps track of the last used value of a sequence in a row in a standalone table. You could set up something similar so that, instead of needing to find the highest value and instead of using the OBJECTIDs, the script assigns sequential IDs starting with the last saved value in the table, and ending when there are no more records needing identifiers. The final step would be to write the last value used to the row in the table corresponding to that incident type so that the script knows what value to start with next time it runs.

-Allison

AllisonMuise1 · ‎08-29-2016

David Buehler‌

I played around with this this afternoon and came up with the following that illustrates what I was trying to describe above. This could help organize a whole series of incrementing identifiers for an app like Reporter.

import arcpy

# workspace containing fc and table
workspace = r''

# Feature class needing id values in field Report_ID
fc = r''

# table that manages ID values. 3 fields:
#   - RPTTYPE - text field - an index for the type of report getting an id assigned so multiple ids can be managed in one table
#   - NEXTVAL - long int - the next value to use in the id sequence
#   - INTERVAL - long int - the number of values to jump between id values
tbl = r''

# value to search for in the RPTTYPE field in the table to find the right sequence
inc_type = 'Parks'

# get next id value and interval from table
with arcpy.da.SearchCursor(tbl, ['NEXTVAL', 'INTERVAL'], where_clause = """RPTTYPE = '{}'""".format(inc_type)) as tblrows:
    for row in tblrows:
        sequence_value = row[0]
        interval_value = row[1]

# Start edit session
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, True)
edit.startOperation()

# find and update all features that need ids
with arcpy.da.UpdateCursor(fc, 'Report_ID', where_clause="""Report_ID is null""") as fcrows:

    for row in fcrows:

        # Calculate a new id value from a string and the current id value
        row[0] = "ConcernID-{}".format(sequence_value)
        #row[0] = "ConcernID-{:04d}".format(sequence_value) #alt: pad sequence value with 4 zeros

        fcrows.updateRow(row)

        # increment the sequence value by the specified interval
        sequence_value += interval_value

# update the table values for next time
with arcpy.da.UpdateCursor(tbl, 'NEXTVAL', where_clause="""RPTTYPE = '{}'""".format(inc_type)) as tblrows:
    for row in tblrows:
        row[0] = sequence_value
        tblrows.updateRow(row)

# Close edit session
edit.stopOperation()
edit.stopEditing(True)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

DavidBuehler · ‎08-31-2016

That works. Thanks Allison.

JoeBorgione · ‎08-25-2016

As bixb0012‌ suggests, the Attribute Assistant has the Generate_ID method that will append a string of your choice while sequencially increasing a numeric component.

That should just about do it....

DavidBuehler · ‎10-12-2016

Hello All,

I am switching over from the testing environment to the enterprise geodatabase, using the script template from Allison posted above and incorporated a few other items as well. When I run the script against the enterprise geodatabase I get the following error: "workspace already in transaction mode" in line 47. "for row in fcrows:"

I am wondering if the section Cursors and Locking of the following link has something to do with it: Accessing data using cursors—Help | ArcGIS for Desktop and how I would take care of such an issue. SQL 2008 R2 if that matters.

Here is my full code:

#-------------------------------------------------------------------------------
# Name:        module1
# Purpose:
#
# Author:      Dbuehler
#
# Created:     07/09/2016 and Last updated 10/12/2016
# Copyright:   (c) Dbuehler 2016
# Licence:     <your licence>
#-------------------------------------------------------------------------------
import arcpy

#----------------------------------------------------------------------------------------------------
# The First part of this script calculates Concern IDs.  It relies on a table to store the next available ID.  This does away with the need to rely on ObjectIDs which get exponential in the ArcGIS Editing.
# This segement was written by Allison Muise of ESRI
#----------------------------------------------------------------------------------------------------

# workspace containing fc and table
workspace = r'C:\GISScripts\GRSTasksConnection.sde'

# Feature class needing id values in field Report_ID
fc = r'C:\GISScripts\GRSTasksConnection.sde\COMGIS2.GIS.GeoConcerns' #r'C:\ESRITest\GeoReportingSystem\Testing9116\Testing9716.gdb\GeoReportingManagement\GeoReporting'

# table that manages ID values. 3 fields:
#   - RPTTYPE - text field - an index for the type of report getting an id assigned so multiple ids can be managed in one table
#   - NEXTVAL - long int - the next value to use in the id sequence
#   - INTERVAL - long int - the number of values to jump between id values
tbl =  r'C:\GISScripts\GRSTasksConnection.sde\COMGIS2.GIS.GR_IDTable' # "//C:/GISScripts/GRSTasksConnection.sde/COMGIS2.GIS.GR_IDTable"

# value to search for in the RPTTYPE field in the table to find the right sequence
inc_type = 'GeoReporting'

# get next id value and interval from table
with arcpy.da.SearchCursor(tbl, ['NEXTVAL', 'INTERVAL'], where_clause = """RPTTYPE = '{}'""".format(inc_type)) as tblrows:
    for row in tblrows:
        sequence_value = row[0]
        interval_value = row[1]

# Start edit session
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, True)
edit.startOperation()

# find and update all features that need ids
with arcpy.da.UpdateCursor(fc, 'ConcernID', where_clause="""ConcernID is null""") as fcrows:

    for row in fcrows:

        # Calculate a new id value from a string and the current id value
#        row[0] = "C{}".format(sequence_value)
        row[0] = "C16{:04d}".format(sequence_value) #alt: pad sequence value with 4 zeros

        fcrows.updateRow(row)

        # increment the sequence value by the specified interval
        sequence_value += interval_value

# update the table values for next time
with arcpy.da.UpdateCursor(tbl, 'NEXTVAL', where_clause="""RPTTYPE = '{}'""".format(inc_type)) as tblrows:
    for row in tblrows:
        row[0] = sequence_value
        tblrows.updateRow(row)

#----------------------------------------------------------------------------------------------------
# This section calculates the information of several fields.  The goal is to off load as many calculations from GeoEvent Processor and rely on GeoEvent Processor to really just sent emails
# It relies on dictionarys within a dictionary to accomplish field calculations. Watch out as the 4 and 5 fields are out of schema sequence. Darren Wiens - GIS Analyst out of Prince George, BC aided via an example.
#----------------------------------------------------------------------------------------------------

# Set other variables
pContact = "PrimaryContact"
contactInfo = "PrimaryInformation"
pDept = "PrimaryDepartment"
sql = """{} is NULL AND {} IS NULL AND {} IS NULL""".format(pContact, contactInfo, pDept)
options = [[1, 'Damaged or Missing Street Sign', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Fire Hydrant Issue', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [1, 'Icy Intersection or Road', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Pothole', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Public Parking Issue', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [1, 'Public Vegetation Concern', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [1, 'Road Obstruction', 'Street Division', '715-486-2081', 'Street Division',  'GRS_StreetPD'], [1, 'Street Light Issue', 'Marshfield Utilities Electrical', '715-387-1195', 'Marshfield Utilities Electrical', 'GRS_MU_Electric'], [1, 'Street Parking Issue', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [1, 'Traffic Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_TrafficPD'], [1, 'Traffic Signal Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_TrafSignPD'], [1, 'Vision Obstruction', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EVegAndObstruct'], [1, 'Water Bubbling From Street', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [1, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [2, 'Cracked Sidewalks', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Esidewalk'], [2, 'Curb Ramp Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_ECurbRamp'], [2, 'Slope Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Esidewalk'], [2, 'Snow or Ice Covered Sidewalk', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [2, 'Tree or Brush Clearance Issue', 'Engineering', '715-486-2034', 'ngineering', 'GRS_EVegAndObstruct'], [2, 'Uneven Sidewalk', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering'], [2, 'Other', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering'], [5, 'Garbage or Recycling Pickup', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [5, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [4, 'Commercial Signage Concern', 'Development Services', '715-486-2077', 'Development  Services', 'GRS_Zoning'], [4, 'Temporary Signage Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [4, 'Other', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [3, 'Abandoned or Junk Vehicle', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [3, 'Building Maintenance Issue', 'Development Services', '715-486-2077', 'Development Services', 'GRS_BuildService'], [3, 'Fence Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [3, 'Fire Code Violation', 'Fire Department', '715-384-3118', 'Fire Department', 'GRS_Fire'], [3, 'Outdoor Burning', 'Fire Department', '715-384-3118', 'Fire Department', 'GRS_Fire'], [3, 'Land Use Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [3, 'Property Maintenance Concern', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Ordinance'], [3, 'Setback or Property Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [3, 'Weeds or Overgrown Vegetation', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [3, 'Other', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [6, 'Park Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Shelter Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Trail Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Zoo Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [9, 'Electrical Service Issue', 'Marshfield Utilities Electrical', '715-387-1195', 'Marshfield Utilities Electrical', 'GRS_ElectricPD'], [9, 'Water Issue', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [7, 'Dumping into a Ditch', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Dumping into a Sewer', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Dumping into a Stream or Pond', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [8, 'Culvert Replacement', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Ditch Flow Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Ponding Water', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Other', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering']]

# What I am trying to do is update the Null PrimaryContact and PrimaryInformation fields based on a General Concern (subtype number) and a specific concern.
#Example would be General Concern = 6 and Specific Concern = Pool Issue then PrimaryContact = Parks and Recreation and PrimaryInformation = 715-555-5555


with arcpy.da.UpdateCursor(fc, ['GeneralConcern', 'SpecificConcern', 'PrimaryContact', 'PrimaryInformation', 'PrimaryDepartment', 'PrimaryEmailGroup'] , where_clause = sql) as cursor:
    for row in cursor:
        for option in options:
            if option[0] == row[0] and option[1] == row[1]:
                row[2] = option[2]
                row[3] = option[3]
                row[4] = option[4]
                row[5] = option[5]
        if not row[2] and not row[3] and not row[4] and not row[5]: # not 100% sure about this
            print 'No Match'
        cursor.updateRow(row) # update row in table with values held in row variable

#----------------------------------------------------------------------------------------------------
# This Section Looks for Reassigns and updates the primary dept, and their info. It is based on similar coding as above.
#----------------------------------------------------------------------------------------------------

reassignYes = "ReassignedTo"
reassignemail = "ReassignedEmailSent"
sql = """{} IS NOT NULL OR {} = 1""".format(reassignYes, reassignemail)
reassignOptions = [[51, 'Development Services', 'Development Services', '715-486-2077', 'GRS_Zoning', 1], [52, 'Engineering', 'Engineering', '715-486-2034', 'GRS_ETrafficSignal', 1], [53, 'Fire Department', 'Fire Department', '715-384-3118', 'GRS_Fire', 1], [54, 'Marshfield Utilities Electrical', 'Marshfield Utilities Electrical', '715-387-1195', 'GRS_MU_Electric', 1], [55, 'Marshfield Utilities Water', 'Marshfield Utilities Water', '715-387-1195', 'GRS_MU_Water', 1], [56, 'Parks and Recreation', 'Parks and Recreation', '715-384-4642', 'GRS_Parks', 1], [57, 'Police Department', 'Police Department', '715-384-3113', 'GRS_Police', 1], [58, 'Street Division', 'Street Division','715-486-2081', 'GRS_Streets', 1]]


with arcpy.da.UpdateCursor(fc, ['ReassignedTo', 'PrimaryDepartment', 'PrimaryContact', 'PrimaryInformation',"PrimaryEmailGroup","ReassignedEmailSent"] , where_clause = sql) as reassign:
    for row in reassign:
        for option in reassignOptions:
            if option[0] == row[0]:
                row[1] = option[1]
                row[2] = option[2]
                row[3] = option[3]
                row[4] = option[4]
                row[5] = option[5]
        if not row[2] and not row[3] and not row[4] and not row[5]: # not 100% sure about this
            print 'No Match'
        reassign.updateRow(row) # update row in table with values held in row variable

# Close edit session
edit.stopOperation()
edit.stopEditing(True)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

DavidBuehler · ‎10-12-2016

I figured it out. Line 41

edit.startEditing(False, True)edit.startEditing(False, True)

# Should Be
edit.startEditing(False, False)‍‍‍‍