Column formatting SharePoint

Start using conditions in SharePoint column format

geertby Geert•Posted 30 December 2020

In my previous post Start creating your own Column format in SharePoint, I explained the basics of writing your JSON. And I completed that post with the following column format:

{
  "$schema" : "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType" : "div",
  "txtContent" : "@currentField.displayValue",
  "attributes" : {
                 "title" : "@currentField"
				},
  "style" : {
				"color" : "#548235",
                "background-color" : "#e5fbd6",
				"padding" : "0 0.5em 0 0.5em" 
  			}
}

In this post I will show you how to use these conditions.
There are two methods of writing these conditions, the easiest one is the basic “if-then-else” principle that you maybe have used already in various other situations, like for example in Excel.
And this will also be the one that we will be using now.

If-then-else basics

At any point in our JSON the value part of our “attribute-value”-pair can contain an if-statement.
The following example will display the text ZERO when the value in that column is 0, in all other situations the actual value will be shown.

{
  "$schema" : "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  	"elmType" : "div",
	"txtContent" : "=if( @currentField == 0 , 'ZERO' , @currentField.displayValue )"
}

Before

After

Operators

In the above formule we are using an operator to == (equals) to check whether or not the value of the currentField is equal to 0.
Other logical operators are

OperatorDescriptionOperatorDescription
==Equal to!=Not equal to
>Greater than<Less than
>=Greater than or equal to<=Less than or equal to
||OR&&AND

In the example underneath I have used to the ||-operator to make sure I also display ZERO when no value is available. 

{
  "$schema" : "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  	"elmType" : "div",
	"txtContent" : "=if( @currentField == 0 || @currentField == '', 'ZERO' , @currentField.displayValue )"
}

One of the disadvantages using the if-then-else-syntax is that it can become quite long. So sometimes you might want to break this in multiple lines, but in JSON we are not allowed to add line breaks within the attribute/value-pair. So you can not actually write this.

Practical use: test for missing metadata

To avoid missing metadata in a library we can make the value mandatory. This has some advantages, like the opening of the details pane and the automatical coloring of the cell when the data is missing.
But in the old days, aka classic experience, the file was also checked out, which did cause quite some trouble in quite some situations, and this experience is still available to users.
So let’s create a column view that clearly indicates that data is missing, without using the “Require that this column contains information”.

Th following code will work for all columns that support .displayValue ( Text, Number, Date… )

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField.displayValue",
  "style": {
    "background-color": "=if(@currentField.displayValue =='','#D38498','inherit')"
  }
}

Change the reference @currentField.displayValue when the column is a column with multiple properties. Like a persons column or a link column.

  • Columntype = Person: @currentField.title
  • Columntype = Lookup: @currentField.lookupValue
  • Columntype = Link: @currentField.desc

Columntypes with multiple properties, basically contain multiple values, for example, a link columntype contains both:
the description of the link ( @currentField.desc ) and the actual link ( @currentField ).
In the same fashion a persons column contains
the name of the person ( @currentField.title ),
the e-mailadress ( @currentField.email ),
the address of their profile picture (@currentField.picture) ….