Solved

SharePoint Calculated Field, Fixed Digits, Combined/Calculated Column

Posted on 2016-08-02
2
17 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
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Work Over Net is a new and very powerful collaboration product. With its new easy interface it is becoming very competitive to other similar products like webex and office interactive. WON 2010 have the standard business tools needed for multi-offic…
There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now