none
Need help on caluculated column or workflow anything will work RRS feed

  • Question

  • Hello All, 

    I have these columns 

    Date (Date)
    Community (Choice) Example: Corporate Responsible / Operation Management
    CR (Choice) C/R 
    Gri ID( Calculated)

    This gri Id should give me the following

    RI-(Community Acron)/CR/Year from Date-Auto number 
    Example (RI-OM/C/2017-001. Fist RI Should be always RI

    Any suggestion would be really helpful

    Tuesday, April 18, 2017 9:06 PM

Answers

  • Kundan,

    Yea, the difficult part is the auto increment number. As Romeo said, that is usually handled by a separate list that maintains the latest number and a workflow that populates current list with that value, then increments the value in the separate list.

    However, for the other part of the concatenation, assuming the community column has two words (Operations Management) and you want the first letter of each word (OM), then this calculation will get you the "RI-OM/C/2017-"

    ="RI-"&LEFT(Community,1)&MID([Community],FIND(" ",[Community])+1,1)&"/"&CR&"/"&YEAR(DATEX)&"-"


    Betty Stolwyk

    • Marked as answer by Kundan Ghimire Wednesday, April 19, 2017 6:03 PM
    Tuesday, April 18, 2017 10:26 PM

All replies

  • Kundan,

    Where does the community acronym come from?  Does the column content contain "Operation Management" or "OM"?

    Betty


    Betty Stolwyk

    Tuesday, April 18, 2017 9:25 PM
  • Hi

    I recommend you to use workflow ( you can create autonumber using calculated columns )

    Create a secondary list  with items for eacy year. In that list add a new column, the current number ( the auto-n umber )

    each time a new item is added to the main list , the workflow will start , and will check in secondary list for current year, will use the current umber from there. Using these value will update column in main list, and will increment by one and update secondary list field

    Let me know if you need more details


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Tuesday, April 18, 2017 10:10 PM
  • Kundan,

    Yea, the difficult part is the auto increment number. As Romeo said, that is usually handled by a separate list that maintains the latest number and a workflow that populates current list with that value, then increments the value in the separate list.

    However, for the other part of the concatenation, assuming the community column has two words (Operations Management) and you want the first letter of each word (OM), then this calculation will get you the "RI-OM/C/2017-"

    ="RI-"&LEFT(Community,1)&MID([Community],FIND(" ",[Community])+1,1)&"/"&CR&"/"&YEAR(DATEX)&"-"


    Betty Stolwyk

    • Marked as answer by Kundan Ghimire Wednesday, April 19, 2017 6:03 PM
    Tuesday, April 18, 2017 10:26 PM
  • Hello Betty, 

    This is not working. It says something went wrong. Just for clearfication I am on SharePoint Online. Can you please check advise

    Thanks

    • Marked as answer by Kundan Ghimire Wednesday, April 19, 2017 6:03 PM
    • Unmarked as answer by Kundan Ghimire Wednesday, April 19, 2017 6:03 PM
    Wednesday, April 19, 2017 5:54 PM
  • Romeo, 

    Sorry but i did not get what am I doing here. Can you please advise from what should I do to get autonumber

    Thanks

    Wednesday, April 19, 2017 5:54 PM
  • Thanks I got it working now
    Wednesday, April 19, 2017 6:03 PM
  • Hi

    please check this link with multiple solutions - regarding my solution read Russel's answer

    https://sharepoint.stackexchange.com/questions/88340/how-to-auto-populate-a-list-column-with-a-sequential-number


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, April 19, 2017 9:59 PM
  • Kundan,

    Did you get the concatenation working yet?  If not, please send me the calculation and a sample value of "Date", "Community" and "CR".

    Betty


    Betty Stolwyk

    Thursday, April 20, 2017 3:33 PM
  • Thanks Betty the concatnetation part work well. Just wondering if you can guide me to auto increment number

    Kundan

    Thursday, April 20, 2017 5:27 PM
  • Just another , what will happen if the user add 3 word in community or 4 words?
    Thursday, April 20, 2017 5:29 PM
  • Let me think about it.  This might be getting more complicated than what calculated fields can support, but I'll see what I can come up with.  If there are 3 or 4 words in the field, do you want the first character of each field?  Please give me an example of a 3 worded value and a 4 worded value and what you want for the output.

    Betty Stolwyk

    Tuesday, April 25, 2017 4:15 AM
  • Kundan,

    This will get you first initials of up to three words:

    ="RI-"&LEFT(Community,1)&(MID(Community,FIND(" ",Community)+1,1))&IF(ISERROR(MID(Community,(FIND(" ",Community,FIND(" ",Community)+1)+1),1)),"",(MID(Community,(FIND(" ",Community,FIND(" ",Community)+1)+1),1)))&"/"&CR&"/"&YEAR(DATEX)&"-"

    next challenge is four words!


    Betty Stolwyk

    Saturday, April 29, 2017 12:13 AM
  • Kundan,

    See if this article helps with the auto increment solution (a workflow that references a separate list with only one item which contains the next sequential number to be used.)

    https://dlairman.wordpress.com/2011/01/10/add-a-unique-auto-incrementing-column-to-a-sharepoint-list/

    I like their calculated column solution to convert a number to six digits with leading zeros. 

    Assume the secondary list has a field "NextNumber" and a field "FormattedNextNumber" which is a calculated field with this definition:

    LEFT("000000",6-(LEN(TEXT(NextNumber,"0"))))&TEXT(NextNumber,"0")

    so if the field NextNumber is the number '1', FormattedNextNumber would be '000001'.  NextNumber '18' would become FormattedNextNumber" '000018'. NextNumber '324' would become FormattedNextNumber '000324', etc. It looks like you only need three digits, so you would just change the '6' to a '3'.


    Betty Stolwyk

    Thursday, May 18, 2017 5:14 PM