Updating new field from existing field using ArcGIS Desktop?

Question

I need to update my new field/column (Property Type) with either "detached", "semi", or "terrace" depending on an existing column (Property Count):

• where the count = 1 I need to populate Property Type with "detached"
• where the count = 2 I need to populate Property Type with "semi"
• where the count>=3 I need to populate Property Type with "terrace"

How would I write this in the field calculator?

Answer 1

Should be something like these:

Pre-logic box

Dim propertyCount as Integer
propertyCount = CInt([Count])
Dim propertyType as String
If propertyCount = 1 Then
 propertyType = "detached"
ElseIf propertyCount = 2 Then
 propertyType = "semi"
ElseIf propertyCount >= 3 Then
 propertyType = "terrace"
End If

PropertyType =

propertyType

• user7620: Thanks cag. What do the first 3 rows mean please? (I am completely new to this). I used your answer but only got it to work by deleting the first 3 rows: Pre-Logic Script Code: If [Count] = 1 Then [PropType] = "detached" ElseIf [Count] = 2 Then [PropType] = "semi" ElseIf [Count] >= 3 Then [PropType] = "terrace" End If PropType = [PropType]
• César Argul García: If you replace in my code [PROPERTY_COUNT] for [Count] it should work for you. In the pre-logic script i's supposed to use for make operations with the values, and the below box to store the final value in the field you want.
• César Argul García: I've changed the field names. Now this should work in your feature class.
• user7620: It did work and I understand that ok. I just don't understand what the first 3 rows that you posted are supposed to do because it didn't work with them included. I am interested to learn what they should do. (Dim propertyCount as Integer propertyCount = CInt([Count]) Dim propertyType as String
• César Argul García: 'Dim propertyCount as Integer' and 'Dim propertyType as String' are variable declarations, these variables are used to store the values of the fields and to make operations with them. 'propertyCount = CInt([Count]' stores the value of the Count field in the propertyCount variable. The function CInt converts the value of the Count field to Interger, i used it because i'dont know if Count is a numeric field, I supposed that it is but I was not sure.

Answer 2

Solution 1: Joins

When the count is limited (e.g., perhaps it cannot exceed 4), an elegant and flexible solution is to prepare a lookup table and join it. You could stop there: no calculation is needed. Indeed, the join will automatically update the [Property Type] value any time [Count] changes or records are added. But if you want to permanently record the Property Type, it can be copied over in the Field Calculator.

The lookup table would have these contents:

[Count] [Property type]
 1 "Detached"
 2 "Semi"
 3 "Terrace"
 4 "Terrace"

Any other counts (such as 0 or 5) would result in null values for [Property type] after the join.

Solution 2: Array lookup

Especially when there can be many counts, a calculation may be better. Here is a Field Calculator expression. It uses Python because VBA does not support any function to limit the array index:

("", "Detached", "Semi", "Terrace")[min(3, !Count! )]

This formula simply uses [Count] as an index into an array of translated values. min makes sure that all values of [Count] exceeding 3 get treated the same as 3. The initial "" copes with zero counts, if they occur.

It should be clear how to generalize this example to cases where more (or fewer) lookup values can occur. It shines when the number of lookup values gets large enough to make "If" statements (and even "case" statements) cumbersome.

I tested this solution with ArcView 10.