Solved

SharePoint Calculated Field, Fixed Digits, Combined/Calculated Column

Posted on 2016-08-02
2
41 Views
Last Modified: 2016-08-04
I want to use the ID field to create an ID number consisting of prefix, and combining one field [year] with the default [ID] field.

I only have SharePoint 2010 design capabilities, so I'm not able to create workflow to auto-generate numbers or install additional software. Is there a way to restart the auto generated ID when there is a new year. For example, if I have two items, can I view them as below in the same list?

ITEM1-2005-0001
ITEM1-2006-0001
ITEM1-2007-0001

With a max of 5 years per list?

The issue that I'm having is that I want the generated new ID at the end to be only 4 digits and not 5 digits. Also, I want the numbering to restart if there is a different year.

Below is the formula that I'm using. When it gets to the number 10, it moves to 5 digits; how do I keep only 4 digits?

="PREFIX"&"-"&[YEAR]&"-"&"000"&[ID]

My Current Results --

PREFIX-2016-0002
PREFIX-2016-0003
PREFIX-2016-0004
PREFIX-2016-0005
PREFIX-2016-0006
PREFIX-2016-0007
PREFIX-2016-0008
PREFIX-2014-0009
PREFIX-2007-00010
PREFIX-2010-00011
PREFIX-2007-00012
PREFIX-2016-00013
PREFIX-2016-00014

These are the results that I would like to see
PREFIX-2007-0001
PREFIX-2007-0002
PREFIX-2010-0001
PREFIX-2014-0001
PREFIX-2016-0001
PREFIX-2016-0002
PREFIX-2016-0003
PREFIX-2016-0004
PREFIX-2016-0005
PREFIX-2016-0006
PREFIX-2016-0007
PREFIX-2016-0008
PREFIX-2016-0009
0
Comment
Question by:ckwillGWU
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 41742254
Try:

=CONCATENATE("PREFIX-"&[YEAR]&"-",REPT("0",MAX(0,4-LEN(ID))),ID)
0
 

Author Closing Comment

by:ckwillGWU
ID: 41742972
Thank you Mvasilevsky! -- I believe that worked perfectly without having workflow rights.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When installing SharePoint 2010 RTM I came across a strange error, I was getting timeouts during the installation. I searched the web and found the best solution to be found here (http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010genera…
Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question