Solved

SharePoint Calculated Field, Fixed Digits, Combined/Calculated Column

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 6 26
sharepoint 2013 calendar change like sharepoing blog formate day above month 1 22
SharePoint Online/365 with SAP API 2 68
Citrix sharefile 1 55
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…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

24 Experts available now in Live!

Get 1:1 Help Now