Jump to content

autocalculation help needed


This topic is 2829 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2829 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.