0

First time coding with JSON and need a little help...... I am attempting to make a calculated column with conditional formatting. So far i can get it to work but only partially. What i want is to have all cells default to green when empty (basic code), but change color based on the value.....

Basic Code Works


{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "style": {
        "color": "#ffffff",
        "padding-left": "10px",
        "background-color": "#0000ff"
    },
    "children": [{
            "elmType": "span",
            "txtContent": "$ ",
            "style": {
                "display": "inline-block",
                "padding": "0 1px"
            }
        },
        {
            "elmType": "span",
            "txtContent": "[$cSDP_x0020_Hardware_x0020_Varian]",
            "attributes": {
                "class": ""
            }
        }
    ]
}

text

But when i add an if statement the conditions work but the empty cells do not turn green.....

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "style": {
        "color": "#000000",
        "padding-left": "10px",
            "background-color": "= if(@currentField < 0 ,'#CB4335',  if(@currentField < ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15) ,'#FFC7CE', if(@currentField < ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3),'#FFEB9C', '#dff6dd'))"
    },


    "children": [{
            "elmType": "span",
            "txtContent": "$ ",
            "style": {
                "display": "inline-block",
                "padding": "0 1px"
            }
        },
        {
            "elmType": "span",
            "style": {
                "overflow": "hidden",
                "text-overflow": "ellipsis",
                "padding": "0 3px"
            },
            "txtContent": "[$cSDP_x0020_Hardware_x0020_Varian]",
            "attributes": {
                "class": ""
            }

        }
    ]

}

text

The column/field the formula is evaluating is a calculated number field and I am wondering if that has anything to do with it? I have played around with simple calculations as well and can not seem to even evaluate a value as "0", null, or '' in the if statement.

I have been at this for a few days trying different ways to get it to work and feel i must be missing something simple in the code? I really hope this isn't a stupid SharePoint limitation situation.....

Thanks for your help!

Screenshot #3 text

3
  • What is the formula for [..Variance]? Based on the data, it looks like [Budget-Actual]. The calculated field, [variance], is going to have a value of 0, >0 or <0. Do you mean why value of 0 doesn't have a green background? For that, You need to add one more condition "=if(@currentField==0, 'green', if(@currentField < 0 ,'#CB4335', ..)))". Jan 2 at 21:00
  • Thanks - Yes i want to highglight when the project costs are at certain thresholds of the budget, ie more than 30% remaining in budget, under 30% of budget remaining, under 15% of the budget, 0, over spent. As for the code, its the values below the 5th row that are not rendering that evaluate to 0 without JSON that are not rendering. They only render when data is added to the hardware budget and actual fields even if its a zero?
    – David Lane
    Jan 2 at 23:30
  • Try adding this additional condition in your existing condition : if((@currentField == 0) || (@currentField == ''),'#dff6dd', <other conditions here>. These conditions are totally based on what value you are returning using your calculated formulas. So, change conditions accordingly. Let me know if this works for you. Jan 3 at 4:10
0

Replace the current IF condition in the Background attribute with the below one:

=  if((@currentField == 0) || (@currentField.length == 0),'#dff6dd',if(@currentField < 0 ,'#CB4335',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.15)) && (@currentField > 0),'#FFC7CE',if((@currentField <= ([$cSDP_x0020_Hardware_x0020_Budget] * 0.3)) && (@currentField > 0),'#FFEB9C',''))))

Output

As you can see, the empty cell has been highlighted with white green background as you need!

enter image description here

Check also, JSON SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online

3
  • Comments are not for extended discussion; this conversation has been moved to chat. Jan 3 at 0:05
  • @melqassas 's answer is the closest we could get. Need to do some data value cleanup, but otherwise this works.
    – David Lane
    Jan 3 at 2:42
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review Jan 3 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.