Sharepoint column background using indexof

Boylan, Chris 0 Reputation points
2023-10-03T11:10:56.8733333+00:00

I have a text field that I want to verify (and change background colour accordingly) against either a lookup field in the same list or alternatively directly with the other list being used as the lookup. So far I have this

The text field is in the list WFD-Training-Calendar.Title and the lookup values I want to check against are in another list 'WFD Training courses'.Title The calendar also has a lookup column called 'TrainingCrsLookup' which looks up 'WFD Training courses'.Title so there are options. So far the text in the calendar title field all goes blank and I want non matches to have a red background.

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
   "elmType": "div",
   "style": {
      "background-color": "=if(indexOf('[$Lookup trng crs title]'.Title, @currentField) == -1, 'red', '')"
   },
   "children": [
      {
         "elmType": "span",
         "txtContent": "@currentField"
      }
   ]
}

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,808 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Boylan, Chris 0 Reputation points
    2023-10-03T14:21:27.8233333+00:00

    I've reposted in the sharepoint forum

    0 comments No comments

  2. Yanli Jiang - MSFT 26,761 Reputation points Microsoft Vendor
    2023-10-04T02:56:47.66+00:00

    Hi @Boylan, Chris ,

    Welcome to Q&A forum!

    It may be the code has an error in the lookup column reference. The correct syntax for referencing a lookup column is [$LookupColumnName], where LookupColumnName is the internal name of the lookup column. Here's the corrected code:

    {
       "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
       "elmType": "div",
       "style": {
          "background-color": "=if(indexOf([$TrainingCrsLookup].Title, @currentField) == -1, 'red', '')"
       },
       "children": [
          {
             "elmType": "span",
             "txtContent": "@currentField"
          }
       ]
    }
    

    This code will check if the value of the current field exists in the Title column of the WFD Training courses list, which is referenced by the TrainingCrsLookup lookup column in the current list. If the value is not found, the background color of the field will be set to red.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. Yanli Jiang - MSFT 26,761 Reputation points Microsoft Vendor
    2023-10-18T08:53:27.3066667+00:00

    Hi @Boylan, Chris ,

    Thanks for your reply, I think I understand your question. You can try using the following JSON code:

    {
       "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
       "elmType": "div",
       "style": {
          "background-color": "=if(indexOf('[$lookuptest.lookupValue]', @currentField) == -1, 'red', '')"
       },
       "children": [
          {
             "elmType": "span",
             "txtContent": "@currentField"
          }
       ]
    }
    

    This is my testing process:

    1, The lookup column in calendar list:

    User's image

    2, The test result:

    User's image

    Hope this helps.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Boylan, Chris 0 Reputation points
    2023-10-18T13:35:37.1566667+00:00

    The code does not work either Yanli (I realise I've posted in two locations).

    0 comments No comments

  5. Boylan, Chris 0 Reputation points
    2023-10-18T13:35:45.21+00:00

    The code didn't work unfortunately.

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "style": {
        "background-color": "=if(indexOf('[$Lookuptrngcrstitle.Title]', @currentField) == -1, 'red', '')"
      },
      "children": [
        {
          "elmType": "span",
          "txtContent": "@currentField"
        }
      ]
    }
    

    All title field values appear as red backgrounds. I've checked and the internal name for the lookup column is Lookuptrngcrstitle I later edited it to become Lookup trng crs title, neither of these have worked:

        "background-color": "=if(indexOf('[$Lookup trng crs title]', @currentField) == -1, 'red', '')"
    
        "background-color": "=if(indexOf('[$Lookup trng crs title].Title', @currentField) == -1, 'red', '')"
    

    both versions of the logic above make the Title column red background

        "background-color": "=if(indexOf('[$Lookup trng crs title]'.Title, @currentField) == -1, 'red', '')"
    

    logic above makes the Title column appear blank.

    There is probably a (more complex) solution using Flow and an extra column in the calendar. The flow would check for a match and set a new match column as true/false, the colour of the Title field could be determined by that.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.