Fusion Manage Forum
Welcome to Autodesk’s Fusion Manage (formerly Fusion 360 Manage) Forum. Share your knowledge, ask questions, and explore popular Fusion Manage topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Year in Computed Field for autonumbering

21 REPLIES 21
SOLVED
Reply
Message 1 of 22
MJ-Carol
1094 Views, 21 Replies

Year in Computed Field for autonumbering

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

Tags (1)
21 REPLIES 21
Message 2 of 22
jpiggee
in reply to: MJ-Carol

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

Joseph Piggee
Fusion 360 Administrator
TPI Composites
jpiggee@tpicomposites.com
Message 3 of 22
PringaultJ
in reply to: MJ-Carol

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
Message 4 of 22
jpiggee
in reply to: PringaultJ

Jeremy,

 

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

Joseph Piggee
Fusion 360 Administrator
TPI Composites
jpiggee@tpicomposites.com
Message 5 of 22
MJ-Carol
in reply to: PringaultJ

Hi Jeremy,

 

Thank you for the solution, really appreciate it.

 

 

Best regards,

Carol Mak

 

Message 6 of 22
adminplm360
in reply to: PringaultJ

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

 

Message 7 of 22

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,



Bastien Mazeran

Technical Support Specialist

Message 8 of 22

Hi
I have the same date format that you have.
Maybe is the part where it says exclude weekends from duration.
Message 9 of 22
bastien.mazeran
in reply to: MJ-Carol

Hi Elias,

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

Regards,



Bastien Mazeran

Technical Support Specialist

Message 10 of 22

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.

 

 

Message 11 of 22

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



Bastien Mazeran

Technical Support Specialist

Message 12 of 22

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

Message 13 of 22

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



Bastien Mazeran

Technical Support Specialist

Message 14 of 22

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?

Message 15 of 22

Yes you can achieve this by using the DATEPART function.

 

DATEPART(YEAR,GETDATE())

 

will return 2013.



Bastien Mazeran

Technical Support Specialist

Message 16 of 22

Thank you that worked

Tags (1)
Message 17 of 22
MJ-Carol
in reply to: jpiggee

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 

Message 18 of 22
bastien.mazeran
in reply to: MJ-Carol

Hi Carol,

 

Please try this new formula:

 

date_part('year', current_date)

 

Regards,



Bastien Mazeran

Technical Support Specialist

Message 19 of 22

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, 

 

 

Jared Sund
Sr. Product Line Manager, Product Lifecycle Management
Autodesk, Inc.
Message 20 of 22
MJ-Carol
in reply to: jared.sund

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

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report