none
SharePoint List Calculated column RRS feed

  • Question

  • Hello,

    Can you please help me check why the formula gets altered after adding 2nd item on the list in sharepoint office 365.

    I have a column that utilize calculated value. the formula is ="CCR-00" & ID. The value should be CCR-001, CCR-002 and so forth after every entry.

    The problem is, after 2nd item is entered it only shows CCR-00 without the appended ID of the item... and whenever I check the formula it always revert back to ="CCR-00"&ID. The space between ampersand and the "ID" column is removed by the system,, I tried enclosing the ID in [] however it still revert back to the previous formula that has problem when another item is entered. please check.

    Friday, November 15, 2019 10:50 AM

All replies

  • FYI

    https://blog.csdn.net/FoxDave/article/details/99674784

    There are several methods for building the increased id. Hope this can help.


    Justin Liu Office Apps & Services MVP, MCSE
    Senior Software Engineer
    Learn Microsoft 365 from Microsoft DOCs now!
    Please Vote and Mark as Answer if it helps you.

    Monday, November 18, 2019 1:00 AM
  • Hi Albert,

    ID value is replaced only after the item is created.

    ID is not valid column to use in Calculated Formulas, it is not displayed in the available columns for a reason.

    1.It is 0 for newly created items as it gets its value from the database after all Formulas are evaluated

    2.You get the correct values (in all existing items) if you re-edit the Formula

    3.It is reset to 0 on Item update.

    As a workaround, you can get hold of that ID in a Workflow and save it to another Column.

    You can use an simple workflow

    1.Create a column in your list, for example testID

    2.Create a workflow

    Set item id to current item testID

    3.Then you can use Formulas in the calculated column

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Proposed as answer by Michael Han6 Tuesday, November 19, 2019 9:12 AM
    Monday, November 18, 2019 6:36 AM
  • Hi

    I am checking your situation here.

    Any progress on your troubleshooting?

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, November 22, 2019 9:03 AM