Default Column Values vs. Column Validations


Last time we talked a little about default values and showed how you can calculate the month name or day of the week from the current date using TODAY. If you added a record or two to the list using the default value, perhaps you noticed that even when you came back to the record a few days later, the default value had not changed. Why? Because the default value is defined at the time the record is created, not at some future time when someone views the record. Effectively, once defined for a new record, the value remains set unless a user manually changes the value of the field.

Perhaps you tried to create a default value using the value of another column in the list. For example, suppose you had an event list in which the past due date for any money owed to the event was seven days after the date of the event. Further suppose that you had a column named EventDate already in the list. Your first thought might be to create a column for the Past Due Date to have a default value calculated as EventDate + 7 or seven days after the event date.

Additional Column Settings

When you try to save your column definition though, you will get an error like the one shown in the next figure.

Something went wrong message

Why did SharePoint not allow the use of the value from a previous column in the default value of the current column? Simply because at the time that the default values for the new record (item) are set, which is before the user enters any values for any of the columns in the new record, the EventDate column does not yet have a value and is undefined. Therefore, SharePoint cannot use the EventDate column to calculate default value of the Past Due Date column.

So while I can take predefined variables like TODAY and use them in the calculation of a default value, I cannot use values from other columns in the list because those values are not defined at the time the default value must be calculated. That also further emphasizes why default values do not change over time, even those based on variables like TODAY, because they obtain their value at the time the default is calculated and that calculation is not performed again just because someone looked at the list record.

Let’s now take a look a column validation. This feature of lists was a welcomed addition to SharePoint 2013 and provided the ability to trap bad values at the time that data is entered. As developers know, trapping bad data through data validation at the time of data entry is far superior to running reports later to discover and fix bad data after it has been entered and the user has moved on. However column validation is often overlooked in SharePoint when defining columns. Perhaps part of the reason is that the area that defines column validation is by default closed when defining the column properties. You must click the box before the words Column Validation found at the bottom of the page to open this section. Second, it is often not clear to the user how to create a column validation expression. Let’s take a look at a simple example.

The first thing to understand is that you must create an expression that is either true or false. In the text of the Column Validation it implies you can enter an expression like [Company Name] = “MyCompany”. However, I have not found this type of expression by itself to be valid. Rather you must enter an expression that begins with an equal sign and then continue with an expression that will evaluate to true or false. Therefore, a valid version of this expression might be: = [Company Name] = “MyCompany”.

Note that the field name, even when self-referencing the current field must enclose the column name in square brackets if the name contains spaces. You can omit the square brackets if the column name does not contain spaces. Also the expression to the right of the first equal sign is not considered an assignment of a string to the column, but rather as a Boolean comparison expression. We are basically asking in this example whether the Company Name is equal to “MyCompany” , not setting the Company Name to a value of “MyCompany”

Suppose you want to validate the Event Date column of the list we previously talked about. You may want to make sure that the event date occurred in the future. Your first thought might be to create an expression that checked if the Event Date was greater than the current date as defined by the variable TODAY as shown in the following figure.

Column Validation using variable TODAY

This expression would also fail because it will try to treat the variable TODAY as a column name.

Something Went Wrong When Using Variable in Column Validation

However, there is a TODAY() function in Excel. So making the following slight change to the column validation will allow the updated column definition to be saved and will guarantee that event dates entered will always be greater than today.

Column Validation Using Function TODAY()

So to summarize, what do we know at this point?

  1. You cannot reference another column in the list within a default value expression.
  2. You can use default variables like TODAY and ME within a default value expression.
  3. You can use many (I have not yet checked them all) Excel functions within a default value expression.
  4. You cannot reference another column in the list within a column validation expression but you can reference the current column.
  5. You cannot use default variables like TODAY and ME within a column validation expression.
  6. You can use many (I have not yet checked them all) Excel functions within a column validation expression.

C’ya next time when I look at Calculated Column expressions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s