2 Replies Latest reply on Jan 26, 2012 10:03 AM by philmodjunk

    serial number query



      serial number query


      Basic newbie question. I've set Serial number/on creation and entered a next value of Dec100. 

      User enters a new record. The serial number automatically inserts Dec100. Fine.

      User enters a second record, the serial number automatically inserts Dec101, which the user changes to Jan1.

      User enters a third record, the program automatically inserts Dec102. How do I get the program to prompt Jan 2 instead.

      At the moment I have to access the database definition and change the number there, but thats not something and end user can do.  


        • 1. Re: serial number query

          Keep the serial number as a number only, don't use the text prefix, and use that for any relationships you have.  Then for the number that the user sees and resets at the start of the month, use a calculated field that counts how many records exist for that month and adds 1.  Something like

          Perform find [Month (date) = Month (Get (current date) and Year (date) = Year (Get (current date)]

          Set Variable $count [ Get (found count)

          New Record

          Set Field yourserialnumber [Left ( MonthName ( Get ( CurrentDate ) ) ; 3 ) & $count +1]

          • 2. Re: serial number query

            In a multi-user database where two or more users might run that script at the same time, you can get two records with the same number. You might want to add a unique values validation rule on the field to trap for that.

            Or you can go ahead and use a serial number field for this (As Mark suggests, DON'T use it in relationships) and have a script reset the next serial value at the end of each month. With a database hosted on the server, this can be a script scheduled to run just after midnight every night and that resets the serial number field if the date is the first day of the month. (Needs to run one no one else is adding records to the DB.)