Showing errors to the end-user is quite ugly, so we should be careful in protecting our formulas so that no ugly
#Value! are displayed on our lists. There are a lot of “IS” functions, as we’ve covered already the “ISERROR”, but I want to focus on the ISBLANK function since it’s extremely useful. The function will return true or false depending on if the column is empty or not.
It follows a simple pattern.
- Column to check
Here’s an example. Let’s say we have a column called text (I’m quite original) a title that indicates to us the value that is inserted (it will make sense in a second) and the calculated formula that is:
Let’s look at the list
The column is configured to show YES/NO so that we know if the column is filled in or not.
Now let’s see the list:
Let’s think about 3 cases and see what we get.
- Nothing filled in
The first and last are easy to guess what the result will be, but what about the second?
Let’s check the result:
It’s doing exactly as we expect even if, apparently, there’s nothing there. Especially in the cases where the user is using the SharePoint List directly, avoid having columns with spaces. Only have them if you really have a good reason do to so.
What about a lookup?
What will happen if we try to use the ISBLANK function in a lookup column. Let’s add one to our list.
We get the following list:
Change the formula to:
There’s no “lookup” column. Lookup columns are not supported
This function will only return true or false. You need to parse the value yourself to show something different to the user. Also, you won’t be able to use it in lookup columns as demonstrated above.
- You should always consider using this function if you’re not sure the value of the columns to avoid undesired results. Validating if a column is empty or not before a formula may be vital for the formula to work or return the intended results..
- You can encompass this formula in pretty much everything, but don’t go overboard. It doesn’t make sense to check
Microsoft’s IS Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Photo by Kelly Sikkema on Unsplash