Field Calculator based on another field in Table

[Anonymous User]

Howdy,

I have joined a sewer Gravity Main (pipes) table to my Manholes table based on [From_Manhole]. My next step was using the field calculator set the values of [Upstream_Elevation] in my Gravity Mains table equal to the values of [Invert_Elevation] from the joined Manholes table. However, when I try to execute this function, I get an error message. Am I using the right expression? The input field and destination fields are both Double with a precision of 38... not sure if this matters?

Here is what the geoprocessing results showed me:

infrastructure.PUBLICWORKS.ssGravityMain.Upstream_Elev= [infrastructure.PUBLICWORKS.ssManhole.INVERTELEV]

General Error executing calculator
ERROR 9999999
Syntax error
Failed to execute

[RichardFairhurst]

Is there a way to write an If then statement in the Field Calculator?
ie. If the UpstreamElevation is <Null> then UpstreamElev=InvertElev

This way I dont overwrite values in the Upstream Elevation field that are already in there?

This requires that you check the box to allow for a codeblock located just above the expression text box.  This will create two entry textboxes, with the top one being for code and the bottom one being the output to the field.  I would do it this way in VB Script (Python can also do it but I am a VB guy)

Parser: VB Script

Use Codeblock: checked

Pre-Logic Codeblock:

If IsNull([UpstreamElev]) Then
  Output = [InvertElev]
Else
  Output = [UpstreamElev]
End If

Expression:  Output

For the bracketed field names in the Pre-Logic Codeblock replace them with whatever the field list inserts to handle the join qualification of the field names.  You have to output a value for every record, so the only way to prevent an error or a change to an existing value is to overwrite the existing value with itself.

[Anonymous User]

Thanks for all the help! I appreciate it

[Anonymous User]

This requires that you check the box to allow for a codeblock located just above the expression text box.  This will create two entry textboxes, with the top one being for code and the bottom one being the output to the field.  I would do it this way in VB Script (Python can also do it but I am a VB guy)

Parser: VB Script

Use Codeblock: checked

Pre-Logic Codeblock:

[ATTACH=CONFIG]30393[/ATTACH]

Expression:  Output

For the bracketed field names in the Pre-Logic Codeblock replace them with whatever the field list inserts to handle the join qualification of the field names.  You have to output a value for every record, so the only way to prevent an error or a change to an existing value is to overwrite the existing value with itself.

Sorry, my lacking scripting/syntax knowledge is what holds me back here. Here is what I am putting in as directed, yet the values are not calculated. Did I enter the right field in the output?

[RichardFairhurst · Accepted solution]

Sorry, my lacking scripting/syntax knowledge is what holds me back here. Here is what I am putting in as directed, yet the values are not calculated. Did I enter the right field in the output?

The second text box is wrong.  It should just have the word Output in it.  Output is a variable name and the calculation will substitute that name with the appropriate value determined by the codeblock logic.

By putting the calculated field name into the output textbox the codeblock logic was totally ignored and instead every value already in the field just overwrote itself with the same value that it already had.