Forum Discussion

SoniaW365's avatar
SoniaW365
Copper Contributor
Dec 18, 2024

Conditional Formatting using JSON

I have this code which is working great, however, every time I make a change to add in that if it is blank it should be light coral as well - 

 

here is the original - 

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) == 0, 'LightGreen', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 10, 'LightGreen', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) >= 11 && (Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 20, 'LightYellow', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) > 20, 'LightCoral', '')))", "color": "black", "padding": "5px", "border-radius": "5px" } }

 

Here is the new attempt at adding in a null value is also Light Coral. 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(isBlank([$Emp_x0020_Returns_x0020_OF306]) || isBlank([$LP_x0020_Sends_x0020_OF306]), 'LightCoral', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 10, 'LightGreen', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) >= 11 && (Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 20, 'LightYellow', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) > 20 || isBlank([$Emp_x0020_Returns_x0020_OF306]) || isBlank([$LP_x0020_Sends_x0020_OF306]), 'LightCoral', '')))))",
    "color": "black",
    "padding": "5px",
    "border-radius": "5px"
  }
}

 

It just makes everything transparent.  Any help would be highly appreciated!  Too bad this isn't built in functionality.

 

  • iaadillatif's avatar
    iaadillatif
    Copper Contributor

    Your JSON might have an issue because the logic for handling null or blank values (isBlank) is either redundant or conflicts with other conditions. Correct and streamline your JSON to handle blank values properly while maintaining the existing logic.

    Here’s a fixed and optimized version:

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "style": {
        "background-color": "=if(isBlank([$Emp_x0020_Returns_x0020_OF306]) || isBlank([$LP_x0020_Sends_x0020_OF306]), 'LightCoral', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 10, 'LightGreen', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) >= 11 && (Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 20, 'LightYellow', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) > 20, 'LightCoral', '')))))",
        "color": "black",
        "padding": "5px",
        "border-radius": "5px"
      }
    }

    Key Changes:

    1. Handling isBlank:
      Added a standalone condition at the start of the background-color formula to handle blank values (isBlank([$Emp_x0020_Returns_x0020_OF306]) || isBlank([$LP_x0020_Sends_x0020_OF306])). This condition is now evaluated before checking any numeric differences.
    2. Avoid Conflicts and Redundancy:
      Removed any redundant isBlank checks within other conditions to avoid unexpected outcomes.
    3. Transparent Issue Fix:
      Empty strings ('') as the fallback value could cause "transparent" effects. Ensure every branch of your if statement assigns a proper color.

    Explanation:

    • Null/Blank Check First: If either field is blank, the background becomes LightCoral.
    • Next, Numeric Comparisons: When both fields are non-blank, it calculates the day difference and applies colors accordingly.
    • Final Fallback: There's no undefined or transparent state—all paths lead to valid color outputs.

    Troubleshooting:

    • If you still see unexpected behavior, double-check your column names and ensure that [$Emp_x0020_Returns_x0020_OF306] and [$LP_x0020_Sends_x0020_OF306] are correct.
    • Ensure that your list or library allows JSON column formatting and there are no syntax errors when pasting the JSON.

    This should solve your issue and provide the intended results! 😊

  • Fixed it for you :)

     

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "style": {
        "background-color": "=if(isBlank([$Emp_x0020_Returns_x0020_OF306]) || isBlank([$LP_x0020_Sends_x0020_OF306]), 'LightCoral', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 10, 'LightGreen', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) >= 11 && (Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) <= 20, 'LightYellow', if((Number([$Emp_x0020_Returns_x0020_OF306]) - Number([$LP_x0020_Sends_x0020_OF306])) / (1000 * 60 * 60 * 24) > 20, 'LightCoral', '')))))",
        "color": "black",
        "padding": "5px",
        "border-radius": "5px"
      }
    }
    

     

Resources