How to apply JSON formatting to a date lookup column

Question

Hi there,

I've created a site column called Next Action Date, and I have some JSON to apply column formatting when the date is less than or equal to today's date. This JSON works fine:

{
"$schema":
"https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"debugMode": true,
"txtContent": "@currentField",
"style": {
"color": "=if([$Next_x0020_Action_x0020_Date] <= @now , '#ec4d37', '')",
"font-weight": "=if([$Next_x0020_Action_x0020_Date] <= @now , 'bold', 'normal')"
}
}

But when I use the same Next Action Date column as a lookup column in a list (its reference becomes Contact_x003A_Next_x0020_Action_x0020_Date), I cannot get the formatting to work properly. I know that one piece I need to change is "txtContent": "@currentField.lookupValue". But there must be more! I've tried if([$Contact_x003A_Next_x0020_Action_x0020_Date.lookupValue] <= @now , '#ec4d37', '')" and if([$Contact_x003A_Next_x0020_Action_x0020_Date] <= @now , '#ec4d37', '')". What seems odd, is that when I inspect the element with Chrome Developer tools, the formatting does get applied correctly, but there is no value for the date, and the console says "cannot evaluate Contact_x003A_Next_x0020_Action_x0020_Date.lookupValue"

Does anybody know the right way to do this?

Thanks,
Tom

Answer 1

Hi @Tom Benjamin

The value returned by lookup colum is of string type, and the date you need to compare is of date type, so this json will be invalid.

We should convert the sting type to the date type:

if(Date(@currentField.lookupValue)  

Please refer to the full code below:

{  
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
  "elmType": "div",  
  "debugMode": true,  
  "txtContent": "@currentField.lookupValue",  
  "style": {  
    "color": "=if(Date(@currentField.lookupValue)<= @now, '#ec4d37', '')",  
    "font-weight": "=if(Date(@currentField.lookupValue) <= @now, 'bold', 'normal')"  
  }  
}  

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 2

Well, that's odd, I don't know how that semi-colon got into my paste of the code, because it's not actually there! So, no difference, even after pasting your code into the column settings. I will contact support.

Thanks for persisting, @Itch Sun-MSFT