May 26, 2022

Samir Daoudi's Technical Blog

SharePoint, Office 365, C#, .Net, SQL Server News, Tips, Ebooks, Events, Webinars ….etc

Calculated Field Formulas for Default value

Have you ever tried to use a calculated field formula in “Default value:” section when setting up “Additional Column Settings” for a custom site column in SharePoint? Using column references in a formula

Have you ever tried to use a calculated field formula in “Default value:” section when setting up “Additional Column Settings” for a custom site column in SharePoint?

I’m guilty. And, even though SharePoint didn’t complain about referencing columns in the formula,  when I tried to add this column to a list I got an error stating:

“The formula contains reference(s) to field(s).”

After a couple of wasted hours trying to make this work, I found this really helpful piece of information from Microsoft:

“You cannot reference another column in a formula that creates a default value for a column.”

In case you want to read the full abstract on “Using column references in a formula“, here it is:

Using column references in a formula

A reference identifies a cell in the current row and indicates to a list or library where to search for the values or data that you want to use in a formula. For example, [Cost] references the value in the Cost column in the current row. If the Cost column has the value of 100 for the current row, then =[Cost]*3 returns 300.

With references, you can use the data that is contained in different columns of a list or library in one or more formulas. Columns of the following data types can be referenced in a formula: single line of text, number, currency, date and time, choice, yes/no, and calculated.

You use the display name of the column to reference it in a formula. If the name includes a space or a special character, you must enclose the name in square brackets ([ ]). References are not case-sensitive. For example, you can reference the Unit Price column in a formula as [Unit Price] or [unit price].

Notes:

  • You cannot reference a value in a row other than the current row.
  • You cannot reference a value in another list or library.
  • You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
  • You cannot reference another column in a formula that creates a default value for a column.

Leave a comment

You may have missed

Show Buttons
Hide Buttons

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

%d bloggers like this: