0

I have never used the SQL function in the field calculator and need some guidance. I am attempting to calculate new field data (date update) using python for a client that will look at first at one column and then another.

I will need something that will look at column one (DateAsBuilt) and if not null will return the right four characters of that column. If DateAsBuilt is null I need the code to then look at another column (Project) and return only the left four characters.

1

For a beginner, there are three important concepts to accomplish this:

  1. How to use the codeblock part of the Field Calculator to write and call a function
  2. How to test for null values
  3. How to return the first or last four characters of a string

I will explain each of these concepts below.

Item 1: You need to check "Python" and "Show Codeblock", then write a function definition in the "Pre-Logic Script Code" box.

Here is a function definition, which should be placed in your Pre-Logic Script Code. I named this function "find":

   def find(dateasbuilt, project): 
      if dateasbuilt is not None: 
         return dateasbuilt[-4:]
      elif dateasbuilt is None: 
         return project[:4]

Then you call the function in the box below, using the function name and the fields that you need it to reference.

So your calculated value should be set to

find( !DateAsBuilt!, !Project!)

Here is a screen-capture:

Field Calculator with the function entered

Item 2: Python uses "None" to represent null. Testing for null values using Python in a text field, you must test for "is None" or "is not None" ... note that a null value is never EQUAL TO Null. It either "is null" or "is not null" but cannot be evaluated using the = (equals sign).

Item 3: You can use Python slice notation to select the first or last four characters of a string. String[:4] will give the first four characters (up to, but not including the character at index 4, where strings are indexed starting at zero). String[-4:] will give the last four characters (all characters including and after the fourth-to-last character).

More on slice notation: https://stackoverflow.com/questions/509211/understanding-slice-notation

  • It worked! Thanks for taking me to school on this! Much appreciated. – TheNoviceCode Sep 20 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.