SharePoint: Lower Function

One of the trickiest things is to keep data consistent, especially if it’s inserted by the users. Each person has their way of providing data, and the same thing can be written in multiple ways. For example, names are written in different ways in Europe. Here in Portugal, we usually write the name in order, so first and last name. But in France, people start with the last name, and sometimes they fully capitalize it. So my name in a French company would be “GOMES, Manuel” and here in Portugal “Manuel Gomes”. So today, let’s look at the SharePoint lower function and how it can bring some consistency to how the data is stored.

Usage

It follows a simple pattern.

String or column name

Example for a column name:

String
"Manuel T. Gomes"

=LOWER([Title])

will return 

"manuel t gomes"

You can provide the string like:

=LOWER("Manuel T Gomes")

will return:
"manuel t gomes"

Although numbers don’t have capitalization, the function won’t return an error if you use a number column:

Number
12

=LOWER([numberColumn])

will return:
12

Be careful, however, with dates. Dates are stored internally as a number, so you’ll get a number if you use a data column as an argument.

Date
21st of September 2021

=LOWER([dateColumn])

will return:
44460

For a “Yes/No” column, you’ll get the result as a “true/false”.

Yes/No
Checked

=LOWER([booleanColumn])

will return:
true

For a location column, you can use the function, but you’ll only be able to use it with each section. So for example, this is valid:

=LOWER([locationColumn: City])

But this isn’t:

=LOWER([locationColumn])

Finally, let’s look at the “Choice” columns. If the column only accepts one value, then you’ll get the string in lowercase.

Choice
Choice 1

=LOWER([choiceColumn])

will return:
choice 1

Limitations

The SharePoint lower function can’t be used on lookup columns. You can’t use it also in choice columns that allow multiple selections.

Recommendations:

Here are some things to keep in mind.

“Hidden” Validation Columns

Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose the “message” columns that have helpful error messages or actions that they can take to solve the problem.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your code. If you don’t validate a column, it can be used in other calculations, and the error will cascade. You can have a mistake in one column, and the issue could be in whole different columns, so deal with cases where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested SharePoint LOWER functions in a formula, you should review it and make everything more straightforward.