The ISLOGICAL Function is not one of the most used functions, and it may not look as useful as others, but it has its useful applications. A logical value is more commonly know as a truth value indicating the proposition of truth. Very philosophical, but it all boils down to the evaluation of the value that represents a boolean type.
It follows a simple pattern.
- Value to check
String TRUE =ISLOGICAL(text) will return true
You can pass it any value, but it will only return true if the value is a boolean type. For example:
String "TRUE" =ISLOGICAL(text) will return false
It’s counterintuitive, but a string is not a boolean value.
Why use it?
I can see the question in your mind. If I need a boolean value to check if it’s a boolean value, why do I use it? It’s a good question.
The answer comes from the nature of the “IS” functions that are validation functions. Imagine that you’re importing data from a text file and want to import the data to a SharePoint List. The text file doesn’t have any definition of types, so you need to convert the data before you store it. After converting and storing the data is a best practice to check if the data type is correct. SharePoint won’t allow you to insert incorrect types, so you’ll have an error in the process.
As we’ve mentioned before and it’s always important to stress. Always deal with errors and don’t let your application or process fail. It’s not just a question of the users seeing error messages, but data that is not imported or something else much more serious.
You can provide any value, but the result will only be true if the value is a boolean value.
Always define the type as Yes/No
Since the “Is” function is true or false, it doesn’t make sense to have it as any other type.
You won’t get much with other types since you want a function to check the validity of data quickly.
“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 to the user the “message” columns that have useful error messages or actions that they can take to solve the problem. In the ISLOGICAL function, you can have the function that does the data conversion in one hidden column and the ISLOGICAL in another hidden column. The user will see only the final result, and you’ll have 2 columns where you can “debug” your data if not what you expect.
Always deal with errors.
Leaving them for the user to see is horrible, but also you can bring trouble to your own code. If you don’t validate a column, use it after in other calculations, and the error will cascade. You can have an error in one column, and the issue could be in whole different columns, so deal with issues 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 ISLOGICAL functions in a formula, you should review it and make everything more straightforward.
Microsoft’s ISLOGICAL Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Photo by Volodymyr Hryshchenko on Unsplash