6 Replies Latest reply on Apr 21, 2010 5:53 AM by Shoreline

# HELP! Booking No defined by calculations

### Title

HELP! Booking No defined by calculations

### Post

I have to create a booking no that is made up of various calculations

It has to display as follow : eg. 1004-0001 (YYMM-SerialNo) only problem is that the month part displays as "01"

This is the calculation step1

Booking_Year & Booking_Month & " - " & Booking_Serial

The year part of my calculation was easy step2

Case(

Year ( 2010 ); "10";
Year ( 2011 ); "11";
Year ( 2012 ); "12";
Year ( 2013 ); "13";
Year ( 2014 ); "14";
)

But the month doesn't want to work the same way, tried diffrent ways step3

Case(

MonthNameJ ("January"); "01";
MonthNameJ ("February"); "02";
MonthNameJ ("March"); "03";
MonthName ("April"); "04";
MonthName ("May"); "05";
Month ("June"); "06";
Month ("July"); "07";
Month ("August"); "08";
Month ("September"); "09";
Month ("October"); "10";
Month ("November"); "11";
Month ("December"); "12";
)

Is there another way of doing this?

• ###### 1. Re: HELP! Booking No defined by calculations

Assuming Booking_Month is a Text field containing the name of the month, try =

SerialIncrement (

"0000" ;

100 * Mod ( Booking_Year ; 100 ) + Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( Booking_Month ; 3 ) ; 1 ; 1 ) / 3 )

) & " - " & Booking_Serial

• ###### 2. Re: HELP! Booking No defined by calculations

Your calcs are messed up. I dont even know what the point is of either of them.

The Year ( ) function is supposed to give the year for a particular date. Year ( 2010 ) should result in 6 as it is returning the year 0006.

Why dont you store a real date and then use month ( ) and Year ( ) off that date?

* Edit - Sorry walked away from the desk for a while before posting but already had typed it up.

• ###### 3. Re: HELP! Booking No defined by calculations

Hi, Sorry I don't really know how the Year() and Month() functions work really.

Im still learning and im trying everything.

What im trying to accomplish is, I have a booking no that is given for each new booking.

The booking_no is supposed to be made up of the year and month the booking is made and then followed by a serial number.

Eg. 1004-0001 if this was the first order in april 2010

Any advice on how to do this?

• ###### 4. Re: HELP! Booking No defined by calculations

Hi Super Contributor,

Thanx, I was actually wanting it to use the current date to work out what the Booking_No should be, but not entirely sure how to do this.

Im still learning and not sure how all the functions work.

I need the booking_no to be made up of the current year and month followed by a serial no.

The script you gave me works if I enter the month into the Booking_month field, but then the month has to be changed every month for this to keep working.

• ###### 5. Re: HELP! Booking No defined by calculations

If I understand correctly what you're trying to do (big if), you should have three fields:

• Booking_Serial - Number, auto-enter serial number
• Booking_Date - Date, auto-enter creation date
• Booking_No - Calculation (result is Text) =

SerialIncrement (

"0000" ;

100 * Mod ( Year ( Booking_Date ) ; 100 ) + Month ( Booking_Date )

)& " - " & Booking_Serial

• ###### 6. Re: HELP! Booking No defined by calculations

Thank you! Thank you! It's working!