Scripting a Sequential ID based on highest value

2320
16
Jump to solution
08-25-2016 01:56 PM
DavidBuehler
Occasional Contributor III

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)
0 Kudos
1 Solution

Accepted Solutions
AllisonMuise1
Occasional Contributor III

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)

View solution in original post

16 Replies
DanPatterson_Retired
MVP Emeritus

I checked your code formatting in the syntax highlighter ( follow the ... ), could you recopy and paste with python syntax highlighting to make it easier to read

0 Kudos
DavidBuehler
Occasional Contributor III
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)
0 Kudos
DarrenWiens2
MVP Honored Contributor

I'm not sure where to start with this. You found this somewhere, but are you not sure what it does, or is it giving you an error, or where do you need help? In general, break it down line by line until you either understand what's happening, or ask for help.

0 Kudos
DavidBuehler
Occasional Contributor III

I know what it does.  Basically, it uses a field with a prefix letter that is the default value.  It then goes sorts the objectID and then adds calculates an ID based on the next value.  It works for what it is designed to do.  However, it "starts" over from the deletion point and rewrites going forward.  What I am aiming for is to generate what it outputs, but not have it start over from a deleted value.

0 Kudos
DanPatterson_Retired
MVP Emeritus

so I am guessing that didn't work or did you test it?

0 Kudos
DavidBuehler
Occasional Contributor III

I tested it. It works. I am trying to get it to be "smarter" where it builds on the highest number value rather than what the picture shows.  It rewrites when there is a "gap" in OIDs. I would like to keep it so that the highest number regardless of what was deleted before it is the starting point to add 1 to the count to generate the new Concern ID.

0 Kudos
DavidBuehler
Occasional Contributor III

0 Kudos
DarrenWiens2
MVP Honored Contributor

You can check if the Concern ID is blank and then update with the next available ID, and not update if it already has a Concern ID (but keep track if it's got the highest existing Concern ID).

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

For your sake, I hope you don't have to run this often nor that this dataset is very large. 

If you are going to be implementing this in an enterprise geodatabase, you might want to talk with your DBAs about implementing something more robust using sequences and triggers.  That said, doing so gets complicated with versioned data, so your DBA has to have some skills to not butcher it.

Although I don't work with Attribute Assistant much, it might be worth exploring its functionality because it can do quite a bit of handy stuff with managing attributes, or so I have heard.

0 Kudos