How to stop JSON formatted calculation happening unless the source column is populated

Sam_01581 40 Reputation points
2024-10-21T20:26:29.4+00:00

Hi there!

I have put together a view in our teams task management list to keep track of reviews. I am struggling to write JSON that allows me to apply conditional formatting that meets our needs. My main issue is the source column being empty causes my formatted column to display an irrelevant number. I don't want this calculation to occur unless the source column is populated.

For context, I have six key columns in this view:

  • Completed Date (Date)
  • Days Since Completed (Number)
  • Review Request Sent (Date)
  • Days Since Review Requested (Number)
  • Reminder Sent (Date)
  • Days Since Reminder (Number)

The idea is to send the review request and subsequent reminders within three stages. Seven days after each populated date column, the matching 'Days Since' column will highlight. The current conditional formatting applied on each 'Days Since' column is this:


{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if((@now - Number([$ReminderSent])) / (1000 * 60 * 60 * 24) >= 7, 'sp-field-severity--blocked', '')"
  },
  "style": {
    "display": "flex",
    "align-items": "center",
    "justify-content": "center"
  },
  "children": [
    {
      "elmType": "div",
      "style": {
        "color": "white",
        "font-size": "12pt",
        "font-weight": "bold",
        "padding": "0 4px"
      },
      "txtContent": "=floor((Number(@now) - Number([$ReminderSent])) / (1000 * 60 * 60 * 24))"
    }
  ]
}

This mostly works. The problem now is when the source date column ($ReminderSent in this case) is blank, the formatted column is displaying "20017", triggering the conditional severity formatting causing clutter and confusion.

I've tried a mix of solutions from other forums to no luck sadly. Ideally, the calculation won't occur if the source column is blank, and instead display "0" or nothing at all.

Thanks in advance :)

SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
3,044 questions
0 comments No comments
{count} votes

Accepted answer
  1. Xyza Xue_MSFT 25,546 Reputation points Microsoft Vendor
    2024-10-23T01:59:32.3833333+00:00

    Hi @Sam_01581 ,

    Thank you for posting in this community.

    Using the following json code, if "ReminderSent" column is blank, and instead display nothing at all.

    JSONCopy

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
      "elmType": "div",
      "attributes": {
        "class": "=if(Number([$ReminderSent]) == 0, '',(if((@now - Number([$ReminderSent])) / (1000 * 60 * 60 * 24) >= 7, 'sp-field-severity--blocked', ''))"
      },
      "style": {
        "display": "flex",
        "align-items": "center",
        "justify-content": "center"
      },
      "children": [
        {
          "elmType": "div",
          "style": {
            "color": "white",
            "font-size": "12pt",
            "font-weight": "bold",
            "padding": "0 4px"
          },
          "txtContent": "=if(Number([$ReminderSent]) != 0, floor((Number(@now) - Number([$ReminderSent])) / (1000 * 60 * 60 * 24)), ' ')"
        }
      ]
    }
    
    
    
    

    Result:

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.