Hi first time here - query on field calculator

[daveDurkin]

Hi - very new to this so apologies for its simplicity.

Arc Desktop v10.2

I'm trying to complete a table field based on (part of) the contents of another field from the same table. I've tried VB and Python.

I'm trying to get the contents of a field called 'Geology1' filled as a text string "CLAY, SILT, SAND AND GRAVEL" if another field called RCS-X has a text string "C+S+V+Z".  For example.

I have tried the field calculator (calculate field) with various forms of the attached.

If [RCS_X] = "C+S+V+Z"  Then Output = "CLAY, SILT, SAND AND GRAVEL" End If

I always get syntax errors.

A student with about 10,000 rows of data to process so any help appreciated

Thanks

Dave

[RichardFairhurst]

To make that expression work as a single line if statement it has to be written:

If [RCS_X] = "C+S+V+Z" Then "CLAY, SILT, SAND AND GRAVEL" Else [Geology1]

The Field Calculator requires a value for every record, including the records that do not meet the condition.  This will use the current contents of Geology1 for any record that does not contain "C+S+V+Z".

However, if these are all of the letters you translate and all combinations of these letters are possible you could write an advanced statement like

Parser:  VB Script

Use Code Block:  Checked

Pre-logic Script Code:

MyArray = Split(Temp, "+")
Output = ""
Name = ""
For i = 0 to UBound(MyArray)
  Select Case MyArray(i)
    Case "C"  Name = "CLAY"
    Case "S"  Name = "SILT"
    Case "V"  Name = "SAND"
    Case "Z"  Name = "GRAVEL"
    Case Else Name = MyArray(i)
  End Select
  if i = 0 Then
    Output = Name
  Elseif i = UBound(MyArray) Then
    Output = Output & " AND " & Name
  Else
    Output = Output & ", " & Name
  End If
Next

Expression:  Output

[daveDurkin]

Hi Richard

Thanks very much for getting back to me.

Having tried the one line example, I still get an 'ERROR 99999: Error executing function.  Syntax error.  Failed to execute (CalculateField).

I think I have it exactly as you suggest but any further advice would be appreciated.

Thanks.........Dave

[daveDurkin]

o Hi Richard

Thanks very much for getting back to me.

Having tried the one line example, I still get an 'ERROR 99999: Error executing function. Syntax error. Failed to execute (CalculateField).

I think I have it exactly as you suggest but any further advice would be appreciated.

Is it ok as a statement if the Geology1 field is blank (ie will it just leave it blank)

The more complex statement I’m afraid won’t work in the general sense because the format of the RCS_X field is very different in many cases. I’m expecting to run the one line statement multiple times.

Many Thanks.........Dave

[RichardFairhurst · Accepted solution]

IT does not matter what is in Geology1, it will just stay the same unless the condition is met to do the translation.  The Field Calculator does not support the one line syntax for VB Script, unless someone can figure out that I made a mistake with the VB Script inline syntax shown in the microsoft help.  It supports it for Python.  For Python it would be:

Parser:  Python

"CLAY, SILT, SAND AND GRAVEL" if !RCS_X! = "C+S+V+Z" else !Geology1!

For VB Script you have to use the the code block:

Parser: VB Script

Use Code Block:  Checked

Pre-Logic Code Block:

If [RCS_X] = "C+S+V+Z" Then
  Output  = "CLAY, SILT, SAND AND GRAVEL"
Else
  Output = [Geology1]
End If

Expression:  Output

If you have problems look at this post where this was discussed previously.

[RichardFairhurst]

After thinking about your problem you are approaching it in a wasteful way.  You should run the Summary Statistics tool on the table with the RCS_X field as the unique case field (right click the RCS_X field header and select Summarize...).  Do any summary, for example First for Geology1.  If you output to a file geodatabase you can rename the field called First_Geology1 to just Geology1 in the ArcCatalog properties or just leave it.  (If you output to dBase the field will be trucated and I don't use those tables, so you have to deal with the output field name if you do).

Then type all of the conversion values into the output table into the First_Geology1 (or Geology1 or whatever field) in an Editor session.  After stopping the Editor, you can join the summary table on the RCS_X field to the original feature class and use the field calculator to transfer the new field values you typed, i.e. [Sum_Output.First_Geology1] by calculating those values over.  When doing the join add the Field Index when prompted.  This will save you having to retype the formula over and over for each value in the table for each separate run of the field calculator and you won't have to add quotes or anything around the old or new values.  Just type the new values one time into the table and calculate once.

[daveDurkin]

Many thanks Richard - much appreciated