Sequential number based on two other fields?

[ionarawilson1]

I need to create an ID that includes the year, county and sequential number. The sequential number or the county makes the id different

So
if year 2013, county 001  - the sequential number is 001  and unique id is 2013-001-001
if year 2013, county 001 - the sequential number is 002 and unique id is 2013-001-002
If year 2014, county 001 - sequential number is 001 and unique id is 2014-001-001
If year 2014, county 005 - sequential number is 001 and unique id is 2014-005-001

I already have the code to calculate the sequential number but now it is just calculating the numbers from 001, 002, 003 for the fields when sorted by the sequential number field. Is there a way to create the sequential number based on year and county ? Thank you

   arcpy.SelectLayerByAttribute_management("Stewardship", "CLEAR_SELECTION")       filter = 'NOT SequenceNumber IS NULL' #filter for non-Null values     cur1 = arcpy.UpdateCursor("Stewardship", filter, "", "", "SequenceNumber A")   # Iterate through rows and get highest ID values     high_id = 0     for row1 in cur1:         if high_id < row1.SequenceNumber:             high_id = row1.SequenceNumber       filter = 'SequenceNumber IS NULL' #filter for Null values     cur2 = arcpy.UpdateCursor("Stewardship", filter)      # Iterate through rows and update values     i = high_id     for row2 in cur2:         i += 1         row2.SequenceNumber = (i)         cur2.updateRow(row2)  

[RhettZufelt]

Yes, would use a searchcursor to populate the lists.

Somthing like this:

yearList = []
countyList = []

with da.SearchCursor(inFc,["years","counties"]) as cursor:
      for row in cursor:
         year= str(row[0])
         county = str(row[1])
         yearList.append(year)
         countyList.append(county)
del cursor

Of course, this will have the same issue, as if you delete one row (if it was the highest sequence number), the next time the maxValue searchcursor is run on it, the "old" number would be the "new" maxValue again.

Also, in the code I provide previously, I should not have put the maxValue SearchCursor within the loop.  It is hardcoded to the FC and fields, so no need to run that process each time.  would move that just above the loop.

R_

[RhettZufelt]

I tried to combine what I had before (sorting by the highest value) with what Stacy has written. But I am getting an error at the end:  

Traceback Info: 
File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 234, in <module> 
row.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3) 
Error Info: 
<type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function. 



Does anybody know why? Thank you!!! 

   filter = 'NOT SequenceNumber IS NULL' #filter for non-Null values
    cur1 = arcpy.UpdateCursor("Stewardship", filter, "", "", "SequenceNumber A")

# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict < row1.PlanID:
            sequenceDict = row1.PlanID


    filter = 'SequenceNumber IS NULL' #filter for Null values
    cur2 = arcpy.UpdateCursor("Stewardship", filter)

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    for row2 in cur2:
        key = str(row2.FFY) + '-' + str(row2.County) # use year/county as the key
        if (key in sequenceDict2):
         sequenceDict2[key] += 1
        else:
            sequenceDict[key] = 1
    row.PlanID = key + '-' + str((sequenceDict2[key]).zfill(3))
    cur2.updateRow(row2)

Does the change in red above get rid of the error?

R_

[ionarawilson1]

No, the problem was with something else. Can you please look at this code and tell me why I get the following error message, but I dont't get an error when I run Stacy's original code?


Traceback Info:
  File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 260, in <module>
    sequenceDict2[key] = 1

Error Info:
     <type 'exceptions.TypeError'>: 'unicode' object does not support item assignment

# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict < row1.PlanID:
            arcpy.AddMessage("TEST")
            sequenceDict = row1.PlanID
            arcpy.AddMessage(sequenceDict)

    filter = 'SequenceNumber IS NULL' #filter for Null values
    cur2 = arcpy.UpdateCursor("Stewardship", filter)

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    arcpy.AddMessage(sequenceDict2)
    for row2 in cur2:
        key = str(row2.FFY) + '-' + str(row2.County) # use year/county as the key
        if (key in sequenceDict2):

         sequenceDict2[key] += 1
        else:

         sequenceDict2[key] = 1
    row2.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)
    cur2.updateRow(row2)

[RhettZufelt]

No, the problem was with something else. Can you please look at this code and tell me why I get the following error message, but I dont't get an error when I run Stacy's original code? 


Traceback Info: 
File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 260, in <module> 
sequenceDict2[key] = 1 

Error Info: 
<type 'exceptions.TypeError'>: 'unicode' object does not support item assignment 

# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict[key] < row1.PlanID:
            arcpy.AddMessage("TEST")
            sequenceDict[key] = row1.PlanID
            arcpy.AddMessage(sequenceDict)

    filter = 'SequenceNumber IS NULL' #filter for Null values
    cur2 = arcpy.UpdateCursor("Stewardship", filter)

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    arcpy.AddMessage(sequenceDict2)
    for row2 in cur2:
        key = str(row2.FFY) + '-' + str(row2.County) # use year/county as the key
        if (key in sequenceDict2):

         sequenceDict2[key] += 1
        else:

         sequenceDict2[key] = 1
    row2.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)
    cur2.updateRow(row2)

I suspect it is related to trying to compare an entire dictionary to a value, then set the entire dict to a value.

Need to have key:value pair for the dict. So, sequenceDict = row1.PlanID would now overwite the setting of sequenceDict as a dictionary and now assigns it as a variable with row1.PlanID as the value.

Then later, you set sequenceDict2 equal to a variable (not a dict) and try to use it as a dict ( sequenceDict2[key] = 1).

R_

[ionarawilson1]

Thank you Rhett. I need to go back to my original code and try to figure out from there how to create the unique value by county and year. Thanks again!

[StacyRendall1]

Stacy, there is only one issue. If for example I delete one 3266-045-009 and create another polygon and run the tool again, the value 3266-045-010 will become 3266-045-009 and the one I just created will become 3266-045-010. Is there a way to make the uniqueID permanent? Thanks again!!!

The script just creates the UniqueID based on the set passed to it. There is no simple way to make it permanent, apart from never running the script again...

The only ways that come to mind for making it permanent would be by using some kind of version control...? I don't really know. It ought to be possible to do something by writing/reading information to/from a text file, but that would be a lot of work to program.

Have you looked into Geodatabase versioning tools? I don't know if they will help you, but they might...

[StacyRendall1]

Basically, it would look at the highest value for that unique id and create the new PlanID from there.

What is PlanID?

When you delete a feature its information disappears. You no longer are able to use it as the basis for a calculation.

I think you need to step back from the problem for a minute and try to figure out exactly what you are trying to do. This is a normal part of programming complex things. Get a piece of paper and write/draw all the information you have, then think about how you can get the output you require. For example, the answer I posted earlier was basically:

increment counter whenever a value of column1+column2 appears
write colum1+column2+counter to column3

This is called pseudo-code. If you can write a pseudo-code that works on paper (or, say, in Excel) then you can program it. Without doing this process you are stabbing in the dark.

[StacyRendall1]

I tried to combine what I had before (sorting by the highest value) with what Stacy has written. But I am getting an error at the end:

Traceback Info:
  File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 234, in <module>
    row.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)

Error Info:
     <type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function.



Does anybody know why? Thank you!!!

I would guess that PlanID is not a text field...? Everything else looks OK.

[ionarawilson1]

Thank you Stacy, I created a workflow but since I am such a newbie I still need help. Thanks!!!

[ionarawilson1]

Thank you Rhett and Stacy for your great help! I figure out a way to do it and it is working perfectly. If we delete the record, the other records don't get overwritten. I will soon post the solution after I clean up my code a little. I appreciate your help and patience!!!