Consecutive Number of Fields- Use Exisitng Data in a Field and Add a Number to it

[Kevin_Sieger]

I have a large table (102K+ rows) with a field that I need to add a number to. I can do what I need to do the long way by creating tables in Excel, importing into Access, running queries, and then importing into Arc to update the fields, but there has to be an easier way to do this and I'm hoping it can all be done using ArcMap.

Here???s the situation: The points which are now junctions never existed until I created the geometric network. I copied and pasted them on a mass scale from the network junction layer to the appropriate layer on the map and filled in a few attributes that are the same for all selected points, one of them being the basin designation.

There are 46 basins that start with one or two alpha characters; within those basins are the points I created where the Facility_ID (this is for use in Cityworks) has only the basin letter(s); what I need to do is add a number to that basin designation and the numbering has to be consecutive.

For instance, in basin 'A' I have 13,276 points that need a number added to the ???A???, but I won't be starting with number 1, I'll be starting with number 0652 resulting in A0652, A0653, A0654, etc; each basin varies in the amount of  points where the Facility_ID needs updating and all have different starting numbers. I know having a leading zero on the number might or will cause problems but I need to keep the leading zeros until I get to 9999 and obviously this will be a text field.

Any help would be appreciated.

[RichardFairhurst]

You did not say which version and licence level of ArcGIS you are using or if this is a one time process or something that needs to be run multiple times.  However, I normally approach this type or problem in 3 steps.

The first step is to get the data in order.  If it is already ordered on your basiin field the objectID should provide your base sequencing values.  If it is not in order and you are using ArcGIS 10 with an ArcInfo licence there is a built-in permanent sort records tool that will sort on your basin field.  If you have ArcGIS 9.3 you can use the script and tool at this post:

http://forums.arcgis.com/threads/12385-(Again-Sorry)-Serial-number-for-the-records-in-the-attribute-...

You also could use ET Geowizard from http://www.ian-ko.com/. It has a permanent sort tool that is included in the subset of tools that are available for free with unlimited use.

Based on your descriptionm, you may first want to develop a query where you get just the values that have no numeric portion to do your sort on.  The query might work if it was something like:  NOT("BASIN" Like '_0%' or "BASIN Like '__0%')

Once the data is ordered, I perform a Summary on the basin field and get the Min ObjectID value.  Getting the Min ObjectID value may involve creating a field and calculating the OID values into it, since for some reason the Summary field does not like using the ObjectID field itself as a summary input.  (It may be possible just to type in the ObjectID field name and avoid that extra step, but I have not tried it).  ONce you have the summary, you could also add another field to the summary table create your unique offset values for each basin value at this point.  If this is a one time set up just do it manually.

Now you perform a join and calculate the sequenctial field.  Using pseudo VBA (for 9.3) the value you want will be:

[OrigTable.Basin] & format([OrigTable.ObjectID] - [SumTable.MinObjectID] + [SumTable.Offset], "0000")

If you have ArcGIS 10, do not use Python on the join calculation as it will run 20 to 50 times slower than VB Script.  The VB Script expression would be:

basinNum = [OrigTable.ObjectID] - [SumTable.MinObjectID] + [SumTable.Offset]
If len(basinNum) < 4 Then
  [OrigTable.Basin] & left("0000", 4 - len(basinNum)) & basinNum
Else
  [OrigTable.Basin] & basinNum
End If

Potentially this could also be done in ModelBuilder, but the unique starting number would have to be hard coded into a calculation.  I hope this helps.