locked
Calculated column using CONCATENATE formula RRS feed

  • Question

  • Hi EveryOne,

    I am facing one issue last 4days.....plzzzzzz help me in this

    when I used the Calculated column formula like 

    =CONCATENATE("0001-",REPT(0,3-LEN(ID+1)),ID+1)....(modified version)

    Output :

    0001-002

    0001-003

    for the first it worked just fine. the numbers were showing on the list.

    However, the next time I open the SharePoint,

    +Add New Item,

    numbers for all items end in 

    0001-002

    0001-003

    0001-001

    0001-001

    and I have to go to the list settings and re-save the formula so that the numbers are shown correctly every time I open the list.

    0001-002

    0001-003

    0001-004

    0001-005

    Is there a way I could fix this issue?

    Regards,

    Viswanath

    Friday, April 12, 2013 4:48 AM

Answers

  • Viswanath,

    For event handler, follow the steps.

    1. Open Visual Studio 2010 & Create SharePoint 2010 Project.

    2. Add new Event Receiver for related SP List. & Select "ItemAdded" Event from pop up checkbox list.

        

    3. Then add code as per below.

    public override void ItemAdded(SPItemEventProperties properties)
           {
               base.ItemAdded(properties);
               if (properties.ListTitle.Equals("ID YEAR LIST NAME"))
               {
                   properties.ListItem["IDYearColumn"] = (properties.ListItemId + 1).ToString("0000") + " - " +
                                                         DateTime.Now.Year.ToString().Substring(2);
                   properties.ListItem.Update();
               }
           }

    Thanks, 


    Manoj | SharePoint

    • Marked as answer by Emir Liu Monday, April 22, 2013 2:03 AM
    Wednesday, April 17, 2013 5:33 AM
  • Hi Viswanath,

    I have tested the code which give you.

    Its working fine at my end.

    Can you check one thing Event Handler attach OR not ? 

    Check using http://speventsmanager.codeplex.com/ 

    Thanks,


    Manoj | SharePoint

    • Marked as answer by Emir Liu Monday, April 22, 2013 2:03 AM
    Thursday, April 18, 2013 5:02 AM

