1

I am trying to find a suitable code that lets me to update a numerical column (namely, "RevOdrNo") of a sorted query table (not a separate table) with autonumbers (e.g. 1,2,3,4,5,6) in ArcGIS 10.3's Field Calculator without using the physical IDs (=not sorted). I sorted this query table by right clicking the heading of the other numerical column (namely, "AX_Co") and clicking "Sort Ascending (or Descending)".

I have had a look at a number of the websites including the following link, but none of them seems to work for the sorted query table because they rely on the 'physical' order of the table rows.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000004s000000.htm (See the section of "Accumulative and sequential calculations")

I have had a thought about using the Sort tool in ArcGIS, but it asks for the output table name, which means that the query table will become a separate table, which I don't want to see.

Does anyone have any ideas of how to do it?

  • 1
    Why don't you sort the table with the sort tool and then replace the original table with your new sorted table, if you wanted to have just one table. – yanes Nov 11 '15 at 15:33
2

If you don't want to have another table outputted from Sort, then I don't believe it will be easy (if possible) with Field Calculator. Your best bet is to use cursors, which have a parameter, sql_clause, for sorting:

tableName = "table"
sortField = "AX_Co"
sortType = "ASC" # use 'DESC' for descending
incrField = "RevOdrNo"
s = 1 # this will be your starting value -- change it to whatever you want

fields = [sortField, incrField]
sql_clause = (None, "ORDER BY {} {}".format(sortField, sortType))

with arcpy.da.UpdateCursor(tableName, fields, sql_clause=sql_clause) as cursor:
    for i,row in enumerate(cursor, s):
        row[1] = i
        cursor.updateRow(row)

enter image description here

  • do you use licecap or some other software for generating .gifs? – Alex Tereshenkov Nov 11 '15 at 18:43
  • 1
    @AlexTereshenkov, I used Snagit to record a video then I pipe that to a bat file that converts it to gif which is similar to what I found on this blog. – Paul Nov 11 '15 at 18:49
  • Am I correct in presuming that this code should be written in the RevOdrNo column's Field Calculator's statement box with 'Show Codeblock' unchecked? If so, I have just done that and the order doesn't look right. Maybe I am following the wrong direction as I haven't used arcpy before. A manual work (as suggested by @yanes) using the Sort tool, Join Table and Update Column in the Field Calculator seems to work, but it would be good to know how to do it in 'cursors' (?). – Chris Nov 12 '15 at 17:53
  • @Chris, this is something you would enter in the Python window or make a python script in a custom toolbox out of. This isn't intended to work in Field Calculator. – Paul Nov 12 '15 at 19:19
  • @Paul, I have just tried that in the Python window and it seems that it just updates the RevOdrNo column based on the 'physical' order' of the table rows? Here's a modified script I have tried to write in the Python window: tableName = "A34_DIR_A" sortField = "AY_Co" sortType = "DESC" incrField = "RevOdrNo" s = 1 fields = [sortField, incrField] sql_clause = (None, "ORDER BY {} {}".format(sortField, sortType)) with arcpy.da.UpdateCursor(tableName, fields, sql_clause=sql_clause) as cursor: for i,row in enumerate(cursor, s): row[1] = i cursor.updateRow(row) – Chris Nov 13 '15 at 10:44

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.