topic Re: Field Calculator based on another field in Table in Data Management Questions

Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 13:29:57 GMT

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

Re: Field Calculator based on another field in Table

RichardFairhurst — Fri, 10 Jan 2014 13:42:15 GMT

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

All you should need is the input field in the brackets in the expression of the Field Calculator chosen from the field name list. I.e.

[infrastructure.PUBLICWORKS.ssManhole.INVERTELEV]

You don't put the target field in the calculation expression, you just right click the field you want the calculation to update to initiate a Field Calculation on it.

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 13:46:29 GMT

Yes, sorry if I didnt make that clear. I did right click UpstreamElevation field and selected = [infrastructure.PUBLICWORKS.ssManhole.INVERTELEV] as the input

Re: Field Calculator based on another field in Table

MichaelVolz — Fri, 10 Jan 2014 13:54:10 GMT

What version of ArcGIS are you using?

Are you performing this operation in the Field Calculator tool of ArcMap?

Is the Scale property for both double precision fields also the same?

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 13:56:01 GMT

yes, I am doing this all in Field Calculator, I right clicked the field I want to calculate, selected and equals symbol, and double clicked the Invert Elevation field from the joined table, that's it. I am using 10.1 and it is an ArcSDE database.

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 13:57:51 GMT

the precision and scale are both the same

Precision is 38
Scale is 8

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 14:01:08 GMT

I got it, ha! I must do not need the equals symbol in the expression

syntax, syntax, syntax.... doh!

Re: Field Calculator based on another field in Table

MichaelVolz — Fri, 10 Jan 2014 14:04:26 GMT

Do you need to perform this type of operation when new sewer information is provided to you (collected)? If so, you might want to consider making a model and exporting it to python.

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 14:05:31 GMT

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?

Re: Field Calculator based on another field in Table

RichardFairhurst — Fri, 10 Jan 2014 14:12:09 GMT

Do not use an equal sign in the calculation expression. That is assumed and only applies if you wanted a True/False result from the calculation (requiring both side of the = expression to have values). You don't want that. All that should be in the expression is the bracketed field name inserted by the field list.

Re: Field Calculator based on another field in Table

RichardFairhurst — Sat, 11 Dec 2021 13:22:39 GMT

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.

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 14:31:40 GMT

Thanks for all the help! I appreciate it

Re: Field Calculator based on another field in Table

Anonymous User — Fri, 10 Jan 2014 16:19:24 GMT

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?

Re: Field Calculator based on another field in Table

RichardFairhurst — Fri, 10 Jan 2014 16:23:32 GMT

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.