Issue in column formatting in SharePoint JSON with If statement

Question

I have a simple JSON formatting code for a column in SharePoint in order to redirect to the appropriate target depending on the content of the record.

Here is the code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "@currentField",
  "attributes": {
        "href": "=IF(LEFT(@currentField,3)=='INC', 'https://domain.service-now.com/nav_to.do?uri=incident.do?sysparm_query=number=' + @currentField, IF(LEFT(@currentField,3)=='PRB', 'https://domain.service-now.com/nav_to.do?uri=problem.do?sysparm_query=number=' + @currentField, 'https://domain.atlassian.net/jira/software/c/projects/CCE/issues/' + @currentField))",
"target": "_blank"
  }
}

The format is accepted by SharePoint, but the output is blank. So I'm a bit stunned.

And the simple code is working like a charm:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "@currentField",
  "attributes": {
    "target": "_blank",
    "href": "='https://domain.service-now.com/nav_to.do?uri=incident.do?sysparm_query=number='+ @currentField"
  }
}

Thanks in advance for any help on this.

Try JSON given in my answer below. Let me know if it works for you or if you face any issues while using it. — Ganesh Sanap - MVP
– Ganesh Sanap - MVP, Commented Nov 29, 2023 at 18:49

Ganesh Sanap - MVP · Accepted Answer · 2023-11-29 18:46:18Z

0

LEFT() function is from SharePoint calculated column formulas which is not supported in SharePoint JSON formatting.

Use below JSON column formatting:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "@currentField",
  "attributes": {
    "href": "=if(substring(@currentField,0,3)=='INC', 'https://domain.service-now.com/nav_to.do?uri=incident.do?sysparm_query=number=' + @currentField, if(substring(@currentField,0,3)=='PRB', 'https://domain.service-now.com/nav_to.do?uri=problem.do?sysparm_query=number=' + @currentField, 'https://domain.atlassian.net/jira/software/c/projects/CCE/issues/' + @currentField))",
    "target": "_blank"
  }
}

You can also refer column values in JSON in the format [$MyColumnName] instead of @currentField. Where MyColumnName is the internal name of your SharePoint list column.

You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?

Output:

answered Nov 29, 2023 at 18:46

Ganesh Sanap - MVP

47.4k22 gold badges32 silver badges64 bronze badges

You can use either substring or startsWith operator for such requirements.

Ganesh Sanap - MVP
– Ganesh Sanap - MVP

2023-11-29 18:49:06 +00:00
Commented Nov 29, 2023 at 18:49
1

Waouh. you're awesome. SO powerful § it works like a charm. Thank you so much !

Brucks
– Brucks

2023-11-30 19:55:00 +00:00
Commented Nov 30, 2023 at 19:55
You are welcome, I am glad it worked for you! Keep using this platform to ask any questions related to SharePoint.

Ganesh Sanap - MVP
– Ganesh Sanap - MVP

2023-12-01 04:08:26 +00:00
Commented Dec 1, 2023 at 4:08

Add a comment |

Stack Exchange Network

Issue in column formatting in SharePoint JSON with If statement

1 Answer 1

Your Answer

Hot Network Questions

Issue in column formatting in SharePoint JSON with If statement

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions