Forum Discussion
jonboylib
Aug 01, 2022Iron Contributor
SP List Format Calculated Field
I have a calculated field called Total (data type is Currency) that counts the sum of some columns, formula is below and it works.
=SUM([Aug-22],[Sept-22],[Oct-22])
I want to conditionally format this column so that if the Total is greater than the Budget, add a warning class to the cell.
The code below i've copied from the MS website. It only works when the column field data type is Number (1, 1.0, 100), it doesn't work when the data type is Currency ($, ¥, €)
When I set the column data type to Currency ($, ¥, €) the column fields are empty, but the css does apply.
How can I apply the formatting to a Currency ($, ¥, €) field?
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"attributes": {
"class": "=if(@currentField > [$Budget],'sp-field-severity--warning', '')"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": "=if(@currentField > [$Budget],'Error', '')"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
jonboylib Try changing this line:
"txtContent": "@currentField"
to this:
"txtContent": "@currentField.displayValue"
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.
jonboylib I just tried the same JSON provided by you on calculated column with Currency data type & it works fine for me:
Column settings:
JSON formatting Output:
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.
- jonboylibIron ContributorYes, ganeshsanap but the currency symbol is missing? See my updates response.
jonboylib Try changing this line:
"txtContent": "@currentField"
to this:
"txtContent": "@currentField.displayValue"
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.
jonboylib What is the data type of [Aug-22], [Sept-22], [Oct-22] and Budget field? Can you show us the screenshot of list view with some sample data for these fields & calculated value in Total?
Also, JSON formatting works with internal name of column. So, make sure you are using correct internal name in JSON.
Follow this article to get the internal name of your SharePoint column: 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.
- jonboylibIron Contributor
The data type of [Aug-22], [Sept-22], [Oct-22] fields are Number. Does this make a difference?
I know the internal names of the columns are correct because when I change the data type from Number to Currency it works.
See image below, the calculation is working ok, and the conditional formatting is working ok - The Total column is currently data type Number. I want it to be Currency so I can show the symbol £