Mon. May 25th, 2020

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

Copyright © All rights reserved. | Newsphere by AF themes.
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.