Calculated column in Sharepoint List loses changes to formula

[jonengland]

I'm trying to use the built in ID column in the Sharepoint List to add to a "Device ID" for an IT inventory list. My idea is to take the row "ID" number and add a couple of zeros in a new Column called "LineID" and then use the result in a DeviceID column which also takes other information from the Sharepoint List to create the DeviceID.

However i have found some weird behaviour - I'm not sure if my code is being changed (because it's wrong - i wouldn't be surprised) or if it's not being saved properly.

If I create a new record (using a flow in Power Automomate) I get this - look at the bottom of the grab:

see the zeros under LineID?

The current formula in LineID is this:

If i change it to to this:

and resubmit the Power Automate:

so it fixes it - and the previous incorrect records.

Checking back on the formula - it has reverted back to the pre-edit.

and then resubmitting again:

I've also created a new LineIDv2 column and the behaviour is the same.

I also have code which creates the string in the Device ID which should include the LineID at the end e.g. LHQW062 but this isn't working either - when it used to... so confused right now!

=IF(Item="Laptop","L",IF(Item="Desktop","D",IF(Item="Server","S",IF(Item="Printer","P",IF(Item="iPhone","iPh",IF(Item="iPad","iPD",IF(Item="Android","Droid")))))))&""&IF(Location="Harpenden Office","HQ",IF(Location="Home Office","WfH"))&""&IF([Computer OS]="Windows","W",IF([Computer OS]="MacOS","M",IF([Computer OS]="Linux","L",IF([Computer OS]="N/A","-")))&""&LineID)

Any advice welcome!

Thanks,

Jon

[#2 · Accepted solution]

@jonengland 

Using ID column in SharePoint calculated column is not supported by Microsoft offcially.

However, if it is returning correct ID in calculated column for you, try using this formula for LineID column: 

="00"&[ID]

Wrap 00 in double quotes.

---

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.

[#3]

@ganeshsanap Thanks, i tried it - and it behaves as before - the square brackets disappear after submitting a new line.

I guess that if using ID in a Calculated Column is not supported, i need to find a different way. I'm not great at programming - do you have any suggestions?

[#4]

@jonengland This is a default behavior in SharePoint calculated formulas to remove the square brackets for the columns having 1 word name (Example: Title, ID, Created, DeviceID etc.) after saving the formula.

Square brackets are required only when you have more than 1 words in column name or when you have space in column name (Example: [My Column 1], [Due Date], [Email Address]).

---

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.

[#5]

Thanks for your help on this @ganeshsanap - It's looks as though Microsoft not supporting the ID column's use in Calculated Columns is causing my issue here. Thanks for that info!

[#6]

Thanks for the explainer on square brackets @ganeshsanap - very helpful!