Forum Discussion
Microsoft List formula is not working
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
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 JayalathImi_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.
- Hey Sven,
This didn't work for me:
After following the steps, the column simply showed the entire formula as a text string (rather than executing it).
Do you know how to fix this, so that the formula actually executes? Thanks!sbocknek Make sure you did not miss the equal to ( = ) sign at the start of this expression:
=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',''))Also, use the correct internal names of your SharePoint columns. 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.
- Hi Sven,
Thanks very much.I will try this.I don't know JSON at all.Hence trying to find a solution that would go with A2 style formulas. I will try this. The logic should be fine.
Thanks,
Imalka Jayalath
