May 21 2021 04:20 AM
HI all,
Just wondering if anyone knows how/if this can be done. I'm trying to format a text column in SharePoint using JSON. I am trying to format the column to be a formatted ID in the following format: REQ000001 using the ID column. So essentially what I'd like to do is something like this (see bold):
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "a",
"txtContent": "REQ" & (Format the [ID} column with 5 leading zeros),
Not sure if this is possible, but if anybody has advice, I'd greatly appreciate it.
Cheers,
Troy
May 21 2021 11:52 AM
Solution@Teeroy_Lyon Yes, you can do this using JSON formatting like below:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "='REQ' + if([$ID] < 10, '00000', if([$ID] < 100, '0000', if([$ID] < 1000, '000', if([$ID] < 10000, '00',if([$ID] < 100000, '0', ''))))) + [$ID]"
}
Output:
Note: You need to adjust the leading zeros using nested if conditions based on your requirements.
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.
May 21 2021 12:03 PM
Hey @ganeshsanap,
That's awesome and a pretty cool solution. I'll give that a try!
Cheers, and thanks,
Troy
May 23 2021 12:48 AM
@Teeroy_Lyon You're welcome.
Try it & let me know if it works for you or if you face any issue while using the JSON.
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.
May 27 2021 04:13 AM
@ganeshsanap , this worked perfectly...thanks for the guidance!
May 21 2021 11:52 AM
Solution@Teeroy_Lyon Yes, you can do this using JSON formatting like below:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "='REQ' + if([$ID] < 10, '00000', if([$ID] < 100, '0000', if([$ID] < 1000, '000', if([$ID] < 10000, '00',if([$ID] < 100000, '0', ''))))) + [$ID]"
}
Output:
Note: You need to adjust the leading zeros using nested if conditions based on your requirements.
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.