All replies

  • Hi,

    If you need to use the ID in a calculation, the best approach is to do the calculation in a workflow and set a column to the value returned by the calculation.

    IDs can work in calculated columns, they are unreliable, speaking from experience. Now I always use On creation and on change workflows in situations where I absolutely have to use the ID value in some sort of calculated column.

    Just simple create calculated field with only =[ID] & it will return 0 every time but after save field you can see updates so never use ID in calculated column.


    Manoj | SharePoint


    Friday, April 12, 2013 5:17 AM
  • Hi Manoj,

    I am not getting what you are saying...

    i want format like 0001-001, but you are saying

    0

    1

    2 and so on.......

    please give me a clear clarification.

    Regards,

    viswanath

    Friday, April 12, 2013 5:28 AM
  • No, I am telling you that [ID] column is not working proprly in Calculated Field.

    So you have to go for SPD workflow.


    Manoj | SharePoint

    Friday, April 12, 2013 5:54 AM
  • ok manoj ..thanks for replying.
    Friday, April 12, 2013 6:11 AM
  • Hi,

    I have created SPD workflow as per below image. Its working perfecly....

    My custom single line of text update with your format

    "0001-0ID"

    Note: If it will help then Mark as Answer.


    Manoj | SharePoint


    Friday, April 12, 2013 7:33 AM
  • Hi Viswanath,

    Your problem is solved ?

    Thanks,

    Note: Note: If it will help then Mark as Answer.


    Manoj | SharePoint

    Tuesday, April 16, 2013 6:48 AM
  • No Manoj,

    its still going on.

    Tuesday, April 16, 2013 6:52 AM
  • Have you tried with above given SPD screen shot ?

    Becuase its working at my end.

    Let me know if any issue.

    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 6:56 AM
  • Hi Manoj,

    i used so many calculated formulas,still i am getting same error.

    Now i used this formula

    =TEXT(ID,"0000")&"-"&RIGHT(YEAR(DateColumn),2)

    When i used that formula,the output will be like this

    0001-13

    0002-13

    0003-13

    0004-13

    But i am facing one issue last 1week onwards,what it means

    When i am adding new item ,the output should be like this

    0001-13

    0002-13

    0003-13

    0004-13

    0000-13

    0000-13

    0000-13
    and I have to go to the list settings and re-save the formula so that the numbers are shown correctly every time I open the list.

    0001-13

    0002-13

    0003-13

    0004-13

    0005-13

    0006-13

    0007-13

    Is there a way I could fix this issue?

    Please help me in this.

    its very urjent for me.

    Regards,

    Viswanath Reddy

    Tuesday, April 16, 2013 7:06 AM
  • Hi Viswanath,

    As per my knowledge you can't use ID column in calculated field becuase for Value effect you have to update Calculated field so please go for SPD workflow.

    OR

    Event Handler.

    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 7:10 AM
  • Thanks for reply.

    I am the new for Workflow.

    I started creating SPD workflow

    i completed first line like Calculate and i am facing how to give then Set function

    please give me each and every step.

    its helpful for me.

    Regards,

    Viswanath Reddy

    Tuesday, April 16, 2013 7:17 AM
  • Hi Viswanath,

    For your info,  please see Office.Microsoft.com site's referance link http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx

    and go to "Using column references in a formulasection's NOTE.

     NOTES 

    • You cannot reference a value in a row other than the current row.
    • You cannot reference a value in another list or library.
    • You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
    • You cannot reference another column in a formula that creates a default value for a column.

    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 7:21 AM
  • Hi Manoj,

    Thanks for reply.

    Now tell me how to create that workflow.

    You given me screen shot but i am not getting that one.

    Thanks

    Tuesday, April 16, 2013 7:28 AM
  • Hi Viswanath,

    Please follow the steps as below.

    1. Open SPD 2010 & open your site collection in which you want to create workflow.

    2. Now open your List from left section "Lists and Libraries".

    3. Then Create List Workflow as per below image.

            

    4. Then type the Name of the workflow.

    5. Then set Workflow Start Option as per below image "Start workflow automatically when new item created".

        

    6. Finally add all required "Actions" & "Conditions" from Ribbon. 

    7. Then configure all Actions & Conditions as per my previous post.

    Now Publish your workflow from Ribbon.

    Then create new item & check your column.

    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 7:45 AM
  • Hi Manoj,

    Thanks for saying like this.

    Just tell me what type of changes done into my requirement.

    In which place i have to change ID and Year.

    Please help me in this.

    Thanks

    Tuesday, April 16, 2013 8:55 AM
  • For year, you have to create one calculated column = YEAR(Today) and assign this field in SPD workflow variable.

    Then for ID just see IF ELSE condition in older screen shot.

    Then concat both Variable.

    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 9:13 AM
  • Hi Manoj,

    what is the formula for calculated column.

    i am giving =YEAR(Today),it is throwing error.

    please explain step by step.

    Thanks,

    Viswanath Reddy

    Tuesday, April 16, 2013 9:34 AM
  • Hi,

    Please create 2 columns as below.

    Date Column


    Year Column


    Thanks,


    Manoj | SharePoint

    Tuesday, April 16, 2013 10:13 AM
  • Hi Manoj,

    I tried every thing what you told me on yesterday, after creating every thing its not working.

    Regards this you have any idea about Event Receiver.

    If you know that please help me.

    Thanks

    Viswanath Reddy

    Wednesday, April 17, 2013 4:36 AM
  • Viswanath,

    For event handler, follow the steps.

    1. Open Visual Studio 2010 & Create SharePoint 2010 Project.

    2. Add new Event Receiver for related SP List. & Select "ItemAdded" Event from pop up checkbox list.

        

    3. Then add code as per below.

    public override void ItemAdded(SPItemEventProperties properties)
           {
               base.ItemAdded(properties);
               if (properties.ListTitle.Equals("ID YEAR LIST NAME"))
               {
                   properties.ListItem["IDYearColumn"] = (properties.ListItemId + 1).ToString("0000") + " - " +
                                                         DateTime.Now.Year.ToString().Substring(2);
                   properties.ListItem.Update();
               }
           }

    Thanks, 


    Manoj | SharePoint

    • Marked as answer by Emir Liu Monday, April 22, 2013 2:03 AM
    Wednesday, April 17, 2013 5:33 AM
  • Hi Manoj,

    Thanks for reply.

    Here what i done means

     if (properties.ListTitle.Equals("My list Name"))

    properties.ListItem["My column Name"]

    I Deployed solution successfully.

    When i am going to add new item,after adding new item its not working.

    Please tell me how to done successfully.

    Regards

    Viswanath

    Wednesday, April 17, 2013 10:05 AM
  • Have you implmented my above Item Added code ?

    Please refersh list after add new Item.


    Manoj | SharePoint

    Wednesday, April 17, 2013 10:25 AM
  • Hi Sanjay,

    i implemented what you given in the above post.

    just tell me what type of column we have to create, if it is a Calculated column means what type of formula i want to use ..please give me step by step.

    Thanq

    Viswanath.


    Wednesday, April 17, 2013 11:58 AM
  • Just create "Single Line of text" for "IDYEARColumn".


    Manoj | SharePoint

    Wednesday, April 17, 2013 12:13 PM
  • Hi Manoj,

    Thanks for reply.

    I am the only one developer in my office.

    I created single line of text what you told me on yesterday, but its not working.

    Please check it once in you end and help me in this.

    Regards,

    Viswanath.

    Thursday, April 18, 2013 4:12 AM
  • Hi Viswanath,

    I have tested the code which give you.

    Its working fine at my end.

    Can you check one thing Event Handler attach OR not ? 

    Check using http://speventsmanager.codeplex.com/ 

    Thanks,


    Manoj | SharePoint

    • Marked as answer by Emir Liu Monday, April 22, 2013 2:03 AM
    Thursday, April 18, 2013 5:02 AM
  • Hi,

    I have same type of requirement 0001-17IND.

    17 is current year and IND is Country.

    Help me on this
    • Edited by Rajesh0929 Wednesday, May 24, 2017 1:22 PM
    Wednesday, May 24, 2017 1:19 PM