Concatenating Fields in ArcMap dynamically?

Question

I added a field to an address layer in ArcMap 10 to concatenate separate address fields. I used the field calculator and a VB Script to accomplish this. So, for example, if the separate fields are "10" "Main" and "Street", then the new field would be "10 Main Street".

The problem is that the concatenated field needs to update dynamically. So if the user changes "Main" in the database to "East Main" then that field will update in ArcMap. However, the concatenated field will not update to "10 East Main Street" unless I rerun the Field Calculator script.

I need a way for this new field to always reflect the current data, but still do the concatenation. Does anyone have any suggestions?

Answer 1

I couldn't find a way to do it on the ArcGIS side of things, but you could easily create a computed column to do this on the database side.

For example, on SqlServer I would perform the following:

alter table TABLENAME add COLUMNNAME as FIELD1 + ' ' + FIELD2 +' ' + FIELD3

Then, any time someone queries the layer and requests COLUMNNAME, they'll get whatever values are currently in FIELD1, FIELD2, and FIELD3, concatenated.

UPDATE: One big caveat, though. I just noticed an ESRI article that asks the question "Can I use Microsoft SQL Server computed column with ArcSDE?" Apparently, if you're on SDE 8.3, 9.0, or 9.1, you can't compress the database if you're using a calculated field. I don't know if this applies to later versions, though.

Answer 2

I do not know of any built-in support to do this, but if are a programmer, ArcObjects API has a few extensibility points for such scenarios. The most flexible option are class extensions. For those who are familiar with ArcObjects, the implementation will be fairly straightfoward. The downside is that it's available only for geodatabases (i.e. not for shapefiles) and everyone opening the dataset with a class extension will need to have the component installed/registered on his machine.

As an alternative, it might also be solved with an editor extension. The updates will only be done in ArcMap, not when the data is edited in another way (e.g. in ArcToolbox or on ArcGIS Server). The upside is that it will work for all data source types (shapefiles included), can be installed as an add-in so no admin privileges are needed. Also, when opening the data on another machine, no additional installation of any components is required. It is merely an ArcMap's editor extension.

Answer 3

Forgive me, but why is it an advantage to have the field concatenated at all, rather than separate? Seems like if it, for example, is for labeling purposes for a large scale parcel map, you can have that sourced from the separate fields on the fly?

Answer 4

Thanks to everyone for your answers, but ultimately I decided to go in a different direction.

I created a view in my database that includes columns for the concatenated address fields and the primary key.

In ArcMap, I set up a non-spatial join between the Address layer and the view. This will reflect changes to the data instantly.

--Seth