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?
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.
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.
