Sharepoint Calculated Columns – Unsupported Functions And Workarounds

Unsupported Functions in SharePoint Calculated Columns

SharePoint calculated columns provide a powerful way to derive values in list items and document libraries by creating formulas that operate on other columns. However, several useful functions are not supported due to restrictions in the formula engine and can lead to errors if attempted to be used.

Common Functions That Don’t Work and Why

Some common functions that cannot be utilized in SharePoint calculated columns include:

• RAND – Returns a random number. Unsupported due to inability to recalculate formulas that contain non-deterministic functions.
• TODAY – Returns the current date. Cannot reference values that change over time.
• IFERROR – Returns an alternative value if an error occurs. Error handling is restricted.

The restrictions stem from the need to be able to automatically recalculate column values whenever related columns are updated. Since functions like RAND and TODAY produce different values over time, the dependencies cannot be tracked.

Examples of Unsupported Functions

Using the RAND, TODAY, or IFERROR functions directly in a calculated column will result in an error. For example:

The formula engine does not know how to process these functions, so it rejects them. Workarounds are needed to emulate the behavior by using different combinations of supported functions and SharePoint features.

Workarounds for Unsupported Functions

While some functions cannot be directly utilized in SharePoint calculated columns, alternative solutions exist to mimic the functionality in roundabout ways. These workarounds leverage different combinations of built-in formulas, additional columns, and workflows to achieve the desired output.

Alternative Formulas to Achieve Similar Results

In some cases, native calculated column functions can calculate slightly modified versions of the desired output.

For example, while RAND is not permitted, the COLUMN function could be used to generate pseudo-random numbers by applying the MOD function on sufficiently large column identifiers.

Although not truly random, for some basic use cases, the output may be adequate.

Using Workflows and Other Columns Instead

SharePoint designer workflows and custom columns can also fulfill some of the void left by unsupported functions.

Workflows enable time-dependent actions like updating the current date in a separate dat/time column on item create/update. This result can then be referenced in calculated columns.

Lookup columns can also help bridge gaps by allowing formulas to indirectly reference lists and drive outputs based on changes.

Example Workaround for RAND Using a Lookup Column

A makeshift RAND function can be constructed with the following steps:

1. Create a lookup list with a large number of items, each with a number between 0 and 1 in a ‘RandomNumber’ column.
2. In the main list, add a lookup column pulling the random number from that list.
3. Set the lookup to refresh on item change via workflow.
4. Reference the lookup column in calculated columns to surface random numbers.

This allows regenerating random numbers on demand to work around the lack of RAND support.

Emulating Unsupported Functions

With greater customization effort, it is also possible to fully emulate functions like RAND and TODAY using JavaScript injection to extend the formula engine capabilities.

JavaScript Injection with Script Editor Web Part

By leveraging the Script Editor web part in SharePoint pages, custom JavaScript code can be embedded to manipulate item data on the fly.

This allows appending unsupported functions during render time to mimic native calculated column behaviors.

Sample Code to Add RAND and TODAY Functions

The following scripts demonstrate adding RAND and TODAY functions to calculated columns.

RAND Function

“`js

“`

RAND can be emulated by generating a random number with JavaScript and setting it in the rendered output of the calculated column with a “rand” class applied.

TODAY Function

“`js

“`

The TODAY function can be mimicked by creating a new JavaScript Date object for each element with a “today” class and displaying the date string.

With some additional logic, the output updates could also be set to recur automatically at intervals to continually refresh.

Considerations and Limitations

While the workarounds allow emulating unsupported functions, some considerations should be kept in mind before implementation.

Performance and Complexity Tradeoffs

The more complex script-based solutions can tax site performance with too many customizations. Balance is needed to enhance capabilities without overly compromising speed.

Character Limits in Calculated Columns

Formulas have an upper limit that can be restrictive for some elaborate workarounds. Workflow additions and separate columns may be necessary to supplement long logic chains.

Managing Solutions Long-Term

Heavier customizations also raise maintenance effort to ensure continuity with future SharePoint updates. Changes may break hardcoded elements. Abstraction layers can isolate custom logic to ease management.