6 Replies Latest reply on Aug 15, 2012 10:31 AM by philmodjunk

    Serial number issue


      Serial number issue


      If I have two fields, one which is a company name and another which is an autocreation serial number, how to I create a third feild calc that produces the first three letters of the company field followed by the last three digits of the serial number field?


      i.e. Customers::company= Harrods   Customers::Serial= 123456   New Field= HAR456



      Thank you

        • 1. Re: Serial number issue

          Upper ( Left ( Customers::company ; 3 ) ) & Right ( "000" & Customers::Serial ; 3 )

          • 2. Re: Serial number issue

            Don't forget that every 1000 serial numbers you are guaranteed to duplicate that serial number and Murphies Law suggest that the probabilities of duplicating that serial number on every invoice are 100%...

            Anyone care to crunch the possibility for how often or how soon a duplicate will appear?

            Also the more active a customer is the more likely the duplicate.

            Not to mention the catastrophe that occurs when a meteor hits your server and wipes out... Then you have to rebuild from backups and you forget and the serial number starts repeating as you enter new records which if not entered in the same order....

            • 3. Re: Serial number issue

              Hi Jack

              Will this still happen if I have the first three letters of the customer's surname or company name (if a company) and the last three numbers of their actuall customer id? We create around 400 of these IDs a year so heopfully it wont create an issue. IS there a way of adding to the calc to be a unique number? OR if it exists to go to the next serial? OR do you have any other suggestions? Thank you

              • 4. Re: Serial number issue

                The best, simplest option for generating unique ID's is not to use this method, but to use a simple auto-entered serial number instead.

                If you do use calculeted Identifiers with leading zeroes, protect your self by making it an auto-entered calculation on a text field and specify a unque values validation--so at least the duplication is caught at the time it happens so you can then take corrective action.

                • 5. Re: Serial number issue

                  I Agree- however, the existing Sage Pastel program to which the old database used to export customer info to, already used this alphanumerical code...

                  So I think I am stuck..

                  • 6. Re: Serial number issue

                    I think the key phrase is "used to". if you no longer need to do this, you no longer need to limit your design by using such a method.