Forum Discussion

jonboylib's avatar
jonboylib
Iron Contributor
Aug 01, 2022
Solved

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.

      • ganeshsanap's avatar
        ganeshsanap
        MVP

        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.

    • jonboylib's avatar
      jonboylib
      Iron Contributor

      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 £

       

Resources