SOLVED

JSON format question

%3CLINGO-SUB%20id%3D%22lingo-sub-2375366%22%20slang%3D%22en-US%22%3EJSON%20format%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375366%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3Ball%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%26nbsp%3Bwondering%26nbsp%3Bif%26nbsp%3Banyone%26nbsp%3Bknows%26nbsp%3Bhow%2Fif%26nbsp%3Bthis%26nbsp%3Bcan%26nbsp%3Bbe%26nbsp%3Bdone.%26nbsp%3B%26nbsp%3BI'm%26nbsp%3Btrying%26nbsp%3Bto%26nbsp%3Bformat%26nbsp%3Ba%26nbsp%3Btext%26nbsp%3Bcolumn%26nbsp%3Bin%26nbsp%3BSharePoint%26nbsp%3Busing%26nbsp%3BJSON.%26nbsp%3B%26nbsp%3B%26nbsp%3BI%26nbsp%3Bam%26nbsp%3Btrying%26nbsp%3Bto%26nbsp%3Bformat%26nbsp%3Bthe%20column%26nbsp%3Bto%26nbsp%3Bbe%26nbsp%3Ba%26nbsp%3Bformatted%26nbsp%3BID%26nbsp%3Bin%26nbsp%3Bthe%26nbsp%3Bfollowing%26nbsp%3Bformat%3A%26nbsp%3BREQ000001%26nbsp%3Busing%26nbsp%3Bthe%26nbsp%3BID%26nbsp%3Bcolumn.%26nbsp%3B%26nbsp%3BSo%26nbsp%3Bessentially%26nbsp%3Bwhat%26nbsp%3BI'd%26nbsp%3Blike%26nbsp%3Bto%26nbsp%3Bdo%26nbsp%3Bis%26nbsp%3Bsomething%26nbsp%3Blike%26nbsp%3Bthis%26nbsp%3B(see%26nbsp%3Bbold)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CP%3E%7B%20%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%2C%3C%2FP%3E%3CP%3E%22elmType%22%3A%20%22a%22%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22txtContent%22%3A%20%22REQ%22%26nbsp%3B%26amp%3B%26nbsp%3B(Format%26nbsp%3Bthe%26nbsp%3B%5BID%7D%26nbsp%3Bcolumn%26nbsp%3Bwith%26nbsp%3B5%26nbsp%3Bleading%26nbsp%3Bzeros)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%26nbsp%3Bsure%26nbsp%3Bif%26nbsp%3Bthis%26nbsp%3Bis%26nbsp%3Bpossible%2C%26nbsp%3Bbut%26nbsp%3Bif%26nbsp%3Banybody%26nbsp%3Bhas%26nbsp%3Badvice%2C%26nbsp%3BI'd%26nbsp%3Bgreatly%26nbsp%3Bappreciate%26nbsp%3Bit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ETroy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2375366%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDocument%20Library%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375816%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20format%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059913%22%20target%3D%22_blank%22%3E%40Teeroy_Lyon%3C%2FA%3E%26nbsp%3BI%20don't%20think%20this%20is%20possible%20with%20JSON%2C%20not%20least%20because%20it%20would%20need%20to%20adjust%20the%20number%20of%20zeros%20based%20on%20the%20ID%20and%20you%20can't%20have%20leading%20zeros%20in%20the%20ID.%20But%20if%20you%20create%20a%20new%20single%20of%20text%20column%20it%20is%20possible%20to%20populate%20it%20in%20the%20way%20you%20want%20via%20a%20flow%20in%20Power%20Automate.%20I%20answered%20almost%20exactly%20the%20same%20question%20early%20last%20year%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsharepoint%2Fauto-increment-number-field-in-a-sharepoint-list%2Fm-p%2F1145521%2Fhighlight%2Ftrue%23M38028%22%20target%3D%22_blank%22%3Ehere%3C%2FA%3E%20which%20I%20hope%20you%20find%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376625%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20format%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059913%22%20target%3D%22_blank%22%3E%40Teeroy_Lyon%3C%2FA%3E%26nbsp%3BYes%2C%20you%20can%20do%20this%20using%20JSON%20formatting%20like%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%3D'REQ'%20%2B%20if(%5B%24ID%5D%20%26lt%3B%2010%2C%20'00000'%2C%20if(%5B%24ID%5D%20%26lt%3B%20100%2C%20'0000'%2C%20if(%5B%24ID%5D%20%26lt%3B%201000%2C%20'000'%2C%20if(%5B%24ID%5D%20%26lt%3B%2010000%2C%20'00'%2Cif(%5B%24ID%5D%20%26lt%3B%20100000%2C%20'0'%2C%20'')))))%20%2B%20%5B%24ID%5D%22%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOutput%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ganeshsanap_0-1621623028015.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282715iBF5DCF765F71364A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ganeshsanap_0-1621623028015.png%22%20alt%3D%22ganeshsanap_0-1621623028015.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3C%2FSTRONG%3E%3A%20You%20need%20to%20adjust%20the%20leading%20zeros%20using%20nested%26nbsp%3B%3CSTRONG%3Eif%3C%2FSTRONG%3E%20conditions%20based%20on%20your%20requirements.%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%26nbsp%3B%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%26nbsp%3B%26amp%3B%20%3CSTRONG%3ELike%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376661%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20format%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376661%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20awesome%20and%20a%20pretty%20cool%20solution.%26nbsp%3B%20I'll%20give%20that%26nbsp%3B%20a%20try!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%20and%20thanks%2C%3C%2FP%3E%3CP%3ETroy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378746%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20format%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059913%22%20target%3D%22_blank%22%3E%40Teeroy_Lyon%3C%2FA%3E%26nbsp%3BYou're%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20it%20%26amp%3B%20let%20me%20know%20if%20it%20works%20for%20you%20or%20if%20you%20face%20any%20issue%20while%20using%20the%20JSON.%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20%26amp%3B%20%3CSTRONG%3ELike%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies
best response confirmed by Teeroy_Lyon (New Contributor)
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:

ganeshsanap_0-1621623028015.png

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.

Hey @ganeshsanap,

 

That's awesome and a pretty cool solution.  I'll give that  a try!

 

Cheers, and thanks,

Troy 

@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.

@ganeshsanap , this worked perfectly...thanks for the guidance!