Forum Discussion

DT-83's avatar
DT-83
Copper Contributor
Jun 30, 2024

SharePoint List conditional formatting compare columns

Hi,

 

I have a SharePoint list to manage rental invoices and ensure the amounts on the invoices are the same as they are on the lease agreement. 

The invoices are captured in this SharePoint list. I have a lookup column pulling what the amount must be from another list containing the amounts as stated in the lease agreements for each of our leased properties.

So, when entering the amount charged on the invoice, one can see what it should be in another column.

I would like to set up conditional formatting so that the 2 columns are compared and highlighted if there is a difference.

The lookup column is "SiteName:RentPerMonth" and the column to be populated is "Rent". Both are number columns

 

I have tried the following in "edit current view", but I'm probably doing something wrong

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "style": {
    "color": "=if([$Rent] != [$SiteName_x003a_RentPerMonth], 'green','red')"
  }
}

 



SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Jul 01, 2024

    @DT-83  you've missed out the elmType and txtContent lines and you select Format current view and add the JSON in advanced mode. I would have thought that it would be green if they match and red if they don't.  So that would be:

     

     

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Rent] == [$SiteName_x003a_RentPerMonth], 'green','red')"
  }
}

     

     

     

    Normally I highlight the background-color instead of the text color as I find it easier to spot when something's red. 

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

     

    • DT-83's avatar
      DT-83
      Copper Contributor
      Jul 01, 2024
      HI Rob_Elliott
      Thank you very much for the reply! I have no idea why, but it still doesn't work. The json looks correct, though. It doesn't change any formatting. Do you think it has to do with the fact that the one is a lookup column? I've seen it recommended to use "@currentField.lookValue in some instances. Though, not even this works. Neither with column formatting nor view formatting

Resources