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

    HELP! Booking No defined by calculations

    Shoreline

      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
          comment_1

          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
            mrvodka

            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
              Shoreline

              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
                Shoreline

                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.

                 

                Any other advice?

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

                  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
                    Shoreline

                    Thank you! Thank you! It's working!