Forum Discussion
Using the ID column to generate a number in a calculated column.
Hello. I was wondering if I could get some help with an issue I'm currently having. I'm trying to generate a ticket number system based on the ID column for an existing sharepoint list.
The new calculated column is titled "Ticket Number". It is calculated column. The formula used is as follows:
="TT"&[ID]
The formula seems valid, but whenever I go back to the list to see the results, every item in the list's value in the "Ticket Number" column is #VALUE!. In fact, I can't seem to get any expressions to work with a calculated column outside of simple math (addition/subtraction/multiplication on the ID value itself).
Could someone tell me what I'm doing wrong here?
- Make sure your column Data type returned is set to Text?
- It's always set to single line text
- Calculated don't even work for me from ID either right now, just stays blank until I go "save" the column settings again.
- there is a solution here referencing [ID] column in json instead of calculated field:
https://community.powerplatform.com/forums/thread/details/?threadid=6a09f9b6-579b-4529-be32-b557497d88a4Note: JSON formatted columns in SharePoint list ONLY shows/displays the value in column. It does NOT actually store the formatted value in column.
So, you cannot use those formatted column values for sorting, filtering, etc. in list view OR cannot read those values in other applications like Power Automate, Power Apps, Using SharePoint REST APIs, etc.
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 Thank you for pointing this but I was aware of that when I tried to filter it didn't work and realised the limitations. But as of now the requirement is to have a reference field but once the requirement changes we will revert to using a flow to update values - its not ideal but we are in testing phase so there is time to make changes.
