topic Re: Sort Field and then Calculate Sequential Values in ArcGIS Pro Questions

Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Fri, 22 Jun 2018 17:40:37 GMT

I have ZERO experience with ArcPy, Arcade expressions... yadda yadda. I basically limp along until something magically works and I'm a huge fan of Copy/Paste. THAT SAID, I'm dipping my toe into something that I thought would be easy...

I have a bunch of campgrounds, and these are in some parks. I want to give them sequential values based on sorting the PARK_NAME field alphabetically. Using Python 3 in Field Calculator, "Sequential Number" will only assign sequential values based on the Object ID field - it doesn't care how I've sorted it.

How do I tell it to use my sorted field instead of the Object ID? Help!

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Fri, 22 Jun 2018 18:17:48 GMT

/blogs/dan_patterson/2018/02/12/table-tools-a-set-of-tools-for-working-with-tabular-data is a description to the Code sharing site for my set of table tools.

Calculations for text fields may be what you are looking for... but a better example of your desired output might help

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Fri, 22 Jun 2018 19:25:27 GMT

Alrighty in this table I've run the Sequential python 3 straight from the calculate field options and this is sorted ascending OID. You can see that the sequential values were assigned by the OID. I don't want this:

In this next screen shot, I've ordered park name ascending by alphabet. I would prefer that the CampgroundID would then be "001, 002, 003", but it's not...

Does this help explain what I'm looking for? Just downloaded Table Tools!!

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Sat, 23 Jun 2018 01:00:56 GMT

let me know if it works or you have any issues... I never got many applicable test cases … it was more an exercise in coding

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Thu, 15 Nov 2018 17:16:01 GMT

Hey Dan,

So I'm circling back around to this item. I've got table tools and I ran Text Fields and keep getting this error:

Traceback (most recent call last):
File "c:\program files\arcgis\pro\Resources\ArcToolbox\toolboxes\Scripts\sequential_funcs_txt.py", line 199, in <module>
in_tbl, in_arr, in_fld, out_fld, del_fld, func, xtend = _tool()
File "c:\program files\arcgis\pro\Resources\ArcToolbox\toolboxes\Scripts\sequential_funcs_txt.py", line 185, in _tool
in_arr = tbl_2_np_array(in_tbl, flds)
NameError: name 'tbl_2_np_array' is not defined
Failed to execute (TxtFieldCalculations).

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Thu, 15 Nov 2018 18:11:16 GMT

like sequential number text fields?

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Thu, 15 Nov 2018 18:16:15 GMT

Yes! Exactly. I keep trying to run this - even trying different tables - and it is giving me the above error.

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Thu, 15 Nov 2018 18:33:31 GMT

I will check the version online... had some issues with things not being updated. I have attached a zip... try it

unzip into a folder and run from there.

don't separate anything and make sure the destination folder has no spaces (i don't tolerate them)

if this doesn't work, zip and email a copy of the table in a gdb (remove fluff and anonymize if needed) and I will have a look at it.

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Thu, 15 Nov 2018 19:05:00 GMT

Ok, interesting! So I took the same layer and threw it into the toolbox from your zip file. This is a layer in an Enterprise Geodatabase that I have full editing privileges to. It said that it failed because it didn't want to work outside of an edit session (I'm working in ArcGIS Pro 2.2.4)... so I exported out the feature layer to the project geodatabase and ran the tool and it worked fine. Hmmm.

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Thu, 15 Nov 2018 19:10:15 GMT

Work local. Enterprise is for Star Trek. You can get a full refund if you aren't satisfied

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Thu, 15 Nov 2018 19:18:51 GMT

Lulz. Not an option! I have a bunch of feature layers to iterate through and give unique IDs to. With the Enterprise geodatabase, this happens OFTEN with scripts - my coworker suspects that it's failing because it's wanting to know WHICH version to edit. Built in tools never seem to have this issue.

Re: Sort Field and then Calculate Sequential Values

DanPatterson_Retired — Thu, 15 Nov 2018 19:38:51 GMT

probably the case, I don't work in or on the Enterprise, so there is only 1 version (except for backups) and 1 editor. That is what the expensive software is expected to handle. I couldn't even test in all environments and configurations... so whatever gets written in one night goes out the door

Re: Sort Field and then Calculate Sequential Values

deleted-user-jxpbpbjlZInj — Thu, 15 Nov 2018 19:42:45 GMT

Understand! Bummertown. Thank you for sticking it out and helping, though.

Re: Sort Field and then Calculate Sequential Values

JoshuaBixby — Sun, 12 Dec 2021 06:34:12 GMT

Does the following work for you when the table is in your EGDB?

from itertools import count, groupby
from operator import itemgetter

tbl = # path to table
case_fields = ["PARK_NAME", "OJBECTID"]
increment_field = ["CampgroundID"]
sql_orderby = "ORDER BY {}".format(", ".join(case_fields))

with arcpy.da.UpdateCursor(
   tbl,
   case_fields + increment_field,
   sql_clause=(None, sql_orderby)
) as cursor:
    counter = count()
    case_func = itemgetter(*(cursor.fields.index(fld) for fld in case_fields))
    for key, group in groupby(cursor, case_func):
          for row in group:
              cursor.updateRow(list(key) + ["{:0>3}".format(next(counter))])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Sort Field and then Calculate Sequential Values

ZulfadliSalleh — Sun, 16 Jul 2023 10:06:07 GMT

Thank you, the tool has solved the issue of the task I'm working on.

Re: Sort Field and then Calculate Sequential Values

DenizTuran — Tue, 23 Apr 2024 06:45:41 GMT

I had the same issue and finally found the script to create sequential values NOT based on ObjectID but related to any other defined column here: https://mediaspace.esri.com/media/t/1_1fkt56w1