Column conditional format with Json to display attention colour if column is blank

Copper Contributor

Hi Devs,

 

I need your help. I cannot for the life of me figure out how to tweak this Json schema provided by Microsoft in order to format a column to simple display an attention or warning colour if a field in the column is blank/null/unpopulated. Same way the default SharePoint ' required info' attention banner behaves for missing mandatory fields library.

 

Please kindly help.

 

{
    "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
    "debugMode": true,
    "elmType": "div",
    "attributes": {
       "class": {
          "operator": "?",
          "operands": [
             {
                "operator": "<=",
                "operands": [
                   "@currentField",
                   70
                ]
             },
             "sp-field-severity--warning",
             ""
          ]
       }
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            },
            "attributes": {
                "iconName": {
                    "operator": "?",
                    "operands": [
                        {
                            "operator": "<=",
                            "operands": [
                                "@currentField",                  
                                70
                            ]
                        },
                        "Error",
                        ""
                    ]
                }
            }
        },
        {
            "elmType": "span",
            "txtContent": "@currentField"
        }
    ]
}

 

23 Replies

Thanks Dean, I have seen those and they don't have anything close to what I'm after which I would have thought was more basic than most of the other samples provided by Microsoft and others.

 

Can anyone please help with this?

Hi Silas,

If you want to just get a specific background-color in the cell depending on the value of that column, you can use something like this:

 

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "background-color": {
         "operator": "?",
         "operands": [
            {
               "operator": "==",
               "operands": [
                 "@currentField", "High"
                  ]
            },
            "#FFA07A",
            ""
         ]
      }
   }
}

I have used the above code successfully to display the Priority column in red if the value is 'High'. The JSON example you have quoted is needed if you want to display the pre-configured icons as well, like what I have used here:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "debugMode": true,
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField == 'Completed', 'sp-field-severity--good', if(@currentField == 'In Progress', 'sp-field-severity--low' ,if(@currentField == 'Not Started','sp-field-severity--warning', if(@currentField == 'Deferred','sp-field-severity--blocked', ''))"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField == 'Completed','CheckMark', if(@currentField == 'In Progress', 'Forward', if(@currentField == 'Not Started', 'Error', if(@currentField == 'Deferred','Warning','')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

See the attached image to view how the list appears.

 

 

 

Thanks for this -- I needed to highlight any empty column values in bright red.  I modified your JSON to this: 

 

{
  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
  "elmType": "div",
  "txtContent": "@currentField",
     "style": {
      "background-color": {
         "operator": "?",
         "operands": [
            {
               "operator": "==",
               "operands": [
                 "@currentField", ""
                  ]
            },
            "#FF0000",
            ""
         ]
      }
   }
}

 

And it works perfectly for what I need!

 

I forgot to mention -- to make it work for non-text Columns, convert the value to a string before evaluating:

 

{
  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
  "elmType": "div",
  "txtContent": "@currentField",
     "style": {
      "background-color": {
         "operator": "?",
         "operands": [
            {
               "operator": "==",
               "operands": [
                    {
                        "operator": "toString()",
                            "operands": [
                                "@currentField"
                            ]
                        },
                        ""
                  ]
            },
            "#FF0000",
            ""
         ]
      }
   }
}

 

Thanks again, @Bibhu Panigrahi!

This is very close to what I want but would love to apply a bunch of formatting if a cell is not blank rather than just one particular style.  I'm struggling with the operator/operands syntax for applying a bunch of styles to the child element if the field is not blank.

 

This is the formatting that I would like but without formatting the blanks.

 

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
   "elmType": "div",
    "children": [
        {
           "elmType": "div",
           "txtContent": "@currentField",        
            "style": {
                "background-color": "#ffffe0",
                "text-wrap": "wrap",
                "font-size": "12px",
                "font-weight": "bold",
                "padding": "4px",
                "border-style": "solid",
                "border-width": "2px",
                "border-color": "#ffff00"
            }
        }
    ]
}

Is it possible to give more values a color, instead of just one?

Yes, you can!  Microsoft has posted a nice example of that here: 

 

https://support.office.com/en-us/article/Column-formatting-1f927342-2bed-4745-b727-ff8b7ff96b22

 

@Bibhu Panigrahiis also doing something like that further up in this post, here.

 

If i wanted to do the opposite, display a color only if there is a value in the column.
How would that look?

I haven't tried that, but I'm guessing you can take these lines:

 

            },
            "#FF0000",
            ""
         ]

 

and switch them around like so:

 

            },
            "",
            "#FF0000"
         ]

 

Nice!

Almost what I've been trying to do but would it also be possible to display an alternative text in the colored blank fields eg "Use default value"?

I'm trying to do this for a date field so I can't add "Use default value" as the default cell value for the column.

 

Thanks!

Can you help me if I would like to highlight cell 1A (in column A)  if cell 1B (in column B) is blank?

As I believe the code you gave is for the same column

 

@Jeremiah Benjamin 

Hi Jeremiah, this code of yours does exactly what I want except that for those list items containing an object (in my case users) it replaces the user name with the text string [object Object] any ideas on how to avoid that?

@Erik WettergrenI'm definitely no expert in this, sorry!  I just shared what worked (which I borrowed from @Bibhu Panigrahi and modified a bit by trial-and-error until I got what we needed). 

 

I've found a lot of good stuff here:  https://github.com/SharePoint/sp-dev-list-formatting/tree/master/column-samples

 

Those samples are great starting points, hopefully you find something useful.  Good luck!

@nanuarSorry I missed this!  I have referenced column data from 1 column and formatted a 2nd based on it in other SP Lists.  The downside is you have to display both columns in the View.

 

You can get some good examples of how it all works here:  https://github.com/SharePoint/sp-dev-docs/issues/1250

 

Best of luck!

Tanks, will look into those examples!

@Erik Wettergren, I use "txtContent": "@currentField.title" to get the user name instead of [object Object] in people columns. @Jeremiah Benjamin , thanks for tagging me to your response.