topic Re: Year in Computed Field for autonumbering in Fusion Manage Forum

Year in Computed Field for autonumbering

Anonymous — Tue, 29 Jan 2013 06:32:45 GMT

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

Re: Year in Computed Field for autonumbering

Anonymous — Tue, 29 Jan 2013 14:56:38 GMT

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).

Re: Year in Computed Field for autonumbering

PringaultJ — Wed, 20 Feb 2013 16:10:01 GMT

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

Jeremy PRINGAULT
EMEA Business Consultant
Autodesk

Re: Year in Computed Field for autonumbering

Anonymous — Wed, 20 Feb 2013 16:21:40 GMT

Jeremy,

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

Re: Year in Computed Field for autonumbering

Anonymous — Mon, 25 Feb 2013 02:26:56 GMT

Hi Jeremy,

Thank you for the solution, really appreciate it.

Best regards,

Carol Mak

Re: Year in Computed Field for autonumbering

Anonymous — Wed, 12 Jun 2013 15:02:09 GMT

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

Re: Year in Computed Field for autonumbering

bastien.mazeran — Thu, 13 Jun 2013 22:10:53 GMT

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,

Re: Year in Computed Field for autonumbering

Anonymous — Thu, 13 Jun 2013 22:36:38 GMT

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

Re: Year in Computed Field for autonumbering

bastien.mazeran — Fri, 14 Jun 2013 18:05:34 GMT

Hi Elias,

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

Regards,

Re: Year in Computed Field for autonumbering

Anonymous — Fri, 14 Jun 2013 18:11:07 GMT

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.

Re: Year in Computed Field for autonumbering

bastien.mazeran — Fri, 14 Jun 2013 18:40:59 GMT

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)))

Re: Year in Computed Field for autonumbering

Anonymous — Fri, 14 Jun 2013 18:44:21 GMT

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

Re: Year in Computed Field for autonumbering

bastien.mazeran — Fri, 14 Jun 2013 19:15:44 GMT

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)))

Re: Year in Computed Field for autonumbering

Anonymous — Fri, 14 Jun 2013 19:41:33 GMT

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?

Re: Year in Computed Field for autonumbering

bastien.mazeran — Fri, 14 Jun 2013 20:23:59 GMT

Yes you can achieve this by using the DATEPART function.

DATEPART(YEAR,GETDATE())

will return 2013.

Re: Year in Computed Field for autonumbering

Anonymous — Fri, 14 Jun 2013 20:37:14 GMT

Thank you that worked

Re: Year in Computed Field for autonumbering

Anonymous — Tue, 11 Nov 2014 07:43:48 GMT

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

Re: Year in Computed Field for autonumbering

bastien.mazeran — Tue, 11 Nov 2014 17:54:15 GMT

Hi Carol,

Please try this new formula:

date_part('year', current_date)

Regards,

Re: Year in Computed Field for autonumbering

jared.sund — Wed, 07 Jan 2015 00:04:31 GMT

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,

Re: Year in Computed Field for autonumbering

Anonymous — Wed, 07 Jan 2015 08:16:02 GMT

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