Share via

Migration Issues with SharePoint calculated field

  • Article

Hi, this is Abhishek Kumar with a new blog post, I am going to talk about migration issues with Calculated field from SharePoint on premise to SharePoint Online.

About Calculated field

Calculated columns are one of the most widely used columns in SharePoint. It is extremely useful when we have to perform data calculation, text manipulation or date and time calculations based on data from other columns using formula. We are going to discuss different issues we face during calculated column migration.

Formula containing double quotes

Using CSOM if we try to copy calculated field with formula containing double quotes we get following error

“The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column. “

Resolution

    1.  Read all attributes of missing calculated field to be added and reformat the field schema.
    2.  Set “Formula” and “Formula DisplayNames” to blank.
    3. Once field is added, replace Quotes in formula with escape character and update formula property of Calculated Field added in step 1.

string Formula= srcField.Formula.Replace('"', '\"');

calculatedField.Formula = Formula ;

calculatedField.Update();

Calculated field referencing other Missing calculated field

Adding Calculated field referencing other Missing calculated field throws error as it is unable to get reference of dependent field on target. It throws following error:

“The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column. “

Resolution

Before adding missing calculated fields, we need to reorder the sequence of source field collection (In memory) based on dependency.

    1.  Read list of all calculated fields from source list and read field reference based on field schema create ordered list of missing calculated fields based on dependency.
    2. Copy fields sequentially by iterating ordered list created in step 1.

Calendar list  with Column name “Event Type” used in Formula

If a calculated column in calendar list references “Event Type” columns it throws error

“One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.”

Resolution

    1.  Rename “Event Type” column to “EventType”
    2.  Add Calculated Column and include “EventType” in formula instead of “Event Type”.
    3.  Go back to list setting page and revert the name of “EventType” to “Event Type”.
    4.  Once column name is changed formula of Calculated Column will be auto corrected referencing “Event Type” which would be exact match of source field schema.

Conclusion

This topic explained migration issues with Calculated field and their remediation during migration from SharePoint on premise to SharePoint Online.

For more information, see https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx to know about SharePoint Calculated field and formula.