autocalculation help needed

[the_furious]

Hi all,

 

I have a manual input field, and I plan to replace it to calculation field instead.

 

The field is show as below:

 

Invoice-136-036-15

136 being the category

036 being the last record

15 being the year invoice was created.

 

Is there a way to obtain the "036" and replace the XXX to display as Invoice-136-037-15 once a new record is created?

"Invoice-136-"  &  "XXX" & "-" & Right(Year ( Get ( CurrentDate ) );2)

 

Appreciate your advices.

[the_furious]

in addition, the record number will reset at the beginning of a new year.

Invoice-136-001-16 when it's 2016

[comment]

I would suggest you start with a simple Number field, set to auto-enter a serial number. Then add a calculation field along the lines of =

"Invoice-" & Category  & SerialIncrement ( "-000" ; SerialNumber ) & "-" & Right ( Year ( InvoiceDate ) ; 2 )

See a recent discussion about resetting a serial number at the beginning of a year:

http://fmforums.com/forum/topic/94445-new-serial-start-of-year/?p=431915

 

Important:

Make sure you have another field, InvoiceID, that auto-enters a serial number consecutively and will never be changed by anything the user does. Use only this field as the matchfield for your relationships.

[the_furious]

Hi Comment,

 

Thanks for the reply. 

It makes more sense to use Year (InvoiceDate) than Year (Get (CurrentDate) )

 

However, SerialIncrement does not seem to work very well with my string.

I will like the field to display the Serialnumber as a fixed 3-digit code

 

Was trying to

Case ( 

Length ( SerialNumber ) = 1 ; "00" & SerialNumber  ; 

Length ( SerialNumber ) = 2 ; "0" & SerialNumber  ; 

)

but was unsuccessful in integrating it.

[comment]

SerialIncrement does not seem to work very well with my string.

 

Could you provide a more accurate description of the problem than just "does not seem to work very well"? Something along the lines of "I have an input of x as the SerialNumber, and I am getting the result of y, instead of the expected z" would be much more productive.

[the_furious]

Hi Comment,

 

Sorry about it.

 

Was trying to get the result

"Invoice-136-036-15" using "Invoice-" & Category & "-" & SerialIncrement ("000"; SerialNumber) & "-" & Right ( Year (InvoiceDate); 2 ), instead, I'm getting "Invoice-136-36-15"

I intend to stick my string of serialnumber to a 3-digit code here.

[comment]

I'm getting "Invoice-136-36-15"

 

I am not:

 

[the_furious]

Hi Comment，

 

I have no idea why, it's showing the correct values today.

My apologies.