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.

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

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

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.

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.

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.

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.

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

 

I am not:

 

Hi Comment，

 

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

My apologies.