Year in Computed Field for autonumbering

[Anonymous]

Hi Support Officer,

I am currently setting up a new workspace and I need to make use of the computed field for auto numbering. I need the last 2 digit of the year to appear in between my auto numbering. With the script below, I managed to get MPCR000001 but I really would like the number to be MPCRYY0001 instead. The YY represent the current year and changes whenever a new year arrived.For year 2013, the autonumber will look like this MPCR130001.

('MPCR' + dbo.PAD_ZERO(6, convert(varchar,REQUEST_NUMBER__AUTO_INC)))

Is there a way to do this? Really appreciate your help and advice.

Many thanks.

Carol

[Anonymous · Accepted solution]

Carol,

Given the limited nature of SQL scripting ( SQL scripting is for all intents and purposes limited for math calculations with in the computed field), this would not be the best way of doing it.

I would suggest using an “on creation script” using JAVASCRIPT (which is much more flexible).

[PringaultJ]

Carol, Joe, 

What you can do on the Field is to put that :

('MPCR' + dbo.PAD_ZERO(6, convert(varchar,ID__AUTO_INC)) + CAST(CONVERT (varchar, GETDATE(),101) as CHAR(10)))

And for sure you will be abel to play with the convert substring ... function available on SQL

Regards & hope this help

[Anonymous]

Jeremy,

Good call, didn't think of it that way.

[Anonymous]

Hi Jeremy,

Thank you for the solution, really appreciate it.

Best regards,

Carol Mak

[Anonymous]

Hi I would like to have the same thing.

I copied this: ('MPCR' + dbo.PAD_ZERO(6, convert(varchar,ID__AUTO_INC)) + CAST(CONVERT (varchar, GETDATE(),101) as CHAR(10))) on the computed field formula and it said that "Modifications to the computed field formula could not be saved. Check your formula syntax and try again." The data type is Auto Number

Can you help me and see what is wrong?

Thanks

[bastien.mazeran]

Hi Elias,

The value

('MPCR' + dbo.PAD_ZERO(6, convert(varchar,ID__AUTO_INC)) + CAST(CONVERT (varchar, GETDATE(),101) as CHAR(10)))

when entered and saved in my auto number computed field works without error for me.

Are you using a different default date format under general display settings? My current default is MM/dd/yyyy.

Regards,

[Anonymous]

Hi
I have the same date format that you have.
Maybe is the part where it says exclude weekends from duration.

[bastien.mazeran]

Hi Elias,

Could you please try on a laptop instead of an iPad?

Regards,

[Anonymous]

I did it in a laptop and it didn't work. I reproduced it in an iPad because I didn't have a laptop at that moment and I wanted to show you the print screen.

[bastien.mazeran]

Thank you for the additional information. I cannot reproduce it using exact same formula you are using.

Could you please try to change formula to?

('PR' + dbo.PAD_ZERO(6, convert(varchar,ID__AUTO_INC)))

[Anonymous]

This is how I have it right now. It's working fine but is not the way I wanted.

[bastien.mazeran]

Please try this one (note the number after GETDATE has been changed from 101 to 112)

('PR' + dbo.PAD_ZERO(6, convert(varchar,ID__AUTO_INC)) + CAST(CONVERT (varchar, GETDATE(),112) as CHAR(10)))

[Anonymous]

Hi Bastien

I tried ('PR' + dbo.PAD_ZERO(6, convert(varchar,NUMBER__AUTO_INC)) + ' - ' + CAST(CONVERT (varchar, GETDATE(),101) as CHAR(10))) and I got it right

I'm getting it like this PR000001 - 06/14/2013

Is there a way that I can get only the year?

[bastien.mazeran]

Yes you can achieve this by using the DATEPART function.

DATEPART(YEAR,GETDATE())

will return 2013.

[Anonymous]

Thank you that worked

[Anonymous]

Hi,

Why is this solution no longer working anymore? Anybody have any idea? 

I keep having this message that my request cannot be process when I use the formula in my newly setup workspace?

May I know, can someone provide some help or advise?

Thank you.

Best regards,

Carol 

[bastien.mazeran]

Hi Carol,

Please try this new formula:

date_part('year', current_date)

Regards,

[jared.sund]

The appropriate computed field syntax for this solution would be as follows:

AUTONUMBER('MPCR' || to_char(current_date, 'YY'), ITEM_NUMBER__AUTO_INC, 4)

However..., this will always display the current date's 2 digit year format.  So, if the current year is 2015, then 15 will display.  When the year is 2016, then 16 will display...   Aside from the incremented number, auto number fields are computed fields.  This means that their values are computed when displayed.  So, if you ask for the current_date you will always be computing the current date...  

So, the above solution will always displays the current year's 2 digit value in your auto number.

The correct way to implement this solution would be with an Action Script tied to an oncreate behavior.

Here's an example of an action script to implement the auto number as requested:

var mySeq = Sequencer.get('mpcr'); //gets existing or creates a new seq as needed.
var today = new Date(); // get today's date

//create a new item number in the following format: 'MPCR'YYXXXX
var itemNumber = 'MPCR' + today.getFullYear().toString().slice(-2) + ('0000' + mySeq.nextValue()).slice(-4);

//field to which you want to store the new auto number in
item.ITEM_NUMBER_FIELD = itemNumber;

I hope this helps, 

[Anonymous]

Hi Jared,

Thank you for the scripts. May I know, other than having the last 2 digit of the year place in the first 2 place, is there a way to place the work week into the 3rd and 4th place. In addition, will it possible to reset the running number to start with 0001 again whenever a new year start?

Really appreciate if you are able to help or advice on the above.

Many many thanks.

Best regards,

Carol Mak