How to update new field from existing field?

Question

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

So...

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? I've tried various methods without success.

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

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.