SOLVED

SP List Format Calculated Field

Iron Contributor

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"
    }
  ]
}

 

 

 

 

 

12 Replies

@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.

@jonboylib I just tried the same JSON provided by you on calculated column with Currency data type & it works fine for me:

Column settings

ganeshsanap_1-1659349362362.png

 

 

JSON formatting Output

ganeshsanap_0-1659349282068.png


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.

@ganeshsanap 

 

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 £

 

1.png

Yes, @ganeshsanap but the currency symbol is missing? See my updates response.
best response confirmed by jonboylib (Iron Contributor)
Solution

@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.

Thanks - that works. Something so simple again proving to be very difficult :\

@jonboylib You're welcome. Glad it worked for you!

Also true that sometimes there is very simple solution to the difficult problem.


Please consider giving a Like if my post helped you in any way.

@ganeshsanap Now the conditional colour formatting only works when I edit an item using 'edit in grid view'. As soon as I click 'exit grid view' the colour formatting is ignored.

@jonboylib Can you try hard refresh (Ctrl + F5) after you click 'exit grid view'? Also, check if JSON formatting code is still there in column settings.

If still facing this issue, try clearing browser cache.


Please consider giving a Like if my post helped you in any way.

@ganeshsanap I've tried a hard refresh, cleared cache, restarted the browser.. and the JSON is still appearing as below;

 

2.png

This worked for me. Thanks!

@DougAtBHB Great, I am glad it worked for you!


Please consider giving a Like if my post helped you in any way.

1 best response

Accepted Solutions
best response confirmed by jonboylib (Iron Contributor)
Solution

@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.

View solution in original post