Forum Discussion

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Jul 18, 2023

Microsoft List formula is not working

Hi all, I am encountering a problem in a calculated formula in the Microsoft List. Scenario- Based on different values in different columns , I need a record to appear as "Overdue" Formula u...
Lists
microsoft lists
SharePoint Online
SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Jul 18, 2023

Hi Imi_Jay ,

instead of using a calculated column you can also use JSON list formatting to achieve this effect.
That will recalculate automatically.

I translated your formula into this JSON (I hope i got the logic right)

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$DueDate]=='Missing info','Missing info',if(((Number([$DueDate])<=Number(@now)) && (toString([$ResultOutcome]) == '') && (toString([$DateResultMarked]) == '')) || ((Number([$DueDate])<=Number(@now)) && [$Require2ndAttempt] && (toString([$ResultOutcome]) == '') && (toString([$DateResubmissionPassed]) == '')),'OVERDUE',''))"
}

 
Just select your column and then select "Column Settings" and then "Format this column"

 


Switch to "Advanced Mode " at the bottom of the new dialog

 


Now paste the JSON formatting into the editor and save

 

Best Regards,
Sven

 

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Aug 18, 2023

SvenSieverding 
Thanks! Logic is correct. For some reason it's showing Overdue for all the scenarios. Could that be the way column name like [Date - resubmission passed ] should be written in the JSON code differently? That's the only issue I can think of. Appreciate if you can look into this.

Thanks,
Imalka Jayalath

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Aug 18, 2023

    Imi_Jay In SharePoint JSON formatting, you have to use the internal names of columns in format like [$InternalNameOfColumn] and not the display names of columns. So, try using internal names for all your columns which are referenced in the JSON.

     

    You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?  

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources